...

View Full Version : Resolved Proper UPDATE syntax - I keep getting errors!



inchecksolution
08-04-2011, 09:20 PM
Can someone please help me? I get very confused with the " and ' - don't know what to use where and I keep getting syntax errors. This is the closest I've come!

Thanks!!


$update = 'UPDATE deals SET title = "' . $title . '" WHERE source = "' . $source . '" AND title = "' . $title . '" AND cityid = "' . $cityid '"';

noisyscanner
08-04-2011, 11:18 PM
Try this:

$update = 'UPDATE deals SET title = "' . $title . '" WHERE source = "' . $source . '" AND title = "' . $title . '" AND cityid = "' . $cityid . '"';
You forgot the dot after $cityid

sunfighter
08-04-2011, 11:24 PM
Also note that your SETTING title = $title

WHERE title = $title

noisyscanner
08-04-2011, 11:26 PM
Yes, I noticed that, too. But it wouldn't cause a syntax error. Still, it's worth taking note of.

inchecksolution
08-05-2011, 04:16 AM
Thanks for the input guys. The point of SETTING title = $title WHERE title = $title was just to see if the row existed (if it does then I didn't need to do anything). Then I would count mysql_affected_rows() and see if anything changed BUT, MySQL is smart and said the title doesn't need to change because they equal eachother so mysql_affected_rows was always 0. Ended up with this:



$timestamp = date("Y-m-d G:i:s");
$update = "UPDATE deals SET timestamp = '$timestamp' WHERE source = '$source' AND title = '".htmlspecialchars($title,ENT_QUOTES)."' AND cityid = '$cityid'";
$result = mysql_query($update);

if(mysql_affected_rows() == 0){
$insert = "INSERT INTO deals (cityid, source, url, title, price, value, discount, lat, lng, image) VALUES ('$cityid', 'crowdsavings', '$url', '".htmlspecialchars($title,ENT_QUOTES)."', '$discountprice[0]', '$retailprice[0]', '$savings', '$lat', '$lng', '$image')";
$result = mysql_query($insert);
}

Old Pedant
08-05-2011, 04:40 AM
Not to ask a silly question, but...

Why do

UPDATE deals SET timestamp = '$timestamp' ...

and pass the timestamp in from PHP instead of just


UPDATE deals SET timestamp = NOW() ...

and let MySQL do it?

[Yes, I can think of one reason...If your PHP server and MySQL server were in different locations and you weren't sure the time was the same on both. But for the purposes used here, I'd think even that wouldn't matter.]



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum