Tag Archives: Database

A store procedure to insert values into multitable without duplicate records

The previous store procedure have records repeated and here is the improve one.

the table like this:

tables

 


  
CREATE PROCEDURE [dbo].[spStoreLMStatusRecording]       
@parameterJobID varchar(30),  
@parameterLMStatus nvarchar(10),  
@parameterEntry_datetime datetime   
   
AS  
BEGIN  
declare   
 @username varchar(500),  
 @Job_Acceptance bit   
    SET @Job_Acceptance = '1'  
   
   
 IF NOT EXISTS(SELECT * FROM tblJob where JobID = @parameterJobID)    
 begin   
  insert into tblJob values(@parameterJobID,@Job_Acceptance);  
  select @username = username from tblUserAuthentication    
  insert into tblLMStatus values(@parameterLMStatus,@parameterEntry_datetime,@parameterJobID,@username)  
 end  
 else  
 begin  
  select @username = username from tblUserAuthentication    
  insert into tblLMStatus values(@parameterLMStatus,@parameterEntry_datetime,@parameterJobID,@username)  
 end  
   
END  
Advertisements

How to write store procedure to insert into multiple table at one time

Today just write a complex store procedures to retrieve data from one table, and insert  values into two tables

Here is my table: DataBase Table

My Task is write a store procedure to get username from tblUserAuthemtication And insert usename and other Values into tblLMStatus and tblJob table.

Here is my solution:


CREATE PROCEDURE [dbo].[spStoreLMStatusRecording]
@parameterRef varchar(30),
@parameterLM_Booking_Ref varchar(30),
@parameterLMStatus nvarchar(10),
@parameterEntry_datetime datetime

AS
BEGIN
declare
@username varchar(500),
@JobID int
insert into tblJob values(@parameterRef,@parameterLM_Booking_Ref);
select @username = username from tblUserAuthentication
select @JobID = ID from tblJob
insert into tblLMStatus values(@parameterLMStatus,@parameterEntry_datetime,@JobID,@username)

END

In my next post I will write how to use this store procedure in my Application project
And my task is not just use this I need write a event to use this store procedure and
also that button trick Send email at the same time.

Cakephp Blog Tutorial 1 – Installation

1. download the latest version cakePHP,

2. copy into localhost root file, and I name it as “Blog”

3. There are 3 things need to fix it out:

First is connect to your databases:

A) Database configuration (make sure have a  database in your localhost)

B) use NetBeans to open your files,under the App file, under config file, you see a database.php.default,rename it and move .default, and open it

C) here you could set your database, there are class you should change it like this

public $default = array(
‘datasource’ => ‘Database/Mysql’,
‘persistent’ => false,
‘host’ => ‘localhost’,
‘port’ => ”,
‘login’ => ‘root’,//I use localhost, so I set it root
‘password’ => ‘*******’,//here put your password
‘database’ => ‘blog’,//choose your databases
‘prefix’ => ”,
‘encoding’ => ‘utf8’,
);

Second,config your salt

under app/config/core.php, open core.php file,find your Security.salt, then go to google type:random strings, click GRC,copy the string to replace it into your file,should look like below:

Configure::write(‘Security.salt’, ‘dVYSYFEl2wYaF5FXSKhFmhGSXBGLmFeyczLUaH4IX8qIKZqo03aasw2jTmcjRP7’);

Third, Debug is optional but I think that it is good have it.

Down load it from cakePHP website name debug_kit-master file put it under your plug file

open bootstrap.php uncommon this statement.

CakePlugin::load(‘DebugKit’); //Loads a single plugin named DebugKit

 

 

Code Files and Data Files

Code Files

  • Web Service .asmx Can be called by other systems, including browsers, and can contain code that can be executed on your server.
  • Class .cs Can contain code to program your web site.
  • Global Application Class .asax Can contain code that is fired in response to interesting things that happen in your site, such as the start of the application or when an error occurs somewhere in the site.

Data Files

  • XML File .xml Used to store data in XML format. In addition to plain XML files, ASP.NET supports a few more XML-based files, two of which you briefly saw before: web.config and the Site Map.
  • SQL Server Database .mdf Files with an .mdf extension are databases that are used by Microsoft SQL Server.
  • ADO.NET Entity Data Model .dbml Used to access databases declaratively, without the need to write code. Technically, this is not a data file, because it does not contain the actual data. However, because it is tied to the database so closely, it makes sense to group it under this header.

create a php page with in paypal button and link–this just continue with previous page

<?php
echo “<h1>Welcome to Bazaar online shop</h1>”;
include(“inc_db.php”);//connect to database newsletter
if($DBConnect)
{
$table = “products”;
$query = “select * from $table”;
$result = @mysql_query($query,$DBConnect);
echo “<table width = ‘80%’ border = ‘1’>”;
echo “<tr><th>Name</th><th>Cost</th><th>Image</th><th>Description</th><th>Add to Cart</th><th>Go Pay</th></tr>”;
while($row = mysql_fetch_assoc($result))
{
echo “<tr><td>$row[Name]</td><td>$row[Cost]</td>”;
echo “<td><img src =’images/”.$row[‘Image’].”‘/></td><td>$row[Description]</td><td>”;
echo “<form action=’https://www.paypal.com/cgi-bin/webscr&#8217; method =’post’>”;
echo “<input type=’hidden’ name=’cmd’ value=’_cart’>”;
echo “<input type=’hidden’ name=’business’ value=’youremail@host.com’>”;
echo “<input type=’hidden’ name=’item_name’ value='”.$row[‘Name’].”‘>”;
echo “<input type=’hidden’ name=’amount’ value='”.$row[‘Cost’].”‘>”;
echo “<input type=’hidden’ name=’currency_code’ value=’AUD’>”;
echo “<input type=’hidden’ name=’add’ value=’add’>”;

echo “<input type=’hidden’ name=’bn’ value=’PP-ShopCartBF:btn_cart_LG.gif:NonHostedGuest’>”;
echo “<input type=’image’ src = ‘https://www.paypalobjects.com/en_AU/i/btn/btn_cart_LG.gif&#8217; border=’0′ name=’submit’ alt=’PayPal – The safer,easier way to pay online.’>”;
echo “<img alt=” border=’0′ src=’https://www.paypalobjects.com/en_AU/i/src/pixel.gif&#8217; width=’1′ height=’1′>”;
echo “</form></td>”;
echo “<td><form action=’https://www.paypal.com/cgi-bin/webscr&#8217; method =’post’>”;
echo “<input type=’hidden’ name=’cmd’ value=’_xclick’>”;
echo “<input type=’hidden’ name=’business’ value=’youremail@host.com’>”;
echo “<input type=’hidden’ name=’item_name’ value='”.$row[‘Name’].”‘>”;
echo “<input type=’hidden’ name=’amount’ value='”.$row[‘Cost’].”‘>”;
echo “<input type=’hidden’ name=’currency_code’ value=’AUD’>”;
echo “<input type=’hidden’ name=’bn’ value=’PP-BuyNowBF:btn_buynowCC_LG.gif:NonHostedGuest’>”;
echo “<input type=’image’ src = ‘https://www.paypalobjects.com/en_AU/i/btn/btn_buynowCC_LG.gif&#8217; border=’0′ name=’submit’ alt=’PayPal – The safer,easier way to pay online.’>”;
echo “<img alt=” border=’0′ src=’https://www.paypalobjects.com/en_Au/i/src/pixel.gif&#8217; width=’1′ height=’1′>”;
echo “</form></td></tr>”;

}
echo “</table>”;
mysql_free_result($result);
mysql_close($DBConnect);

}
?>

use php process a web form feedback-insert data-continue1

you already  had a page collect information from use input, and build a page connect to your serves databases, here is the page use for process the data, (insert the information to your databases)

here is your process_chinese_zodaic.php page sourse code;

<?php
$sender = trim($_POST[‘fname’]);
$feedback =trim($_POST[‘feedback’]);
$agree = $_POST[‘agree’];
$submit = $_POST[‘submit’];
$error=0;

if(empty($sender)||empty($feedback))
{
echo “Your must write your first name,last name,feedback”;
++$error;
}
if($agree == 1)
{
$agree = “N”;

}
else
{
$agree = “Y”;
}
if($error == 0)
{
include(“inc_connect.php”);
if($DBConnect)
{
$table = “zodiacfeedback”;
$data = date(“Y-m-d”);
$time = date(“H:i:s”);
$query = “insert into $table(message_date,message_time,sender,message,public_message)values(‘$date’,’$time’,’$sender’,’$feedback’,’$agree’)”;
$result = @mysql_query($query);
if(!$result)
{
echo “<p>unable to insert the values “. mysql_error().”</p>”;
}
else
{
echo “<p>”.htmlentities($sender).”,Thanks you for giving us feedback.</p>”;
echo “<p><a href=’view_zodiac_feedback.php’>View all of the feedback</a></p>”;

}
mysql_close($DBConnect);
}

}

?>

use php process a web form feedback–continue

since you got a form collect your information , so now you need create a database

here it is , you could save this as inc_connect.php

<?php
$db_name = “chinese_zodiac_database”;
$DBConnect = mysql_connect(“severname”,”usename”,”password”);
if (!$DBConnect)
{
echo “<p>unable to connect to the database server “.mysql_error().”</p>”;
}
else
{
$db = mysql_select_db($db_name);
if(!$db)
{
echo “<p>unable to connect to the database server “.mysql_error().”</p>”;
mysql_close();
$DBConnect = false;
}

}

?>

 

some useful function php with mysql

  • mysql_connect(‘servers’,’admin’,’password’)
  • mysql_select_db(‘name of databases’)
  • mysql_query(‘sql querys’)
  • mysql_num_row(‘my sql query result’)
  • mysql_affected_row() and mysql_info() return information on the records that were affected by an insert,update,or delete query.
  • mysql_fetch_array()
  • mysql_fetch_assoc()
  • mysql_fetch_row()
  • mysql_fetch_lengths()
  • mysql_free_result() This ensures that the resultset doesn’t keep taking up space in your Web server’s memory.
  • You can only use the mysql_free_result() function with SQL statements that return results,such as SELECT queries,and only when the SQL statement successfully returned results. If you attempt to use the mysql_free_result() function with SQL statements that do not return results, such as the CREATE DATABASE and CREATE TABLE statements, or on an empty resultset, you will receive an error.

Retrieving Data With PHP & MySQL(while loop)

<?php
mysql_connect(“localhost”, “admin”, “password”) or die(mysql_error());
mysql_select_db(“test”) or die(mysql_error());

// Retrieve all the data from the “example” table
$result = mysql_query(“select * from example”) or die(mysql_error());

//store the record of the “example” table into $row

//Print out the contents of the entry
echo “<table border=’1′>”;
echo “<tr><th>Name</th><th>Age</th></tr>”;
while($row = mysql_fetch_array($result))
{
echo “<tr><td>”.$row[‘name’].”</td><td>”.$row[‘age’];
echo “</td></tr>”;
}
echo “</table>”;
?>