Use mysql_insert_id in single query

Question

Ok, don't know if this is simple in practice as it is in theory but I want to know.

I have a single INSERT query were by in that query, i want to extract the AUTO_INCREMENT value then reuse it in the same query.

For example

//values to be inserted in database table
$a_name = $mysqli->real_escape_string($_POST['a_name']);
$details = $mysqli->real_escape_string($_POST['details']);
$display_type = $mysqli->real_escape_string($_POST['display_type']);
$getId = mysqli_insert_id();

//MySqli Insert Query
$insert_row = $mysqli->query("INSERT INTO articles (a_name,details,display_type,date_posted) VALUES('$a_name','$details','$display_type$getId',CURRENT_TIMESTAMP)");

Apparently, am getting a blank value(I know because the mysqli_insert_id() is before the query, but I've tried all i could but nothing has come out as i want. Can some please help me on how to achive this

What prevents you to put `mysqli_insert_id()` *after* the query? And note that it should be PDO: `$getId = $mysql->lastInsertId();` — umka, May 30 '15 at 20:28

Tarquin · Answer 1 · 2015-05-30T21:30:52.583

1

From my knoweldge this cant be done. Because no query has been run, MySQL is unable to return the ID of said query.

You could use a classic approach, pull the id of the previous record and add 1 to it, this is not a great solution as if a record is deleted, the auto increment value and the last value +1 may differ.
Run multiple queries and then use the insert_id (MySQLi is different to what you are using, you are best using $db->lastInsertId(); as mentioned in the comments.
Run a query before hand and store it as a variable;

SELECT auto_increment FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'tablename'

I strongly recommend Option 2, it is simply the cleanest and most reliable method for what you are looking to achieve.

edited May 30 '15 at 21:30

answered May 30 '15 at 21:03

Tarquin

482
2
6
21

1

1 and 3 may be problematic if there are multiple users trying to insert rows at the same time. – Tomaso Albinoni May 30 '15 at 21:26
Correct. Option 2 is the only one worth relying on, especially if you expect multiple users. I will further clarify this opinion. – Tarquin May 30 '15 at 21:30
This sounds great, since its some kind of control panel(CMS) for a website that will be used by one user, then this won't be a problem. Thanks alot – Zaid E. May 30 '15 at 21:32

score 0 · Answer 2 · answered May 30 '15 at 20:33

0

I don't think this can be done. You'll have to first insert the row, then update display_type, in two separate queries.

answered May 30 '15 at 20:33

Tomaso Albinoni

1,003
1
8
19

score 0 · Accepted Answer · edited May 23 '17 at 12:31

0

It seems the value required for $display_type is :$display_type + (max(id) + 1).

In order to get the max_id you'll have to do this query before :

$sql = "SELECT id FROM articles ORDER BY id DESC LIMIT 1";
$result = mysqli->query($sql);
$maxid = $result->fetch_array(MYSQLI_NUM);

// $maxid[0] will contains the value desired

// Remove the mysqli_insert_id() call - Swap $getid by ($maxid[0] + 1)
// and u're good to go

N.B. update the name of ur primary key in the query $sql.

EDIT :

Assuming the weakness of the query and the quick resarch i did.

Try to replace $sql by (don't forget to Update DatabaseName & TableName values) :

$sql = SELECT `AUTO_INCREMENT`
       FROM  INFORMATION_SCHEMA.TABLES
       WHERE TABLE_SCHEMA = 'DatabaseName'
       AND   TABLE_NAME   = 'TableName';

That Should do it . More info on the link below :

Stackoverflow : get auto-inc value

edited May 23 '17 at 12:31

Community

1
1

answered May 30 '15 at 20:47

Falt4rm

915
6
21

This seems to be working but problem comes when a row is deleted lets say row number 5 is deleted, the next entry will add its self to 5 instead of the previous which will be 4 by then, is there any way i can sort this – Zaid E. May 30 '15 at 21:17
Another thing i've noticed is that the `$maxid` is based on the total number of rows in the table but not on the auto_increment value – Zaid E. May 30 '15 at 21:21
Edited - That should fix these issues like Tarquin mentionned aswell. – Falt4rm May 30 '15 at 21:24
Btw the data u stored(the concatenation of the curr_id) introduces complexity and shouldn't be duplicated in ur DB. if u're interested to learn more about it - Read the great post from PerformanceDBA :http://stackoverflow.com/questions/30446230/how-to-sum-new-and-last-inserted-entry-with-same-id-and-insert-result-in-new-ent/30446926#30446926 – Falt4rm May 30 '15 at 21:46

score 0 · Answer 4 · answered May 30 '15 at 21:38

Thanks guys for your opinions, out of final copy, paste, edit and fix; here is the final working code(solution)

`

//values to be inserted in database table
$a_name = $mysqli->real_escape_string($_POST['a_name']);
$details = $mysqli->real_escape_string($_POST['details']);
$display_type = $mysqli->real_escape_string($_POST['display_type']);

//Select AUTO_INCREMENT VALUE
$sql = "SELECT `AUTO_INCREMENT`
       FROM  INFORMATION_SCHEMA.TABLES
       WHERE TABLE_SCHEMA = 'chisel_bk'
       AND   TABLE_NAME   = 'articles'";

$result = $mysqli->query($sql);
$maxid = $result->fetch_array(MYSQLI_NUM);
$getId = $maxid[0];

//MySqli Insert Query
$insert_row = $mysqli->query("INSERT INTO articles (a_name,details,display_type,date_posted) VALUES('$a_name','$details','$display_type$getId',CURRENT_TIMESTAMP)");

This happens to do the magic!!!

`

This is silly. If you're going to run two queries anyways, don't "guess" the id beforehand. Since the goal of "a single query" cannot be accomplished, you should get the insert ID *after* doing the insert and simply update the row afterwards. — Aaron Cicali, Nov 24 '20 at 19:34

Use mysql_insert_id in single query

4 Answers4