Page 1 of 1

Insert autoincremented field into another table?

Posted: Tue Mar 31, 2009 6:14 pm
by erika
I am trying to create a (my first) normalized database. I have executed the following command in my script:

Code: Select all

$query = "INSERT INTO `product_t` (mfg_id, mfg_prod_id, product_name, product\
_price, product_size, color_id, pip_color, decoration_id, product_is_set, produ\
ct_sides, product_image_small, product_image_large) VALUES ('$mfg_id', '$mfg_pr\
od_id', '$product_name', '$product_price', '$product_size', '$color_id', '$pip_\
color', '$decoration_id', '$product_is_set', '$product_sides', '$product_image_\
small', '$product_image_large')";
 
mysql_query($query);
 
 
Now I want to update another table, products_to_category_t, with the new product_id (generated automatically with auto-increment) and the appropriate categories selected from the form.

I didn't know how to record the category numbers, so I displayed them as below (each category can be a subcategory of other categories):

Code: Select all

function display_categories($categories) {
 
    $topcats = array();
    $subcats = array();
    $cats = array();
 
    $i = 0;
 
    while ($x = mysql_fetch_array($categories, MYSQL_ASSOC)) {
 
      $cats[$i] = array();
      array_push($cats[$i], $x['category_id']);
      array_push($cats[$i], $x['category_name']);
      array_push($cats[$i], $x['category_subcat_of']);
 
      $i++;
    }
 
    $size = sizeof($cats);
 
    $n = 1;
 
    for ($i = 0; $i < $size; $i++) {
 
      if ($cats[$i][2] == NULL) {
 
        print '<input type="radio" name="category' . $n . '" value="' . $cats[$i
][0] . '">' . $cats[$i][1] . "<br>\n";
        $n++;
 
        for ($x = 0; $x < $size; $x++) {
 
          if ($cats[$x][2] == $cats[$i][0]) {
 
            print "&nbsp;&nbsp;&nbsp;" . '<input type="radio" name="category' .
$n . '" value="' . $cats[$x][0] . '">' . $cats[$x][1] . "<br>\n";
            $n++;
          }
 
        }
 
      }
 
    }
 
}
 
So the category variables are $category1 through $category# where # is however many categories have been displayed.

I can't figure out how to get the auto-incremented number from the product_t table into the second table--or not without doing another query on the database, which seems like it isn't the right thing to do.

Am I going about this wrong? Any assistance and pointers would be greatly appreciated.

--Erika

Re: Insert autoincremented field into another table?

Posted: Tue Mar 31, 2009 7:50 pm
by Bill H
After you do the insert, immediately after,

Code: Select all

 
mysql_select_db($dbmOne, $Link);
// insert record in table One
 
$Query = "SELECT LAST_INSERT_ID()";  
$Result = mysql_query($Query, $Link); 
$Row = mysql_fetch_array($Result);  
$NewID = 0 + $Row[0];                   // this is the newly created record
 
mysql_select_db($dbmTwo, $Link);
// update record in table Two with newly created record number from table One
 
$NewID is the last autoincrement created in the selected database, regardless of table. You can then update your other table using that value as needed.