Page 1 of 1

PHP code for displaying non sequential ids

Posted: Sun Jul 26, 2009 5:20 am
by ahmedyk
Dear All,
I have a table in a database which contains the following fields :

1- id
2- student_name
3- subject
4- marks

In this table I have 4 records and id field is auto incremented, the id of my records start from 1 to 5 and id no. 3 is deleted so using php i would like to display that id 3 is missing, so how can i do that?
It will be great help if someone can help me, thank you very much appreciate it.

Re: PHP code for displaying non sequential ids

Posted: Sun Jul 26, 2009 5:52 am
by requinix
How about

5- deleted

It marks whether the record is "deleted" or not. Because in general, deleting stuff from a database is bad practice: a number of reasons, one of which is that it's hard to tell if something's missing when it's deleted.

Re: PHP code for displaying non sequential ids

Posted: Sun Jul 26, 2009 8:30 am
by Eric!
You could also write a routine to clean up the database by reorganizing the ID's after one is deleted, unless it is important to keep the ID the same because they are used elsewhere.

If you just want to look for missing holes, you could do a "SELECT * FROM `table` ORDER BY `table`.`id` ASC" then loop the data looking for missing id numbers. If the database is really big, you'd be better off rebuilding the table with a deleted field and writing a short routine to loop all the data and fill in the missing ID rows with placeholder values and marked the deleted field as 1.

Re: PHP code for displaying non sequential ids

Posted: Sun Jul 26, 2009 8:55 am
by ahmedyk
Thank you very much for giving me such a briliant idea and its working amazingly, thanks once again.