Page 1 of 1

Can't update MySQL correctly

Posted: Mon Apr 03, 2006 12:29 pm
by Red Blaze
Ok, here's my problem.
I want the visitor to be able to update the kind of paper, and how many in quantity of a photo. But sometimes it'll do it, sometimes it'll get ignored. I do want it to ignore the mysql_query but only when 3 cols are the same. Here's my code:

Code: Select all

<?php require_once('../Connections/prophot.php'); ?>
<?php
for($u = 0; $u < count($size); $u++)
{
 print "$itemid[$u]<br>";
 print "$filename[$u]<br>";
 print "$size[$u]<br>";
 print "$paper[$u]<br>";
 print "$quantity[$u]<br>";

mysql_select_db($database_prophot, $prophot);
$query_callprices = 'SELECT * FROM prices WHERE size = "'. $size[$u] .'" AND paper = "' .$paper[$u] .'"';
$callprices = mysql_query($query_callprices, $prophot) or die(mysql_error());
$row_callprices = mysql_fetch_assoc($callprices);
$totalRows_callprices = mysql_num_rows($callprices);
$subtotal = $row_callprices['price'];
$subtotal = $subtotal * $quantity[$u];

echo "Subtotal $subtotal";
 print "<hr width=\"125\" align=\"left\">";
 
 $sql = 'SELECT * FROM items WHERE itemname= "'. $filename[$u] .'" AND size = "'. $size[$u] .'" AND paper = "'. $paper[$u] .'"';
 $query = mysql_query($sql) or die(mysql_error());
 $row_sql = mysql_fetch_assoc($query);
 $totalRows_sql = mysql_num_rows($query);
 	if(mysql_num_rows($query) != 0){
	//ignore only when the itenmane, size, and paper is the same...
	}else{
	//...otherwise update it.
	mysql_query('UPDATE items SET paper="'. $paper[$u] .'", quantity="'. $quantity[$u] .'", subtotal="'. $subtotal .'" WHERE itemid="'. $itemid[$u] .'"');
	}
}
?>
Here's the results as an image.
Image
Order goes like so:
itemid
itemname
size
paper
quantity
subtotal

And my database table:
Image

As always, help would greatly be appreciated.

Posted: Mon Apr 03, 2006 8:49 pm
by Ambush Commander
Several things that should be done:

1. Move the database selection outside the loop. You don't need to reselect the same database every time you query.
2. You probably want to escape the output of the prints with htmlentities
3. Make sure you escape the db parameters using mysql_real_escape_string
4. Instead of checking the item if it changed, I would recommend blindly performing the update. Because there are less queries involved, it's faster and simpler

Any of these four things could be causing the problem, or it could be something else. I can't really tell until you isolate the problem. Until then, fix these issues, and it may fix itself.

Posted: Tue Apr 04, 2006 11:17 am
by Red Blaze
Ambush Commander wrote:Several things that should be done:

1. Move the database selection outside the loop. You don't need to reselect the same database every time you query.
2. You probably want to escape the output of the prints with htmlentities
3. Make sure you escape the db parameters using mysql_real_escape_string
4. Instead of checking the item if it changed, I would recommend blindly performing the update. Because there are less queries involved, it's faster and simpler

Any of these four things could be causing the problem, or it could be something else. I can't really tell until you isolate the problem. Until then, fix these issues, and it may fix itself.
figured it out. Thank you so much for the tips.