Column number in mysql 'WHERE' condition-CodePudding

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.

CodePudding user response：

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");

CodePudding user response：

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.