1

I have an mysql table with an column called "id" - this column has auto increment set.

I only want to know how i can get, with PHP and PDO, the next auto increment value, without doing a INSERT INTO query. (i found some questions about this - but none without INSERT INTO and only based on mysqli).

Thanks!

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Mike
  • 163
  • 1
  • 4
  • 19
  • Why do you need it? You can't use it if you simply read it in advance, because you'll run into concurrency issues then – Mark Baker Aug 29 '13 at 10:34
  • @MarkBaker There are a number of reasons you would want to do this, perhaps using the id as the seed to a display id. The concurrency issue is easily solved by using a transaction. – lsl Jan 16 '15 at 04:24
  • A transaction won't be retained across multiple http requests, ie a GET to display and a POST to insert/update – Mark Baker Jan 16 '15 at 07:57

5 Answers5

1

Why do you need this ?

If you need, you can get the current auto increment value like this, but I don't see a situation where this would be needed.

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

More info:

Community
  • 1
  • 1
Vlad Preda
  • 9,780
  • 7
  • 36
  • 63
1

One line solution : 

$var = $db->query("SHOW TABLE STATUS LIKE 'my_table'")->fetch(PDO::FETCH_ASSOC)['Auto_increment'];
Meloman
  • 3,558
  • 3
  • 41
  • 51
0

The very statement of the question is wrong.
You actually don't need it, as without the INSERT query this value makes absolutely no sense.

Although I do understand that simplicity of PHP language welcomes anyone into the world of web-programming, it still requires some knowledge. One of the things you have to understand is the purpose and the meaning of the autoincremented unique identifier. Which is not what you think. It makes sense ONLY after insert and nowhere else.

So, there could be only two possible scenarios:

  • either you really need a freshly generated id - then just insert a record, get that id and use it
  • or you are confusing this value with something else and thus don't need it at all
allen213
  • 2,267
  • 2
  • 15
  • 21
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • 1
    This answer just says the same thing as the ones below "I don't see a situation where this would be needed." equates to what your are saying in your bullet points. I think your comment should have said using the suggested query wasn't advisable. – allen213 Aug 29 '13 at 11:03
0

I can see reasons why you would want / need this. We use it as part of security for new clients which keeps the 'keys' unique without having to send additional queries to the DB.

$q = $db->query("SHOW TABLE STATUS LIKE 'yourtable'");
$next = $q->fetch(PDO::FETCH_ASSOC);
echo $next['Auto_increment'];
Sabyre
  • 97
  • 10
0

be careful because between you ask the next ID and when you want to use it, someone concurent query can insert a new record and the ID you get is no longer valid

let's say when you want to add new person record when you didn't know the name, want to put newPerson

get the id write to db newPerson

but this could happend:

get1 the id get2 the id write1 to db newPerson write2 to db newPerson

where 1 and 2 are two concurent scripts that happens to execute at the same time

easy fix would be to use transactions, to avoid this

Dan Mihail Matei
  • 31
  • 2