SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    Dec 2002
    Location
    Philadelphia
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL weird update error

    I have a simple, plain UPDATE statement that keeps throwing up an error. I've been toying with this for hours:

    $query ="UPDATE users SET blogaddress='www.that.blogspot.com', blogaddress2='something', WHERE id = 1 ";

    and the error:

    Cannot run this query: You have an error in your SQL syntax. Check the
    manual that corresponds to your MySQL server version for the right
    syntax to use near 'WHERE id = 1' at line 1
    Last edited by longneck; Jul 30, 2008 at 14:50. Reason: extra large fonts are unnecessary

  2. #2
    SitePoint Addict
    Join Date
    Dec 2002
    Location
    Philadelphia
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I also tried WHERE id = '1'


    and I also tried

    $query ="UPDATE users SET blogaddress='www.that.blogspot.com', blogaddress2='something', WHERE users.id = '1' ";


    and they are all throwing up similar errors .

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    delete the comma after 'something'

  4. #4
    SitePoint Addict
    Join Date
    Dec 2002
    Location
    Philadelphia
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, that was it. Thanks. Somehow I had kept a comma after the last table alteration in previous code , which must be a bad habit. I know this because I checked previous UPDATE code I had written years back.

    Maybe it was an earlier version of MySQL or I was just lucky. Thanks for the heads up. I love this forum.

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You're welcome

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    PHP Code:
    $a = array( 'this' => 1,'that' => 2,'the other'=>3, ) ;  
    // in php a trailing comma is allowed in an array declaration ... see after "3"

    // simple query builder based on that array

    $qry 'update table set ' ;

    foreach( 
    $a as $k=>$v ){
    $qry .= $k ' = ' $v ',' ;
    }

    $qry rtrim$qry "," );
    $qry .= ' where id = 1 ' ;

    echo 
    $qry ;

    // gives :
    // update table set this = 1,that = 2,the other = 3 where id = 1 
    If you were using some kind of query builder, I would guess you lost a rtrim() somewhere along the line rather than mysql changing how it deals with the rather strict sql statements.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    if you had been using the leading comma coding convention, it would have been more obvious

    trailing comma convention --
    Code:
    UPDATE users 
       SET blogaddress = 'www.that.blogspot.com', 
           blogaddress2 = 'something', 
     WHERE id = 1
    leading comma convention --
    Code:
    UPDATE users 
       SET blogaddress = 'www.that.blogspot.com'
         , blogaddress2 = 'something'
         , 
     WHERE id = 1
    see? more obvious

    of course, this assumes you also indent your code

    (which i just cannot understand why some people don't)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    if you had been using the leading comma coding convention, it would have been more obvious
    I was thinking about linking to that post of yours you once mentioned, but I forgot how it was called, so I couldn't find the post, and decided to let it be


Bookmarks

Posting Permissions

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