Column number in mysql 'WHERE' condition

Question

How is it possible to select the first column in the Where clause. I am trying to make a php function to retrieve table data based on the id, yet since the titles of the id columns are different in various tables, I need to refer to the first column in the Where clause as the first column is always the id column.

The scenario would be something like the following, but it throws errors and says that there is an error in the SQL syntax.

$stmt = $this->conn->prepare("SELECT * FROM $table WHERE column(1) = :id");

Thanks in advance.

AFAIK, MySQL doesn't have any such function. It doesn't seem very robust to depend on the order of columns like this. — Barmar, Oct 13 '21 at 22:35
The only times you can refer to columns by number is in `GROUP BY` and `ORDER BY`. — Barmar, Oct 13 '21 at 22:37
Not a very good idea. But that query could be dynamically generated via `information_schema.columns.ordinal_position`, if you really want to do this. — Jon Armstrong, Oct 13 '21 at 22:40
Related: https://stackoverflow.com/q/52559333/2943403 (unanswered duplicate) — mickmackusa, Oct 14 '21 at 04:08

score 2 · Answer 1 · answered Oct 13 '21 at 22:40

2

I don't think there's a built-in way to do this. But you can query INFORMATION_SCHEMA.COLUMNS to get the column names.

$col_stmt = $this->conn->prepare("
    SELECT column_name 
    FROM information_schema.columns 
    WHERE table_schema = DATABASE() 
        AND ordinal_position = 1 
        AND table_name = :table");
$col_stmt->execute([':table' => $table]);
$first_col = $col_stmt->fetchColumn();
$stmt = $this->conn->prepare("SELECT * FROM `$table` WHERE `$first_col` = :id");

answered Oct 13 '21 at 22:40

Barmar

741,623
53
500
612

How about in a single trip to the database? – mickmackusa Oct 14 '21 at 04:05
1

You could do the same thing in a stored procedure. Can't think of any other way to do it with a single trip to the DB. – Barmar Oct 14 '21 at 04:28

H. M.. · Accepted Answer · 2021-10-14T03:23:29.023

2

Since there is no built-in way to do this, I came up with the following code block to get the first column and then use it in my SELECT statements.

$stmt = $this->conn->query("SHOW columns FROM $table");
        return $stmt->fetch(PDO::FETCH_LAZY)[0];

I think this strategy is a bit shorter than Barmar's, though his is completely right and to the point.

edited Oct 14 '21 at 03:23

answered Oct 14 '21 at 03:10

H. M..

568
6
15

Column number in mysql 'WHERE' condition

2 Answers2