Re: [PHP] PHP + MySQL - Load last inserts
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. I'll have to check my DBs then, because the exact situation I described happened to me a few years back when I was first getting into the DB scene. Regardless, I'm fairly certain the MSSQL box we have at work behaves this way. Maybe it's a version thing, that test was done on mysql 5 (I'm sure it'll work on mysql 4.0/4.1 as well). No idea about ones before that. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP + MySQL - Load last inserts
On Mon, Mar 30, 2009 at 5:10 PM, Chris wrote:
> haliphax wrote:
>>
>> On Mon, Mar 30, 2009 at 9:13 AM, Sebastian Muszytowski
>> 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.
I'll have to check my DBs then, because the exact situation I
described happened to me a few years back when I was first getting
into the DB scene. Regardless, I'm fairly certain the MSSQL box we
have at work behaves this way.
--
// Todd
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP + MySQL - Load last inserts
haliphax wrote:
On Mon, Mar 30, 2009 at 9:13 AM, Sebastian Muszytowski
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/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP + MySQL - Load last inserts
On Mon, Mar 30, 2009 at 9:13 AM, Sebastian Muszytowski 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. Not trying to be rude, it just doesn't seem like you read any of what I wrote in my last message. Also--if you're already doing the inserts one at a time, you're not going to lose any noticeable speed by tracking the last 5 inserted rows or so. You are not working with an embedded system--this is a high-level programming language. Why is everybody always so concerned about adding two or three lines of code like it's going to slow their system to a crawl? Unless you're messing with file IO, chewing up gigantic amounts of RAM, or performing cryptography/compression, you don't need to be quite so frugal with your methods. I'm not saying you should be sloppy--just that you shouldn't completely disregard an option because it adds 1 term (NOT magnitude) to your project's Big-O Notation measure. My 2c. Go with ORDER BY asdf_id DESC if it suits your fancy. -- // Todd -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] PHP + MySQL - Load last inserts
> Date: Mon, 30 Mar 2009 14:55:08 +0200 > From: s.muszytow...@googlemail.com > To: p...@addmissions.nl > CC: php-general@lists.php.net > Subject: Re: [PHP] PHP + MySQL - Load last inserts > > Thijs Lensselink schrieb: > > Sebastian Muszytowski wrote: > > > >> Hello :) > >> > >> I have some troubles with php and mysql. I have a normal MySQL Query > >> and this returns X > 3 rows. > >> Now i want to get the last 3 inserted values. For Example i've inserted > >> > >> A, B, C, D, E > >> > >> I want to get the last inserted values, e.g. E D C (in reversed order) > >> > >> So how to do this? I already searched the web and the whole php.net site > >> but i don't see any workaround. > >> > >> Thanks in advance > >> > >> Sebastian > >> > >> > >> > > It's not really a PHP question. But here goes : > > > > SELECT column FROM `table` ORDER BY column DESC LIMIT 3 > > > Oh okay thank you very much :) > > I thought I must do this with php and some sort of mysql_fetch_asssoc or > something like this > Hey Dude, I'm sure you don't wanna complicate issues for yourself. You've been given two correct ways to go about it. I think you should try those. Ciao. _ Drag n’ drop—Get easy photo sharing with Windows Live™ Photos. http://www.microsoft.com/windows/windowslive/products/photos.aspx
Re: [PHP] PHP + MySQL - Load last inserts
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 :) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP + MySQL - Load last inserts
On Mon, Mar 30, 2009 at 7:55 AM, Sebastian Muszytowski wrote: > Thijs Lensselink schrieb: >> >> Sebastian Muszytowski wrote: >> >>> >>> Hello :) >>> >>> I have some troubles with php and mysql. I have a normal MySQL Query >>> and this returns X > 3 rows. >>> Now i want to get the last 3 inserted values. For Example i've inserted >>> >>> A, B, C, D, E >>> >>> I want to get the last inserted values, e.g. E D C (in reversed order) >>> >>> So how to do this? I already searched the web and the whole php.net site >>> but i don't see any workaround. >>> >>> Thanks in advance >>> >>> Sebastian >>> >>> >>> >> >> It's not really a PHP question. But here goes : >> >> SELECT column FROM `table` ORDER BY column DESC LIMIT 3 >> > > Oh okay thank you very much :) > > I thought I must do this with php and some sort of mysql_fetch_asssoc or > something like this 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? -- // Todd -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP + MySQL - Load last inserts
Thijs Lensselink schrieb: Sebastian Muszytowski wrote: Hello :) I have some troubles with php and mysql. I have a normal MySQL Query and this returns X > 3 rows. Now i want to get the last 3 inserted values. For Example i've inserted A, B, C, D, E I want to get the last inserted values, e.g. E D C (in reversed order) So how to do this? I already searched the web and the whole php.net site but i don't see any workaround. Thanks in advance Sebastian It's not really a PHP question. But here goes : SELECT column FROM `table` ORDER BY column DESC LIMIT 3 Oh okay thank you very much :) I thought I must do this with php and some sort of mysql_fetch_asssoc or something like this -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP + MySQL - Load last inserts
>I have some troubles with php and mysql. I have a normal MySQL Query >and this returns X > 3 rows. >Now i want to get the last 3 inserted values. For Example i've inserted > >A, B, C, D, E > >I want to get the last inserted values, e.g. E D C (in reversed order) > >So how to do this? I already searched the web and the whole php.net site >but i don't see any workaround. I guess you have a numeric primary key with auto_increment activated. Then just order your select by that primary key in descending order and limit the output to 3. For instance: SELECT * FROM table ORDER BY primary_key DESC LIMIT 0, 3; Greetins from Germany Marc -- Sync and share your files over the web for free http://bithub.net My Twitter feed http://www.twitter.com/MarcSteinert -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP + MySQL - Load last inserts
Sebastian Muszytowski wrote: > Hello :) > > I have some troubles with php and mysql. I have a normal MySQL Query > and this returns X > 3 rows. > Now i want to get the last 3 inserted values. For Example i've inserted > > A, B, C, D, E > > I want to get the last inserted values, e.g. E D C (in reversed order) > > So how to do this? I already searched the web and the whole php.net site > but i don't see any workaround. > > Thanks in advance > > Sebastian > > It's not really a PHP question. But here goes : SELECT column FROM `table` ORDER BY column DESC LIMIT 3 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
