Re: PHP + MySQL - Load last inserts

haliphax wrote:

On Mon, Mar 30, 2009 at 9:13 AM, Sebastian Muszytowski <s.muszytowski@googlemail.com> wrote:

haliphax schrieb:

[..cut...]

Except when your primary key value rolls over, or fills a gap between two other rows that was left when a row was deleted/moved/etc... there has got to be a better way than grabbing rows in descending order based on the auto_increment value. Are you doing the inserts one at a time? If so, why not just use code to remember what you put in the DB?

I do the inserts one at a time, i could use code to remember but i think it would be very slow when i have too much users. The second thing that aware me from doing this is, why use/build ressources when you could use others? i think why should i use and add another system? I work with mysql already and when php+mysql have the function i need it's better and i don't waste ressources :)

Insert 100 records. Delete 50 of them at random. Now do your "grab the last few records and sort them in descending order" trick and see where it gets you.

Mysql does not re-use id's (nor does any other db as far as I'm aware, and nor should it). I don't know where you got that idea from. mysql> create table test (id int auto_increment not null primary key, name text); Query OK, 0 rows affected (0.12 sec) mysql> insert into test(name) values ('one'),('two'),('three'),('four'),('five'),('six'),('seven'),('eight'),('nine'),('ten'); Query OK, 10 rows affected (0.00 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> select * from test; +----+-------+ | id | name | +----+-------+ | 1 | one | | 2 | two | | 3 | three | | 4 | four | | 5 | five | | 6 | six | | 7 | seven | | 8 | eight | | 9 | nine | | 10 | ten | +----+-------+ 10 rows in set (0.00 sec) mysql> delete from test where id in (1,3,7); Query OK, 3 rows affected (0.00 sec) mysql> insert into test(name) values('eleven'); Query OK, 1 row affected (0.00 sec) mysql> select * from test; +----+--------+ | id | name | +----+--------+

|  2 | two    |

| 4 | four | | 5 | five |

|  6 | six    |

| 11 | eleven | | 8 | eight | | 9 | nine |

| 10 | ten    |

+----+--------+ 8 rows in set (0.00 sec) The physical order of the rows changed (as you can see) but it does not re-use the id's that were deleted. Ordering by the id will get you the last bunch inserted. If you're using innodb you'll have to be aware it's transaction specific, if you're using myisam it will be system wide. -- Postgresql & php tutorials http://www.designmagick.com/