PHP select the biggest `id` value from MySQL table

php mysql select

12,299

Solution 1

Here is the right code you have to use.

mysql_query('INSERT INTO table_x(id_x) VALUES(NULL)');
$id = mysql_insert_id();
mysql_query("INSERT INTO table_y(id_x) VALUES($id)");

Solution 2

Depending on the context either

SELECT id FROM my_table ORDER BY id DESC LIMIT 1

or mysql_insert_id() in PHP or (SELECT LAST_INSERT_ID()) in MySQL

12,299

Author by

Cosmi

Updated on June 08, 2022

Comments

Cosmi almost 2 years
I have a table with more than 300 000 rows and I need to select the highest value for the column 'id'. Usually, I will do like this:
```
SELECT id FROM my_table ORDER BY id DESC
```
... but this will cause slow queries and I don't want to use it. Is there a different way to solve this situation? id is auto increment and primary key.

Later Edit: It seems my full code is quite bad written, as I deduct from your comments. Below I posted a sample of the code I'm working and the tables. Can you suggest a proper way to insert the last ID+1 of table_x in two tables (including table_x itself). I have to mention that the script will be running more than once.
```
TABLE_X          TABLE_Y
------------     ----------
id_x | value     id_y | id_x
------------     ----------
   1 | A            1 | 3
   2 | B            
   3 | C

<?php
for($i=0; $i<10; $i++){
    $result_x = mysql_query('SELECT id_x FROM table_x ORDER BY id_x DESC');
    $row_x = mysql_fetch_array($result_x);

    $next = $row_x['id_x'] + 1;
    mysql_query('INSERT INTO table_x(id_x) VALUES("'.$next.'")');
    mysql_query('INSERT INTO table_y(id_x) VALUES("'.$next.'")');
}
?>
```
SteAp about 12 years

LAST_INSERT_ID() is connection specific and thus only in very special situations useful.
Mikulas Dite about 12 years

@SteAp Of course. Though it has to be considerably faster then searching for the max(). No need to downvote here folks.
SteAp about 12 years

Ah, OK, I now understand your comment. Grated! Please place a note in your answer - and I'm going to upvote again. If only to unlock voting for me for this answer.
Mikulas Dite about 12 years

@SteAp Again, it is context dependent, you cannot use it everywhere. The OP did not mention what he needs to do with the id and when, so this was as good suggestion as any. Obviously it cannot be used with another resource.
SteAp about 12 years

I propose to enhance the word 'context' with a bit more detail. Anyway, I can't upvote unless you edit your answer - somehow ;-) It is locked for me - due to my downvote.
Your Common Sense about 12 years

Reverted it to first state. Mentioning speed makes no sense here. Both methods has COMPLETELY DIFFERENT USAGE, they AREN'T interchangeable. You cannot list them this way.
Cosmi about 12 years

I need to insert the ID in two tables and I can't find a proper solution. I revised my post, can you take a look please? Thanks!
SteAp about 12 years

First, insert the main records and save the result of mysql_insert_id() in a variable. Then use this permanently valid insert-ID in the next two inset statement. All in one single script.
Cosmi about 12 years

Oh, that's so easy. Yep, I have to use mysql_insert_id(). Thank you!
Your Common Sense about 12 years

auto increment feature intended to be used automatically, not manually. Otherwise you will break your database consistency. Always let database assign the id