yandoo Posted February 14, 2008 Share Posted February 14, 2008 Hi there, I have manage to avoid encountering this problem until now and was hoping for some advice on what to do... Heres the table that im dealing with its called: FavouriteFoods - which contains: FoodID(int - auto increment(primary key of another table)), Foodname (varchar) and AnimalID (Int -auto increment (primary key of another table)) Basically i have a drop down menu on a form that displays foodnames, with 2 hiddenfields for FoodID and AnimalID. My problem.... Is that the user only has the options (Foodnames) of the drop down which they enter....But i need to somehow make the appropriate FoodID (that matches FoodName in Food table that they input in drop down) appear in the hiddenfield.... How do i do this please ??????? At the moment NO value i given to the FoodID so i cannot update the record. This is something I have contemplated for a long time but never understood how to do... Previously i have avoided the problem by making the primary key a unique Varchar which would be FoodName leaving out the FoodID completely. If anybody could give me some help that would be brilliant!!! Thank you Link to comment https://forums.phpfreaks.com/topic/91040-insert-record-primary-key-value/ Share on other sites More sharing options...
Cep Posted February 14, 2008 Share Posted February 14, 2008 Code please (And why doesn't your table have its own primary key?) Link to comment https://forums.phpfreaks.com/topic/91040-insert-record-primary-key-value/#findComment-466637 Share on other sites More sharing options...
yandoo Posted February 14, 2008 Author Share Posted February 14, 2008 Hi there, Sorry FavourtieFood has its own primary key FavourtieFoodID i forgot to mention sorry. Ill post the code below and provide a screen shot of page to give u idea...thankyou <?php require_once('Connections/woodside.php'); ?> <?php function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") { $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue; switch ($theType) { case "text": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "long": case "int": $theValue = ($theValue != "") ? intval($theValue) : "NULL"; break; case "double": $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL"; break; case "date": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "defined": $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; break; } return $theValue; } $editFormAction = $_SERVER['PHP_SELF']; if (isset($_SERVER['QUERY_STRING'])) { $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']); } if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) { $updateSQL = sprintf("UPDATE animal SET HouseTrained=%s, ExpCat=%s, ExpCar=%s, ExpChild=%s, ExpOnlead=%s, ExpOffLead=%s, Valid=%s WHERE AnimalID=%s", GetSQLValueString(isset($_POST['checkbox2']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['checkbox22']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['checkbox24']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['checkbox25']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['checkbox27']) ? "true" : "", "defined","1","0"), GetSQLValueString(isset($_POST['checkbox']) ? "true" : "", "defined","1","0"), GetSQLValueString($_POST['vallid'], "int"), GetSQLValueString($_POST['animalid'], "int")); mysql_select_db($database_woodside, $woodside); $Result1 = mysql_query($updateSQL, $woodside) or die(mysql_error()); $updateGoTo = "stage5.php"; if (isset($_SERVER['QUERY_STRING'])) { $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?"; $updateGoTo .= $_SERVER['QUERY_STRING']; } header(sprintf("Location: %s", $updateGoTo)); } if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) { $insertSQL = sprintf("INSERT INTO favouritefoods (FavouriteFoodID, AnimalID, FoodID, FoodName) VALUES (%s, %s, %s, %s)", GetSQLValueString($_POST['favouritefoodisd'], "int"), GetSQLValueString($_POST['animalid'], "int"), GetSQLValueString($_POST['foodid'], "int"), GetSQLValueString($_POST['foodname'], "text")); mysql_select_db($database_woodside, $woodside); $Result1 = mysql_query($insertSQL, $woodside) or die(mysql_error()); $insertGoTo = "stage4.php"; if (isset($_SERVER['QUERY_STRING'])) { $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?"; $insertGoTo .= $_SERVER['QUERY_STRING']; } header(sprintf("Location: %s", $insertGoTo)); } $colname_animal = "-1"; if (isset($_GET['recordID'])) { $colname_animal = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']); } mysql_select_db($database_woodside, $woodside); $query_animal = sprintf("SELECT * FROM animal WHERE AnimalID = %s", $colname_animal); $animal = mysql_query($query_animal, $woodside) or die(mysql_error()); $row_animal = mysql_fetch_assoc($animal); $totalRows_animal = mysql_num_rows($animal); mysql_select_db($database_woodside, $woodside); $query_test = "SELECT AnimalTypeID FROM animal ORDER BY AnimalID DESC"; $animal = mysql_query($query_test, $woodside) or die(mysql_error()); $row_test = mysql_fetch_assoc($animal); $query_foods = "SELECT * FROM foods WHERE AnimalTypeID='" . $row_test['AnimalTypeID'] . "'"; $foods = mysql_query($query_foods, $woodside) or die(mysql_error()); $row_foods = mysql_fetch_assoc($foods); $totalRows_foods = mysql_num_rows($foods); mysql_select_db($database_woodside, $woodside); $query_favouritefoods = "SELECT * FROM favouritefoods"; $favouritefoods = mysql_query($query_favouritefoods, $woodside) or die(mysql_error()); $row_favouritefoods = mysql_fetch_assoc($favouritefoods); $totalRows_favouritefoods = mysql_num_rows($favouritefoods); ?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <title>Untitled Document</title> <link href="bordertop.css" rel="stylesheet" type="text/css" /> </head> <body> <form id="form1" name="form1" method="POST" action="<?php echo $editFormAction; ?>"> <p> </p> <table height ="225" border="0" align="center"> <tr valign="baseline"> <td height="20" colspan="3" align="right" valign="middle" nowrap="nowrap"> </td> </tr> <tr valign="baseline"> <td width="87" height="20" align="right" valign="middle" nowrap="nowrap"><strong>Animal ID:</strong></td> <td width="6" align="right" valign="middle" nowrap="nowrap"> </td> <td height="20" width="158" valign="middle"><?php echo $row_animal['AnimalID']; ?></td> </tr> <tr valign="baseline"> <td height="20" align="right" valign="middle" nowrap="nowrap"><strong>Animal Type:</strong></td> <td align="right" valign="middle" nowrap="nowrap"> </td> <td height="20" valign="middle"><?php echo $row_animal['AnimalTypeID']; ?></td> </tr> <tr valign="baseline"> <td height="20" align="right" valign="middle" nowrap="nowrap"><strong>Name:</strong></td> <td align="right" valign="middle" nowrap="nowrap"> </td> <td height="20" valign="middle"><label></label> <label><?php echo $row_animal['Name']; ?></label></td> </tr> <tr valign="baseline"> <td height="25" align="right" valign="middle" nowrap="nowrap"><strong>Breed:</strong></td> <td align="right" valign="middle" nowrap="nowrap"> </td> <td height="25" valign="middle"><label><?php echo $row_animal['BreedID']; ?></label></td> </tr> <tr valign="baseline"> <td height="22" align="right" valign="middle" nowrap="nowrap"><strong>Age:</strong></td> <td align="right" valign="middle" nowrap="nowrap"> </td> <td height="25" valign="middle"><label><?php echo $row_animal['Age']; ?></label></td> </tr> <tr valign="baseline"> <td height="22" align="right" valign="middle" nowrap="nowrap"><strong>Sex:</strong></td> <td align="right" valign="middle" nowrap="nowrap"> </td> <td height="25" valign="middle"><label><?php echo $row_animal['Sex']; ?></label></td> </tr> <tr valign="baseline"> <td height="22" align="right" valign="middle" nowrap="nowrap"><strong>Date:</strong></td> <td align="right" valign="middle" nowrap="nowrap"> </td> <td height="25" valign="middle"><?php echo $row_animal['Date']; ?></td> </tr> <tr valign="baseline"> <td height="25" colspan="3" align="right" valign="middle" nowrap="nowrap"> </td> </tr> </table> <p> </p> <table width="471" border="0" align="center"> <tr> <td width="461"> </td> </tr> <tr> <td> </td> </tr> </table> <table width="496" border="0" align="center"> <tr valign="baseline"> <td colspan="11" align="right" nowrap="nowrap"><div align="center"> <p><strong>Animal Experience</strong></p> <p> </p> </div></td> </tr> <tr valign="baseline"> <td width="97" height="30" align="right" valign="baseline" nowrap="nowrap">House Trained:</td> <td width="27" valign="baseline"><input <?php if (!(strcmp($row_animal['HouseTrained'],1))) {echo "checked=\"checked\"";} ?> input="input" disabled="disabled" type="checkbox" name="checkbox2" value="checkbox" /></td> <td width="7" valign="baseline"> </td> <td width="62" valign="baseline">Cats:</td> <td width="36" valign="baseline"><input <?php if (!(strcmp($row_animal['ExpCat'],1))) {echo "checked=\"checked\"";} ?> input="input" disabled="disabled" type="checkbox" name="checkbox22" value="checkbox" /></td> <td width="15" valign="baseline"> </td> <td width="39"> Cars:</td> <td width="23" valign="baseline"><input <?php if (!(strcmp($row_animal['ExpCar'],1))) {echo "checked=\"checked\"";} ?> input="input" disabled="disabled" type="checkbox" name="checkbox24" value="checkbox" /></td> <td width="9" valign="baseline"> </td> <td width="63" valign="baseline">Children:</td> <td width="72" valign="baseline"><input <?php if (!(strcmp($row_animal['ExpChild'],1))) {echo "checked=\"checked\"";} ?> input="input" disabled="disabled" type="checkbox" name="checkbox25" value="checkbox" /></td> </tr> <tr valign="baseline"> <td nowrap="nowrap" align="right">On lead:</td> <td><input <?php if (!(strcmp($row_animal['ExpOnlead'],1))) {echo "checked=\"checked\"";} ?> input="input" disabled="disabled" type="checkbox" name="checkbox27" value="checkbox" /></td> <td> </td> <td> Off Lead:</td> <td><input <?php if (!(strcmp($row_animal['ExpOffLead'],1))) {echo "checked=\"checked\"";} ?> input="input" disabled="disabled" type="checkbox" name="checkbox" value="checkbox" /></td> <td> </td> <td>dogs:</td> <td><input <?php if (!(strcmp($row_animal['ExpDog'],1))) {echo "checked=\"checked\"";} ?> input="input" disabled="disabled" type="checkbox" name="checkbox23" value="checkbox" /></td> <td> </td> <td>alone:</td> <td><input <?php if (!(strcmp($row_animal['ExpAlone'],1))) {echo "checked=\"checked\"";} ?> input="input" disabled="disabled" type="checkbox" name="checkbox26" value="checkbox" /></td> </tr> <tr valign="baseline"> <td colspan="11" align="right" nowrap="nowrap"> </td> </tr> </table> <p> </p> <table height ="117" border="0" align="center"> <tr valign="baseline"> <td height="20" colspan="3" align="right" valign="middle" nowrap="nowrap"> </td> </tr> <tr valign="baseline"> <td width="87" height="20" align="right" valign="middle" nowrap="nowrap">Food Name: </td> <td width="6" align="right" valign="middle" nowrap="nowrap"> </td> <td height="20" width="158" valign="middle"><label> <select name="foodname" id="foodname"> <?php do { ?> <option value="<?php echo $row_foods['FoodName']?>"><?php echo $row_foods['FoodName']?></option> <?php } while ($row_foods = mysql_fetch_assoc($foods)); $rows = mysql_num_rows($foods); if($rows > 0) { mysql_data_seek($foods, 0); $row_foods = mysql_fetch_assoc($foods); } ?> </select> </label></td> </tr> <tr valign="baseline"> <td height="20" align="right" valign="middle" nowrap="nowrap"> </td> <td align="right" valign="middle" nowrap="nowrap"> </td> <td height="20" valign="middle"></td> </tr> <tr valign="baseline"> <td height="20" align="right" valign="middle" nowrap="nowrap"> </td> <td align="right" valign="middle" nowrap="nowrap"> </td> <td height="20" valign="middle"><label></label> <label></label></td> </tr> <tr valign="baseline"> <td height="25" align="right" valign="middle" nowrap="nowrap"> </td> <td align="right" valign="middle" nowrap="nowrap"> </td> <td height="25" valign="middle"><label></label></td> </tr> </table> <p align="center"> <label> <input type="submit" name="Submit" value="Submit" /> </label> </p> <input name="animalid" type="hidden" id="animalid" value="<?php echo $row_animal['AnimalID']; ?>" /> <input name="foodid" type="hidden" id="foodid" value="<?php echo $row_favouritefoods['FoodID']; ?>" /> <input name="favouritefoodisd" type="hidden" id="favouritefoodisd" value="<?php echo $totalRows_favouritefoods +1 ?>" /> <input type="hidden" name="MM_insert" value="form1"> </form> </body> </html> <?php mysql_free_result($animal); mysql_free_result($foods); mysql_free_result($favouritefoods); ?> Thank You Link to comment https://forums.phpfreaks.com/topic/91040-insert-record-primary-key-value/#findComment-466753 Share on other sites More sharing options...
revraz Posted February 14, 2008 Share Posted February 14, 2008 I dont understand your reasoning to have Two ID fields that auto increment. I understand that you are assigning a ID for a foreign key, but you may want to consider on how you are assigning the key. Link to comment https://forums.phpfreaks.com/topic/91040-insert-record-primary-key-value/#findComment-466760 Share on other sites More sharing options...
yandoo Posted February 14, 2008 Author Share Posted February 14, 2008 Hi there, I have 2 tables that related to this... 1) FavouriteFoods table (FavouriteFoodID [primary key], FoodID [primary key of Food table],FoodName, AnimalID[primary key of animal table]) 2) Food table (FoodID[primary key], AnimalTypeID[primary key of AnimalType table], FoodName) Does this help at all???? Thanks Link to comment https://forums.phpfreaks.com/topic/91040-insert-record-primary-key-value/#findComment-466762 Share on other sites More sharing options...
yandoo Posted February 14, 2008 Author Share Posted February 14, 2008 p.s....to get the FavouriteFoodID is easy to get as i just query total_rows of FavouriteFoods + 1..... But i need to related the FoodID to the appropriate Foodname that the user enters in the form.... Thanks Link to comment https://forums.phpfreaks.com/topic/91040-insert-record-primary-key-value/#findComment-466764 Share on other sites More sharing options...
yandoo Posted February 14, 2008 Author Share Posted February 14, 2008 Hi, there I know what you mean now, there is ONLY 1 field that has auto increment per table! So in the case of the Food table, FoodID will auto increment. For FavouriteFoods table ONLY has FavouriteFoodID that auto increments... Link to comment https://forums.phpfreaks.com/topic/91040-insert-record-primary-key-value/#findComment-466951 Share on other sites More sharing options...
revraz Posted February 14, 2008 Share Posted February 14, 2008 When you popluate the OPTION tag, just use the value=$id and have $id be the row id? Link to comment https://forums.phpfreaks.com/topic/91040-insert-record-primary-key-value/#findComment-466955 Share on other sites More sharing options...
yandoo Posted February 14, 2008 Author Share Posted February 14, 2008 Yeah but the nature of the FoodName is that it is inappropriate to be $id as it is like Whiskers Supermeat - Chicken..... Are you saying its possible to just change the value to FROM FoodName thats entered by user to FoodID?? Thanks Link to comment https://forums.phpfreaks.com/topic/91040-insert-record-primary-key-value/#findComment-466964 Share on other sites More sharing options...
revraz Posted February 14, 2008 Share Posted February 14, 2008 The name and ID are different, use them like <option value=1>Food Name</option> If I pick that, then it will send 1 as the value, not Food Name, then I can relate 1 to Food Name through a table. Link to comment https://forums.phpfreaks.com/topic/91040-insert-record-primary-key-value/#findComment-466968 Share on other sites More sharing options...
yandoo Posted February 14, 2008 Author Share Posted February 14, 2008 Hi there, If i make the change you suggested...including adjusting the update value to use the FoodID from Drop down....which is now using value of FoodID...but displaying option FoodName...i still cant update record as error message says: Cannot update as FoodID is Null... Please Help Thanks Link to comment https://forums.phpfreaks.com/topic/91040-insert-record-primary-key-value/#findComment-467132 Share on other sites More sharing options...
yandoo Posted February 14, 2008 Author Share Posted February 14, 2008 Hi there, Got it working!! I know what you now, thats brilliant i have learnt something new. Thank You very much for all your help Link to comment https://forums.phpfreaks.com/topic/91040-insert-record-primary-key-value/#findComment-467152 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.