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
Post a Comment