Update vs Insert

cmptrwhz · Sep 22, 2003

Hi All,

I am having a problem with the code below. What I want to do is have the variables Number, sess1, ID, total, & confirmation passed from the previous page (works fine).
Then have the system run a query on the existing order table and see if there is already an order with the Number and sess1. If there is, then just update it with the new information that was passed from the previous page. If it does not exist already, then insert the info into the table.

Seems pretty straight forward, but I'm missing something.

Any help would be very very appreciated.

Thanks.

Mike

Code :

<?
extract($HTTP_GET_VARS);
?>
<?
$DBhost = "localhost";
$DBuser = "xxxxxx";
$DBpass = "xxxxxx";
$DBName = "xxxxxxx";

mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");
@mysql_select_db("$DBName") or die("Unable to select database $DBName");

function NumGen($length)
{
for ($i = 1; $i <= $length; $i++)
{
if ($i == 1)
$randnum = rand(0, 9);
else
$randnum .= rand(0, 9);
}

return $randnum;

}

$confirm_num = NumGen(10);
$today = date("mdY");
$aa="SELECT * FROM orders WHERE ID='$ID' and sess1='$sess1'";
$bb=mysql_query($aa);
while ($cc=mysql_fetch_array($bb)){
$dd="UPDATE orders SET date='$today', conf_num='$confirm_num', total='$total', confirmation='$confirmation' WHERE ID='$ID' and sess1='$sess1' and Number='$Number'";
if (mysql_query($dd)) {
echo '
<table width=725 border=0>
<tr>
<td>
Thank You!
Your Order Confirmation # is : '. $confirm_num .'

</td></tr><tr></td>
<a href="menu.php?Number='. $Number .'">Click Here to Return To The Main Menu</a></td></tr></table>
';
}
}
else {
echo ("<center>Error Creating Item : ". mysql_error()."</center>");
}

if(mysql_num_rows($bb)<1){
$a="INSERT INTO orders SET ID='$ID', sess1='$sess1', Number='$Number', date='$today', conf_num='$confirm_num', total='$total', confirmation='$confirmation'";
if (mysql_query($a)) {
echo '
<table width=725 border=0>
<tr>
<td>
Thank You!
Your Order Confirmation # is : '. $confirm_num .'

</td></tr><tr></td>
<a href="menu.php?Number='. $Number .'">Click Here to Return To The Main Menu</a></td></tr></table>
';
}
}
else {
echo ("<center>Error Creating Item : ". mysql_error()."</center>");
}
?>

7thgencivic_com · Sep 22, 2003

this should do it. not tested tho.
You SQL insert was totally wrong. You need to make better var names other than dd,bb,aa etc.
Let me know how it works.

 
<? 
extract($HTTP_GET_VARS); 

$DBhost = "localhost"; 
$DBuser = "xxxxxx"; 
$DBpass = "xxxxxx"; 
$DBName = "xxxxxxx"; 

mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database"); 
@mysql_select_db("$DBName") or die("Unable to select database $DBName"); 

function NumGen($length)  { 
	for ($i = 1; $i <= $length; $i++) { 
		if ($i == 1) 
			$randnum = rand(0, 9); 
		else 
			$randnum .= rand(0, 9); 
	} 
	return $randnum; 
} 

$confirm_num = NumGen(10); 
$today = date("mdY"); 

$checkit=mysql_query("SELECT * FROM orders WHERE ID='$ID' and sess1='$sess1'"); 
if (mysql_num_rows($checkit) > 0) {
	while ($result=mysql_fetch_array($checkit)){ 
		$updateit="UPDATE orders SET date='$today', conf_num='$confirm_num', total='$total', confirmation='$confirmation' WHERE ID='$ID' and sess1='$sess1' and Number='$Number'"; 
		$result=mysql_query($updateit);
} 
else { 
//insert goes here
		$inserit=mysql_query("INSERT INTO orders (ID,sess1,Number,date,conf_num,total,confirmation)
				VALUES('$ID', '$sess1', '$Number', '$today','$confirm_num', '$total', '$confirmation'"); 
		$result=mysql_query($insertit);				
} 
if ($result) {
		echo ' 
			<table width=725 border=0> 
				<tr> 
					<td> 
					<b><font size="5" face="Arial,Helvetica,Geneva,Swiss,SunSans-Regular">Thank You!</b></font><p> 
					<font size="3" face="Arial,Helvetica,Geneva,Swiss,SunSans-Regular color=red">Your Order Confirmation # is : '. $confirm_num .'<p> 
					<p></p><p></p> 
					</td></tr><tr></td><font size="3" face="Arial,Helvetica,Geneva,Swiss,SunSans-Regular"> 
					<a href="menu.php?Number='. $Number .'">Click Here to Return To The Main Menu</a></font>
					</td>
				</tr>
			</table> '; 

} else { 
echo ("<center>Error Creating Item : ". mysql_error()."</center>"); 
} 
?>

Update vs Insert

Ccmptrwhz

77thgencivic_com