Insert Record - primary key value

yandoo · 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

Cep · February 14, 2008

Code please (And why doesn't your table have its own primary key?)

yandoo · 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

revraz · 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.

yandoo · 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

yandoo · 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

yandoo · 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...

revraz · February 14, 2008

When you popluate the OPTION tag, just use the value=$id and have $id be the row id?

yandoo · 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

revraz · February 14, 2008

The name and ID are different, use them like

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.

yandoo · 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

yandoo · 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

Sign In

Insert Record - primary key value

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information