SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    473
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can Insert but not Update

    Hi

    I get the following when trying to update my DB:

    Error in update query. Error: 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=152' at line 23.

    id is auto incremental primary key.

    I can insert data but update no can do.

    PHP below.

    PHP Code:
    if($_POST[Update]):  
         
       if(!
    $_REQUEST['id']): 
       
       
    // IF NEW DATA THEN INSERT
          
    $r=mysql_query("INSERT into " $prev "land SET 
          landref='" 
    strtoupper($_REQUEST['landref']) . "',
          location ='" 
    $_REQUEST['location'] . "', 
          city='" 
    $_REQUEST['city'] . "',  
          province='" 
    $_REQUEST['province'] . "',
          country='" 
    $_REQUEST['country'] . "',
          plot='" 
    $_REQUEST['plot'] . "',
          description='" 
    $_REQUEST['description'] . "',
          orientation='"
    .$_REQUEST['orientation']."',
          price ='" 
    $_REQUEST['price'] . "',
          reduced ='" 
    $_REQUEST['reduced'] . "',
          pricefrom ='" 
    $_REQUEST['pricefrom'] . "',
          status ='" 
    $_REQUEST['status'] . "',
          remark='" 
    $_REQUEST['remark'] . "',
          agency_status ='" 
    $_REQUEST['agency_status'] . "',
          joint_name ='" 
    $_REQUEST['joint_name'] . "',
          name ='" 
    $_REQUEST['name'] . "',
          address ='" 
    $_REQUEST['address'] . "',
          tel ='" 
    $_REQUEST['tel'] . "',
          mobile ='" 
    $_REQUEST['mobile'] . "',
          email ='" 
    $_REQUEST['email'] . "',
          geo  ='" 
    $_REQUEST['geo'] . "',
          date_add='" 
    date('Y-m-d') . "'")
          or die (
    "Error in insert query. Error: ".mysql_error());       
          
    $_REQUEST['id']=mysql_insert_id();
                  
       else: 
       
       
    // IF DATA EXISTS THEN UPDATE
          
    $r=mysql_query("UPDATE  " $prev "land SET
          landref='" 
    strtoupper($_REQUEST['landref']) . "',
          location ='" 
    $_REQUEST['location'] . "', 
          city='" 
    $_REQUEST['city'] . "',  
          province='" 
    $_REQUEST['province'] . "',
          country='" 
    $_REQUEST['country'] . "',
          plot='" 
    $_REQUEST['plot'] . "',
          description='" 
    $_REQUEST['description'] . "',
          orientation='"
    .$_REQUEST['orientation']."',
          price ='" 
    $_REQUEST['price'] . "',
          reduced ='" 
    $_REQUEST['reduced'] . "',
          pricefrom ='" 
    $_REQUEST['pricefrom'] . "',
          status ='" 
    $_REQUEST['status'] . "',
          remark='" 
    $_REQUEST['remark'] . "',
          agency_status ='" 
    $_REQUEST['agency_status'] . "',
          joint_name ='" 
    $_REQUEST['joint_name'] . "',
          name ='" 
    $_REQUEST['name'] . "',
          address ='" 
    $_REQUEST['address'] . "',
          tel ='" 
    $_REQUEST['tel'] . "',
          mobile ='" 
    $_REQUEST['mobile'] . "',
          email ='" 
    $_REQUEST['email'] . "',
          geo  ='" 
    $_REQUEST['geo'] . "',
          WHERE id=" 
    $_REQUEST['id'])
          or die (
    "Error in update query. Error: ".mysql_error());    
          
    $_REQUEST['id']=$_POST['id'];  
       endif; 
    Not sure whether this is a PHP prob or MySQL ?

    Thanks for looking

    C

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know php but all of your query lines are surrounded with single quotes, except the line giving the error.
    And, on looking again, you don;t seem to have a closing double quote either for the where clause or the full query

    hth

    bazz
    Last edited by IBazz; Mar 17, 2010 at 19:57. Reason: another point to add

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    You have a trailing quote for the last character on the line above WHERE id=" . $_REQUEST['id']) that needs to be removed.

  4. #4
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    473
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oddz you are a star!

    I have been staring at this code for hours and never noticed

    Thanks a lot

    C

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    it actually was a trailing comma, not a trailing quote

    wouldn't happen nearly as easily if you were using the leading comma convention...
    Code:
    UPDATE " . $prev . "land 
       SET landref='" . strtoupper($_REQUEST['landref']) . "'
         , location ='" . $_REQUEST['location'] . "'
         , city='" . $_REQUEST['city'] . "'
         , ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Evangelist ColinHughes's Avatar
    Join Date
    Sep 2004
    Location
    Spain
    Posts
    473
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the convention tip I have now changed it. Just need to change my brain to do the same

    C


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
  •