Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Detect Auto Increment Column

Tags:

mysql

pdo

How can I get the auto_increment column name from a table (NOT THE LAST INSERT ID)?

for example:

create table members (member_id int auto_increment, name char(50), primary key(member_id));

What can I do to get the member_id from table members.

I am making a php class, and I am going to add a method that will allow you to get this like this:

$members->findById(123);

It should know to find the auto increment column and build a query based on that then do the query.

like image 658
Get Off My Lawn Avatar asked Mar 15 '13 21:03

Get Off My Lawn


Video Answer


2 Answers

You can get the column with

show columns from members where extra like '%auto_increment%'

The first column Field is your auto_increment column name.

$sql = "show columns from members where extra like '%auto_increment%'";
$sth = $dbh->prepare($sql) or die($dbh->error());
$sth->execute() or die($dbh->error());
$row = $sth->fetch();
$column = $row['Field'];
like image 59
Olaf Dietsche Avatar answered Nov 08 '22 16:11

Olaf Dietsche


SELECT  column_name 
FROM    INFORMATION_SCHEMA.COLUMNS 
WHERE    table_name = "members" 
AND     extra = "auto_increment";

Try this!

like image 39
beck03076 Avatar answered Nov 08 '22 18:11

beck03076