Can't update MySQL correctly

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Red Blaze
Forum Commoner
Posts: 40
Joined: Mon Mar 27, 2006 3:45 pm

Can't update MySQL correctly

Post 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.
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post 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.
Red Blaze
Forum Commoner
Posts: 40
Joined: Mon Mar 27, 2006 3:45 pm

Post 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.
Post Reply