Broken ID Number

alanrenouf

We have a table with an ID number at the start which is auto increment.

We have deleted a record from the table and want some help displaying the records one by one.

ID | Name |

1 | Alan |
2 | Eric |
4 | Tim |
5 | Giles |

So how do we get a number which is not the ID number if you know what i mean ?

Code is basicaly this which displays our records:

Start
Count is 1
show record where id = count
Add 1 to count
Next button
loop

so i get

1 Alan

New Page

2 Eric

New Page

3 Error !

Any Ideas ?

Sorry about the wording of this problem, its a hard one to write down.

Alan

van__

What you are doing is a bad idea.

AUTO_INCREMENT is really just meant for key association (which is why it must be primary to auto_inc in the first place)
It's not intended to be a userID or something for you to rely on.

It's just a unique index.
And once the associated value dissapears, it's gone forever.

Personally...
I wouldn't do it this way at all though.
I'd just do a LIMIT CLAUSE

say "SELECT * FROM myTable LIMIT $offset,1"
where offset is the last viewed record starting at one.

If you are dead set on doing it your current way, then you should switch your table type to innoDB rather than myISAM which is the MySQL default. Of course, you'll need MySQL 4 for innoDB, so get ready to upgrade if you haven't done so all ready.

Anon

You can do what you want in a while loop. Some example code is below:

while (list($short_ref, $names) = mysql_fetch_row($result))
{
print ("<OPTION VALUE='$short_ref' >$names</OPTIONS>" );
print ("\n");
}
?>

Basically this code builds a HTML select box, but the principal is identical.

Variables:
$result is my database result set.
$short_ref would be your ID
$names would be your name field.

The while statment says: as long as can retrieve a row (msql_fetch_row) and split is into $short and $names (list($short_refm $names)) do the print statement.

Hope this helps,

Justin

Anon

FYI, you can quite happily run Innodb on version 3.23.x

Justin

van__

Ya... I keep getting lost in all the new features and branches of MySQL lately.