MySQL using SELECT WHERE Primary Key = XYZ

mysql, php, primary-key, where-clause

Solution

I am guessing that you do not want to supply the name of the primary key, and instead have it selected automatically. In this case, you should first get the primary key's column name, and then make the simple query passing on the primary key's name.

// first query
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';

// second query
SELECT * FROM $table WHERE $primary=$row

where `$primary` is the column name you got by running the first query.

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.

Problem

Is it possible to use "Primary Key" instead of a column in a `SELECT * WHERE` query in MySQL? Example situation: ``` table1: name (P) | surname table2: page (P) | content ``` I have tried (PHP) ``` SELECT * FROM $table WHERE PRIMARY=$row ``` and nothing has been returned (despite PHPMyAdmin highlighting it). I want to do this because I want to make a `getDbData` function where I only need to specify the table (`$table`) and the row (`$row`, row name is primary key's value) for it to return the single row I need.

Original source