Can Insert but not Update

Databases
1

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.

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

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

3

You have a trailing quote for the last character on the line above WHERE id=" . $_REQUEST[‘id’]) that needs to be removed.

4

Oddz you are a star!

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

Thanks a lot

C

5

it actually was a trailing comma, not a trailing quote

wouldn’t happen nearly as easily if you were using the leading comma convention…

UPDATE " . $prev . "land 
   SET landref='" . strtoupper($_REQUEST['landref']) . "'
     , location ='" . $_REQUEST['location'] . "'
     , city='" . $_REQUEST['city'] . "'
     , ...
6

Thanks for the convention tip I have now changed it. Just need to change my brain to do the same :nono:

C