Auto Fit Detection

Question

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

eg:

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

What can I do to get member_id from table elements.

I am making a php class and I am going to add a method that will allow you to get the following:

 $members->findById(123); 

He needs to know in order to find the auto-increase column and build a query based on this, and then execute the query.

Answer 1

You can get a column with

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

The first Field column 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']; 

Answer 2

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

Try it!

Answer 3

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

Something like the following (but this is the simplified code I just tested, not part of the 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; } 

Answer 4

You will need to prepare the statement dynamically based on information_schema, and 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;