MySQL problems!

neofox

Hey was hoping someone could shed some light on a problem i have with MySQL database.

Ok this is my situation.

I have a table cars, in that table there is 2 Fields one is ID and the other is MAKE. My ID field is set to auto_increment so each time i add a record it increases giving me something like

1 - Ford
2 - Honda
3 - Mitsubishi
4 - Peugot

Problem is i also have a page that deltes records and say if i delete record 1 and then add a new record my table then looks like this

5 - Vauxhall
2- Honda
3 - Mitsubishi
4 - Peugot

How can i stop it from doing this and make put the 5th record after the 4th like

2- Honda
3 - Mitsubishi
4 - Peugot
5 - Vauxhall

Someone please help this is driving me nutz 😕

hermzz

try

mysql_query("SELECT * FROM cars ORDER BY id");

after id you can also put ASC or DESC which orders from higher to lower or the other way.

is that what you want?

neofox

erm not really, well could be but i may not know how to use it properly! :p

K yeh that code sorts the results from 1 - 5 excellent but its only usefull when getting the list of whats in the database, if i then do

INSERT INTO cars VALUES ('0', '$make')

if record 1 has been deleted then record 6 goes into its spot in the database

hermzz

ah yes, you want to insert a new recrod with id 1

you have to do it manually, when you do auto_increent ou have two options, set the autoincrement to 0 so that next time it'll insert the record with a id=1 but then you'll have to change it or you'll have two records with id=2, so my sugestion is to just insert the record not with the last_insert_id, in its place put a 1

neofox

well not really

What i really want it to do is just not use that space again ever and just use the last one!

I`m thinking about instead of deleting the record but just setting make to null

hermzz

... ok that sounds like a good idea
that never happend to me.. sorry i coulnd't help.

neofox

if you were creating a field ID that will go 1,2,3,4....... what would the SQL Query you would use?

At the moment i`m using

CREATE table cars
(

id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
make TEXT

);