How can I get the auto incrementing field name or the primary key fieldname from a mysql table?

Question

In PHP, how do I get the field name of the field that's been set as to auto increment when a new rec is added to it?

In most cases, it's the same as the PRIMARY_KEY of the table but not necessarily always.

So this question has 2 parts with the second one branching into a 3rd part.

1- How to get the name of the auto-incrementing field name...

2- How to get the name of the primary_key field name...

2.1 How to get the primary_key(s) info when a table uses more than one field as its primary key...

Answer 1

if you want to get the primary key column of the table, you can use this code:

SELECT k.COLUMN_NAME
FROM information_schema.table_constraints t
LEFT JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
    AND t.table_schema=DATABASE() 
    AND t.table_name='tbName';    -- the name of your table

To get the auto-incremented field, try this:

SELECT Auto_increment 
FROM information_schema.tables 
WHERE table_name   = 'tbName'  
  AND table_schema = DATABASE();  

Answer 2

You can get the table information using the SHOW COLUMNS FROM table. Something like this:

$res = $mysqli->query('SHOW COLUMNS FROM tablename');

while($row = $res->fetch_assoc()) {
  if ($row['Extra'] == 'auto_increment')
    echo 'Field with auto_increment = '.$row['Field'];
  if ($row['Key'] == 'PRI')
    echo 'Field with primary key = '.$row['Field'];
}