CODEWITHSUNDEEP
mysqlpdo
Get Off My Lawn
Get Off My Lawn

Reputation: 36351

Detect Auto Increment Column

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.

Upvotes: 6

Views: 5696

Answers (4)

Bill Karwin
Bill Karwin

Reputation: 562871

I implemented this type of function as part of the Zend_Db component when I worked on the Zend Framework. I found that accessing the INFORMATION_SCHEMA is too slow, so I had to use DESCRIBE instead.

Something like the following (but this is simplified code I just tested, not part of Zend Framework):

function getAutoIncColumn($table)
{
    global $dbh;

    $stmt = $dbh->query("DESCRIBE `$table`");
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
      if (strstr($row["Extra"], "auto_increment")) {
        return $row["Field"];
      }
    }
    return null;
}

Upvotes: 1

Olaf Dietsche
Olaf Dietsche

Reputation: 74098

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'];

Upvotes: 9

beck03076
beck03076

Reputation: 3308

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

Try this!

Upvotes: 1

Michael Benjamin
Michael Benjamin

Reputation: 2915

You'll have to prepare the statement dynamically based on the information_schema, then execute it.

PREPARE stmt FROM concat(
 'select * from members where ', 
 (select COLUMN_NAME 
  from INFORMATION_SCHEMA.COLUMNS 
  where table_name='members' and EXTRA like '%auto_increment%'),
  '=?'
 );
EXECUTE stmt;

Upvotes: 0

Related Questions