generate ID for records

[naughtybynature]

[PHP]

<?php

$db = mysql_connect(" localhost", "root","");
mysql_select_db ("sistem bank soalan",$db);
$result = mysql_query("SE LECT * FROM soalan",$db);
$count_emp=mysq l_num_rows($res ult);

if ($count_emp==0)
$id=1;
else
$id=$count_emp+ 1;

echo($id)
?>


[/PHP]
Hi

I attempted to generate ID for my records. It seems that
they really work well when adding record into database. HOwever
problem arises when I tried to delete data that located in the middle
Based on the codes above, the $count_emp variable will return the number of records exist in the database.

The initial value for $id is 0. If there are 10 records for example in the database,($coun t_emp=10), the new value of id would be 11(because $id=$count_emp+ 1).But whenever I want to delete in the middle record, the value of $count_emp will become 9. The new record then will take the last id value which is 10 and this record surely fail to be inserted into the database as there's already exist record with id=10.

Those code might explain you to understand my problem much better. I also need codes that enable the new entry record to replace the location of the deleted record (meaning that the new inserted record would take the id value of the deleted record) and the next new record would then be inserted normally.

[LaoDe]

Hi,

this is a mySQL question and should be postede in the mySQL-formun !

How ever, your problem vonsists of 2 parts:

1. identify max(id) ! Have look in mySQL-Docu under aggregate functions or 'group by' ! this orientation might look a bit missleading, since max retrieves only 1 value, but it belongs to the aggregate functions.
2. to fill the gap of numbers, you get by deleting records, you have to code yourself or look for a user-defined function from somebody else. I don't no any standard-function for this in any DB-System.

LaoDe

[ronverdonk]

Basic question should be: why are you so dependent on the generated id? Since it is auto-generated and kept server side, you cannot change it or influence it, except by creating a new table and copying the old taable into it.
Using max() is also very unreliant in this case. I quote from an old reply of mine on this:

The MAX() will not work, it will only give you the highest number used, and that plus 1 is not alway the next assigned number. Example:
You have a table with auto_increment column 'id' and values:
1
2
3
The MAX(id) will give you 3.
Now you delete 3. The MAX(id) value will give you 2.
After inserting a new row, the table will be:
1
3
4
and the MAX(id) will return 4.

Only solution for you is to do the indexing of the IDs yourself, and that's a hell of a task to maintain.

Ronald :cool: