Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Jul 2011
    Location
    Toronto, ON
    Posts
    102
    Thanks
    12
    Thanked 1 Time in 1 Post

    Proper UPDATE syntax - I keep getting errors!

    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!!

    PHP Code:
    $update 'UPDATE deals SET title = "' $title '" WHERE source = "' $source '" AND title = "' $title '" AND cityid = "' $cityid '"'
    Last edited by inchecksolution; 08-05-2011 at 04:16 AM.

  • #2
    New Coder
    Join Date
    May 2009
    Location
    Glastonbury, UK
    Posts
    53
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Try this:
    PHP Code:
    $update 'UPDATE deals SET title = "' $title '" WHERE source = "' $source '" AND title = "' $title '" AND cityid = "' $cityid '"'
    You forgot the dot after $cityid
    Bradley Reed

    HTML5, CSS3, Ruby on Rails and PHP
    http://www.universalgames.biz
    http://www.joseandbrad.com

  • #3
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,679
    Thanks
    25
    Thanked 655 Times in 654 Posts
    Also note that your SETTING title = $title

    WHERE title = $title

  • #4
    New Coder
    Join Date
    May 2009
    Location
    Glastonbury, UK
    Posts
    53
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Yes, I noticed that, too. But it wouldn't cause a syntax error. Still, it's worth taking note of.
    Bradley Reed

    HTML5, CSS3, Ruby on Rails and PHP
    http://www.universalgames.biz
    http://www.joseandbrad.com

  • #5
    Regular Coder
    Join Date
    Jul 2011
    Location
    Toronto, ON
    Posts
    102
    Thanks
    12
    Thanked 1 Time in 1 Post
    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:

    PHP Code:
    $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);


  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Not to ask a silly question, but...

    Why do
    Code:
    UPDATE deals SET timestamp = '$timestamp' ...
    and pass the timestamp in from PHP instead of just
    Code:
    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.]


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •