Problem with mysql_insert_id()

geekisthenewsexy · September 29, 2010

hello, im asking for your help again. i have a problem with this mysql_insert_id() function im using. it's used to get the values of id's and insert it to a field of another table right?but in my case where i have 4 tables and using the function to get the unique id,it no longer gets the id on the third table which is supposed to be doing so that this id will be inserted to the field of the final table. these are dynamic textboxes im working on by the way..here's my php code:

html

<html>
<head>
<script language="JavaScript">	

function AddTextBox()
{
document.getElementById('container').innerHTML+='<input type="text" size="15" maxlength="15" name=block[]><br>';	
}
function AddTextBox2()
{
document.getElementById('container2').innerHTML+='<input type="text" size="15" maxlength="15" name=room[]><br>';	
}

</script>
</head>
<body>
<form name="form1" method="post" action="adnew.php">
<input type="hidden" name="cid">
Course:<input type="text" name="course">
<input type="hidden" name="yid">
Year: <select name="year">
            <option value="1">1</option>
            <option value="2">2</option>
          <option value="3">3</option>
<option value="4">4</option>
</select>
  <table width="23%" border="0">
    <tr> 
      <td width="37%" height="1" colspan="2"> <input type="hidden" name="block_id">
        Block: 
        <input name="button" type="button" onClick="AddTextBox();" value="Add textbox"></td>
    </tr>
    <tr> 
      <td height="21"><div id="container"></div></td>
    </tr>
  </table>
  <table width="23%" border="0">
    <tr>
      <td width="64%">Room: 
        <input name="button2" type="button" onClick="AddTextBox2();" value="Add textbox"></td>
    </tr>
    <tr>
      <td><div id="container2"></div></td>
    </tr>
  </table>
  
  <br><br><input type="Submit" name="submit" value=" Add ">
</form> 
</body>
</html>

php

<?php
include("dbcon.php");
?>
<?php
$id_c=$_POST['cid'];
$block=$_POST['block'];
$block_id=$_POST['block_id'];
$room=$_POST['room'];
$intblock=0;
$introom=0;

$sql=mysql_query("INSERT INTO course VALUES ('$id_c','$_POST[course]')") or die (mysql_error());
$id_c = mysql_insert_id(); 

$sql=mysql_query("INSERT INTO year VALUES ('$_POST[yid]','$id_c','$_POST[year]')") or die (mysql_error());
$_POST['yid'] = mysql_insert_id(); 

while(count($block)>$intblock) {
if (($block[$intblock]<>"")){
$sql=mysql_query("INSERT INTO block VALUES ('$block_id', '$_POST[yid]', '".$block[$intblock]."')") or die (mysql_error());
mysql_query($sql);
}
else{
echo "Block ".($intblock+1)." is missing values and cannot be inserted.";
}
$intblock=($intblock+1);
}
$block_id = mysql_insert_id(); 
while (count($room)>$introom) {
if (($room[$introom]<>"")){
$sql=mysql_query("INSERT INTO room VALUES ('$block_id', '".$room[$introom]."')") or die (mysql_error()); // this is the 4th table..
mysql_query($sql);
}
else{
echo "Room ".($introom+1)." is missing values and cannot be inserted.";
}
$introom=($introom + 1);
}

echo "Successfully added.";
echo "<br><a href='index.php'>Add another</a>";
?>

$block_id = mysql_insert_id();

this is what i'm having problems with..

$sql=mysql_query("INSERT INTO room VALUES ('$block_id', '".$room[$introom]."')") or die (mysql_error());

and this is for my 4th table..

i have found out that you have to put the function after an INSERT command,but in my case, i have a switch statement and after i tried putting it inside the switch, i get a message that says "duplicate entry 1 for b_id...etc.." any suggestions?advise? :-\

geekisthenewsexy · September 29, 2010

in addition to my last post, i have researched on forums regarding the same problem and i stumbled across this comment on stackoverflow by dan_l

For example, let's say two people, A & B, are adding questions to your db at close to the same moment and you're using the last_insert_id() to determine the id of the person's question. What happens when they both submit their question at the same time? Is the last_insert_id() from person A or B?

One method to overcome this issue is to use a "pre-defined" id or guid in addition to or in place of the auto incremented id.

that's exactly the root of my problem. because as i am using dynamic text boxes to get user's input, the block field there varies with the user's input. there can be only one block,two,three,or five blocks in each year level and every block has different numbers of room. so when i tried to input 2 blocks,and 4 rooms, the id on the 4th table which is room returned 0 values when i checked it when it should have contained the id's 1,2,3,4.. so do you have any suggestions? and how do i use

One method to overcome this issue is to use a "pre-defined" id or guid in addition to or in place of the auto incremented id.

?

Sign In

Problem with mysql_insert_id()

Recommended Posts

geekisthenewsexy

Link to comment

Share on other sites

geekisthenewsexy

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information