php - Error updating foreign keys in MYSQL database where wrong option data from select boxes is used to update foreign keys -
i new php , trying update table in mysql php database named mineraltable. need update foreign keys locationid, itemtypeid, imageid , donatorid.
these foreign keys link primary keys in sourcelocationtable, itemtypetable, imagetable , donatortable respectively.
i have form 5 dropdown select boxes actual data values site,region,country , continent shown when user selects location locationid (primary key of sourcelocationtable) recorded in option data. value want inserted mineraltable foreign key linking sourcelocationtable. overall php code file below:
<?php $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['update'])) { 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']; } $sql = "update `mineraltable` set `locationid`='$locationid', `itemtypeid`='$itemtypeid', `imageid`='$imageid', `donatorid`='$donatorid' `itemid`='$itemid'"; if(! $retval ) { die('could not update data: ' . mysql_error()); } echo "updated data successfully\n"; mysql_close($conn); die($sql); } ?> <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> i have php update statement update database php variables selected select boxes. see below:
if(isset($_post['update'])) { 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']; } $sql = "update `mineraltable` set `locationid`='$locationid', `itemtypeid`='$itemtypeid', `imageid`='$imageid', `donatorid`='$donatorid' `itemid`='$itemid'"; if(! $retval ) { die('could not update data: ' . mysql_error()); } echo "updated data successfully\n"; mysql_close($conn); die($sql); } ?> but when run code in web browser success message
"updated data update
mineraltablesetlocationid='golden bay nelson new zealand australasia',itemtypeid='mineral',imageid='c:\xampp\htdocs\images\photo composites-high def\box 2 checked19-01-2013.jpg',donatorid='james smith'itemid='373'"
so values being inserted database not primary key id numbers in select box option data supposed stored actual word data values. can please tell me why ids in select boxes not being stored , handed update statement used update mineraltable?
there html coding problem <option value-, <option value=
change coding from
$siteoptiondata .= "<option value-\"$locationid\">$site $region $country $continent</option>"; $donatoroptiondata .= "<option value-\"$donatorid\">$donatorfn $donatorln</option>"; $itemtypeoptiondata .= "<option value-\"$itemtypeid\">$itemtype</option>"; $imageoptiondata .= "<option value-\"$imageid\">$image</option>"; $itemoptiondata .= "<option value-\"$itemid\">$trayboxno,$iteminbox,$name</option>"; to
$siteoptiondata .= "<option value=\"$locationid\">$site $region $country $continent</option>"; $donatoroptiondata .= "<option value=\"$donatorid\">$donatorfn $donatorln</option>"; $itemtypeoptiondata .= "<option value=\"$itemtypeid\">$itemtype</option>"; $imageoptiondata .= "<option value=\"$imageid\">$image</option>"; $itemoptiondata .= "<option value=\"$itemid\">$trayboxno,$iteminbox,$name</option>"; 
Comments
Post a Comment