PHP: Get primary key of table?

Question

Hello!

Is there a way to get the name of primary key field from mysql-database? For example:

I have a table like this:

+----+------+
| id | name |
+----+------+
| 1  | Foo1 |
| 2  | Foo2 |
| 3  | Foo3 |
+----+------+

Where the field id is primary key (it has auto increment but I can't use that). How can I retrieve fields name "id" in php?

Martti Laine

Answer 1

Here is the Primary key Column Name

SELECT k.column_name
FROM information_schema.table_constraints t
JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
  AND t.table_schema='YourDatabase'
  AND t.table_name='YourTable';

Answer 2

I think if you fetch data from your database the primarykey is already in it access it like $yourdatabase->id; I dont know which method you trying to fetsch the datas but the primary key is also in that datas. Access it with id

Answer 3

SELECT kcu.column_name
FROM   information_schema.key_column_usage
WHERE  table_schema = schema()             -- only look in the current db
AND    constraint_name = 'PRIMARY'         -- always 'PRIMARY' for PRIMARY KEY constraints
AND    table_name = '<your-table-name>'    -- specify your table.

Answer 4

A better way is to use SHOW KEYS since you don't always have access to information_schema. The following works:

SHOW KEYS FROM table WHERE Key_name = 'PRIMARY'

Column_name will contain the name of the primary key.

Answer 5

For a PHP approach, you can use mysql_field_flags

$q = mysql_query('select * from table limit 1');

for($i = 0; $i < mysql_num_fields(); $i++)
    if(strpos(mysql_field_tags($q, $i), 'primary_key') !== false)
        echo mysql_field_name($q, $i)." is a primary key\n";

Answer 6

Is there any code for getting Primary Key from ODBC (Access)???? I tried odbc_primarykeys() not working for Access... Any other option?????????