help with insert...

[nishmgopal]

Hi guys,

 

I am trying to insert values from the form into two different tables.  The skill Name goes to the skill table, in which the Skill_ID is auto increment.  and then I want to insert the weight and the last Skill_ID into the ID_Table.

 

My code so far is below, i no i have to use mysql_last_insert_ID to get the last ID in the skill table, but im not sure how....can anyone please help.

 

<?php
  echo "</p>
</br>
<form id='addmore' method='post' action='add_1.php'>
  <table width='200' border='0' nowrap>
  <tr>
  <td></td>
  <td align='center'><strong>Skill Name</td>
  <td><strong>Weight</td>
    <tr>
      <td nowrap='nowrap'> <font color='#4096EE'><strong>Skill 1</font></td>
      <td bgcolor='#85c329' div id='input'><input type='text' name='skill1' id='skill1' /> </td>
     <td><select name='weight1' id='weight1'>
        <option value='1'>1</option>
        <option value='2'>2</option>
        <option value='3'>3</option>
        <option value='4'>4</option>
        <option value='5'>5</option>
      </select>      </td>
  <td > <input type='submit' name='add1' id='add1' value='Add' /></td>

      </form>
   </tr>
  </table>";
  ?>

<?php  

$skill1=$_POST['skill1'];
$weight=$_POST['weight1'];

mysql_query("INSERT INTO Skill_ID 
(Skill_ID, Skill_Name) VALUES('NULL', '$skill1' ) ") 
or die(mysql_error());  

mysql_query("INSERT INTO ID_Table 
(ID, Job_ID, Skill_ID, Weight) VALUES('NULL', '1','LAST_INSERT_ID', '$weight' ) ") 
or die(mysql_error());  


echo "Data Inserted!";

?>

 

thanks

[Yesideez]

As soon as you call mysql_query() to INSERT you can call mysql_insert_id() to return the auto_increment value of the INSERT

 

mysql_query("INSERT INTO table (`field`) VALUES ('value')");
$rowID=mysql_insert_id();

 

That will return (in $rowID) the auto_increment value of the INSERT allowing you to use that to insert into your second table.

 

<?php  
$skill1=$_POST['skill1'];
$weight=$_POST['weight1'];

mysql_query("INSERT INTO Skill_ID (Skill_Name) VALUES('$skill1' ) ") or die(mysql_error());
$rowID=mysql_insert_id();
if ($rowID>0) {
  mysql_query("INSERT INTO ID_Table (Job_ID, Skill_ID, Weight) VALUES('1','".$rowID."', '$weight' ) ") or die(mysql_error());
  echo "Data Inserted!";
} else {
  echo 'Error inserting data';
}
?>

 

Note I've also remove the two fields inserting as NULL as they don't have to be specified - as they're auto_increment MySQL will fill them in for you.