mysql - PHP syntax error in UPDATE table statement -


i trying update table named mineraltable (which has primary key named itemid) foreign keys values sourcelocationtable (locationid), imagetable (imageid), itemtypetable (itemtypeid) , donatortable (donatorid).

i want user able select location, image, itemtype , donator values dropdown select boxes value stored in variable , mineraltable updated foreign key numbers of value displayed in dropdown select boxes.

the relationship of latter 4 tables mineraltable 1-many therefore can't use junction table hold foreign keys must go in mineral table.

after trying run following sql code

update mineraltable set locationid='160',itemtypeid='1',imageid='6',    donatorid='4' itemid='372'  

this converted php formatting php variables substituted numerical values.

$sql = "update mineraltable\n"     . "set locationid=\'$locationid\', itemtypeid=\'$itemtypeid\', imageid=\'$imageid\', donatorid=\'$donatorid\'\n"     . "where itemid=\'itemid\'"  

i found sql code written local server xampp numerical values runs , updates foreign key values in mineraltable, when run php version of code in web browser error:

"you have error in sql syntax; check manual corresponds mariadb server version right syntax use near '\'166\', itemtypeid=\'6\', imageid=\'11\', donatorid=\'4\' itemid=\'371\'' @ line 2”

i have looked @ on stackoverflow method of updating foreign keys have found can temporarily remove foreign key execute code , reapply foreign key. not recommended.

i have gone through code multiple times , cannot see errors. can please tell me new php coding syntax error being caused? constructive answers appreciated.

i have followed answer in how update foreign key value in mysql database update statement code. have looked @ [1]: syntax error in update statement troubleshoot problem, example in latter link not similar mine.

here php code whole input form.

   $debugmode = true;     $dbhost = 'localhost';    $dbuser = 'root';    $dbpass = '';    $dbname = 'rockandmineraldb';    $conn = mysql_connect($dbhost, $dbuser, $dbpass,$dbname);     if(! $conn ) {       die('could not connect: ' . mysql_error());    }     echo 'connected successfully';      $sql = 'select locationid,site,region,country,continent sourcelocationtable';    mysql_select_db('rockandmineraldb');    $retval = mysql_query( $sql, $conn );     if(! $retval ) {       die('could not data: ' . mysql_error());    }  $siteoptiondata="";    while($row = mysql_fetch_array($retval, mysql_assoc)) {          $locationid = $row['locationid'];           $site = $row['site'];          $region = $row['region'];          $country = $row['country'];          $continent = $row['continent'];         $siteoptiondata .= "<option value-\"$locationid\">$site $region $country $continent</option>";  }        $sql = 'select donatorid,donatorfn,donatorln donatortable';    mysql_select_db('rockandmineraldb');    $retval = mysql_query( $sql, $conn );     if(! $retval ) {       die('could not data: ' . mysql_error());    }  $donatoroptiondata="";    while($row = mysql_fetch_array($retval, mysql_assoc)) {          $donatorid = $row['donatorid'];           $donatorfn = $row['donatorfn'];          $donatorln = $row['donatorln'];          $donatoroptiondata .= "<option value-\"$donatorid\">$donatorfn $donatorln</option>";  }      $sql = 'select itemtypeid,itemtype itemtypetable';    mysql_select_db('rockandmineraldb');    $retval = mysql_query( $sql, $conn );     if(! $retval ) {       die('could not data: ' . mysql_error());    }  $itemtypeoptiondata="";    while($row = mysql_fetch_array($retval, mysql_assoc)) {          $itemtypeid = $row['itemtypeid'];           $itemtype = $row['itemtype'];           $itemtypeoptiondata .= "<option value-\"$itemtypeid\">$itemtype</option>";  }     $sql = 'select imageid,image imagetable';    mysql_select_db('rockandmineraldb');    $retval = mysql_query( $sql, $conn );     if(! $retval ) {       die('could not data: ' . mysql_error());    }  $imageoptiondata="";    while($row = mysql_fetch_array($retval, mysql_assoc)) {          $imageid = $row['imageid'];           $image = $row['image'];         $imageoptiondata .= "<option value-\"$imageid\">$image</option>";  }   $sql = 'select itemid,trayboxno,iteminbox,name mineraltable';    mysql_select_db('rockandmineraldb');    $retval = mysql_query( $sql, $conn );     if(! $retval ) {       die('could not data: ' . mysql_error());    }  $itemoptiondata="";    while($row = mysql_fetch_array($retval, mysql_assoc)) {          $itemid = $row['itemid'];           $trayboxno = $row['trayboxno'];          $iteminbox = $row['iteminbox'];          $name = $row['name'];           $itemoptiondata .= "<option value-\"$itemid\">$trayboxno,$iteminbox,$name</option>";  }    mysql_free_result($retval);    echo "fetched data successfully\n";  if(isset($_post['item'])){ $itemid== $_post['item']; }  if(isset($_post['location'])){ $locationid = $_post['location']; }  if(isset($_post['itemtype'])){ $itemtypeid = $_post['itemtype']; }  if(isset($_post['image'])){ $imageid = $_post['image']; }  if(isset($_post['donator'])){ $donatorid = $_post['donator']; }    if(isset)  $sql = "update mineraltable\n"     . "set locationid=\'$locationid\', itemtypeid=\'$itemtypeid\', imageid=\'$imageid\', donatorid=\'$donatorid\'\n"     . "where itemid=\'$itemid\'";               mysql_select_db('rockandmineraldb');             $retval = mysql_query( $sql, $conn );              if(! $retval ) {                die('could not update data: ' . mysql_error());             }             echo "updated data successfully\n";              mysql_close($conn);   ?>      <form method = "post" action = "<?php $_php_self ?>"> <table>  <tr>                     <td>select site</td>                     <td>                         <select size="10" name="location" multiple="multiple" id="location">                               <?php echo $siteoptiondata; ?>                          </select>                     </td>                 </tr>      <br>                   <tr>     <td>select donator</td>                     <td>                         <select size="10" name="donator" multiple="multiple" id="donator">                               <?php echo $donatoroptiondata; ?>                          </select>                     </td>                 </tr>  <br>                  <tr>     <td>select itemtype</td>                     <td>                         <select size="10" name="itemtype" multiple="multiple" id="itemtype">                               <?php echo $itemtypeoptiondata; ?>                          </select>                     </td>                 </tr>  <br>                  <tr>     <td>select image</td>                     <td>                           <select size="10" name="image" multiple="multiple" id="image">                           <?php echo $imageoptiondata; ?>                           </select>                     </td>                 </tr>                  <tr>      <td>select item</td>                     <td>                         <select size="10" name="item" multiple="multiple" id="item">                               <?php echo $itemoptiondata; ?>                          </select>                     </td>                 </tr>   </table>    <input name="update" type="submit" id="update" value="update">  </form> 

 $sql = "update mineraltable         set locationid='$locationid', itemtypeid='$itemtypeid', imageid='$imageid',       donatorid='$donatorid' itemid='itemid' "; 

please remove \n , backslash query. not required.


Comments

Popular posts from this blog

get url and add instance to a model with prefilled foreign key :django admin -

css - Make div keyboard-scrollable in jQuery Mobile? -

ruby on rails - Seeing duplicate requests handled with Unicorn -