How to check if column does not exist using PHP, PDO, MySQL?

Question

In my application I have a generic query that applies to multiple users. There are instances where the table structure may differ between users. I have a query that I only want to apply to the users where the column exists in their table.

function get_item($user_id) {

    global $dbh;

    $sth = $dbh->query ("SELECT item_type FROM items WHERE user_id = '$user_id'");

    $row = $sth->fetch();

    $item_type = $row['item_type'];

    return $item_type;

}

If the column 'item_type' does not exist in my table, I want to ignore it, and set the $item_type variable to NULL.

For these users, I am getting the error on the query line of code:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'item_type' in 'field list' in /item_display.php:5

Any ideas?

Answer 1

I don't know if it helps you, but you can try this:

if (count($dbh->query("SHOW COLUMNS FROM `items` LIKE 'item_type'")->fetchAll())) {
    $sth = $dbh->query ("SELECT item_type FROM items WHERE user_id = '$user_id'");
    $row = $sth->fetch();
    $item_type = $row['item_type'];
} else {
    $item_type = null;
}

It checks if the column exists and performs the task.

Answer 2

Use the SHOW COLUMNS query:

SHOW COLUMNS FROM <table> WHERE Field = '<column>'

If a row is returned, the column exists.