MySQL using SELECT WHERE Primary Key = XYZ

Question

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.

Answer 1

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.