Id Help

franches

I really need your help. I have a problem on my script add.php. At the start adding an entry was ok. I added 20 entries then I deleted some entries. The id is set to primary key and auto-inc in my dbase. I deleted entries which has id value of 6,7,11,12. When I tried to add an entry I noticed that the id value assigned is 11 such as

Your ID will be 11

First Name:
Last Name:
Address:
Position:

Is there somebody who knows the code for my problem and where in my script i will insert it. hope i'm not asking too much.

add.php

if($submit){

$db=mysql_connect("localhost","root");

mysql_select_db("mydb",$db);

echo "$count\n";

$sql="INSERT INTO employees (id,first,last,address,position) VALUES ('$ID','$first','$last','$address','$position')";

$result=mysql_query($sql);

  echo "<font size=4 face=arial color=#330066>\n";

foreach($_POST as $name=>$value){
if ($name!='submit')
echo "$name = $value<br><br>\n";
}

echo "<font size=5 face=arial color=red>";
echo "<center>Thank you! Information has been added to database.</font>\n";
echo "<br><br><Br>\n";

echo "<form method=\"post\" action=\"$PHP_SELF\">\n";
echo "<input type=\"submit\" value=\"add again\">\n";
echo "</form>\n";

echo "<form method=\"post\" action=\"testing6.php\">\n";
echo "<input type=\"submit\" value=\"back to main\">\n";
echo "</form>\n";
}

else{

$count=1;

$db=mysql_connect("localhost","root");
mysql_select_db("mydb",$db);
$temp=mysql_query("SELECT * FROM employees");

while($myrow=mysql_fetch_array($temp)){
$count=$count+1;//test how many records have been in database.
}

echo "<Br><Br>\n";
echo "<table boarder=0><tr><td align=right>\n";
echo "<font size=4 face=arial color=#330066>\n";
echo "<form method=post action=\"$PHP_SELF\">\n";
echo " <font color=red>Your ID will be $count\n</font><br>";
echo "<input type=\"hidden\" value=$count name=\"ID\"><br>\n";
echo "First Name: <input type=\"text\" name=\"first\"><Br><Br>\n";
echo "Last Name: <input type=\"text\" name=\"last\"><Br><Br>\n";
echo "Address: <input type=\"text\" name=\"address\"><Br><Br>\n";
echo "Position: <input type=\"text\" name=\"position\"><Br><br>\n";
echo "<input type=\"submit\" name=\"submit\" value=\"add to database\">\n";
echo "</form>\n";
echo "</td></tr></table>\n";

}

thanks in advance. hope you understand.
🙁

laserlight

$sql = "INSERT INTO employees (id,first,last,address,position)
	VALUES (NULL,'$first','$last','$address','$position')";

That way you get the auto increment to function as it should.

franches

thanks i tried it but it was no good. it still starts with id value 11 and when i displayed all the data, the info i just added which has the id value of 11 supposedly turned out to be id value 43 in the table.

laserlight

That's because you are inconsistent.

On one hand, you are using AUTO_INCREMENT.
On the other hand, you're getting the next id from the number of records in the table.

Look over [man]mysql_insert_id/man.

Roger_Ramjet

A lot of newbies have trouble understanding how an autoincrement field works:

the database engine has an index field in a hidden table where it records the last id issued.
when you try an insert the first thing that happens is that the database adds 1 to that index, updates the field, and then issues that number to your insert query.
if the insert fails the number does NOT get reset.

Thus, you can never predict what the next number will be. What you have to do is insert the record and then use mysql_insert_id or the mysql function LAST_INSERT_ID() to return the number issued.

If it is essential that you know what the number will be, or that no numbers are omitted from the sequence, then you have to create your own 'autoinc' procedure that you can control, predict, and roll-back.

create a table with one integer field.
set it to ONE LESS than the starting number that you want.
when required, look up that number and add 1 to it
insert your record with the new number
update the index field with the new number, which is the last number issued, only after your insert has succeeded.
for this to work you will need to use database transactions to lock the index field during the whole process.
you will need excellent error handling routines to cope with failed locks, dropped connections, overlapping updates and all the other things that can interupt a series of queries. How all-encompassing they need to be will depend on your platfoem and database engine.

NEVER rely on a record count to calculate the next number in a series. As you have found, 1 deleted record will foul the whole thing up. At the very least, use a query to return the highest value in the field to ascertain the last number used.