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?

Can you not do a SELECT * on the table, and then check in the PHP to see if $row['item_type'] is set? — andrewsi
– andrewsi, Commented May 18, 2012 at 17:31
Take a look at this answer. stackoverflow.com/a/7091984/212940 — vascowhite
– vascowhite, Commented May 18, 2012 at 17:32
possible duplicate of php pdo: get the columns name of a table — vascowhite
– vascowhite, Commented May 18, 2012 at 17:34
@andrewsi if i do this, a NULL value (i.e. unanswered value) does not distinguish those from ones that do not exist in the table. — Michael
– Michael, Commented May 18, 2012 at 18:09
Ah, so it does - I didn't know that. On the other hand, $row will have a key for 'item_type', so you could check to see if that's present, instead? — andrewsi
– andrewsi, Commented May 18, 2012 at 18:27

VisioN · Accepted Answer · 2012-05-18 17:35:15Z

13

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.

answered May 18, 2012 at 17:35

VisioN

146k35 gold badges287 silver badges291 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

feeela Over a year ago

+1, but that doubles the number of queries. So it should be used with care and not in a frequent manner.

VisioN Over a year ago

True. I was also thinking that both queries could be merged in one but can't check it at the moment.

Salman Arshad · Accepted Answer · 2024-11-28 08:26:17Z

9

Use the SHOW COLUMNS query:

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

If a row is returned, the column exists.

edited Nov 28, 2024 at 8:26

answered May 18, 2012 at 18:45

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Comments

Your Common Sense · Accepted Answer · 2024-11-27 11:35:02Z

2

You can also let PDO throw exceptions and catch those to parse the MySQL-error.

/**
 * Enable PDO exceptions.
 * @see http://php.net/pdo.setattribute.php
 */
$pdoObject->setAttribute ( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

try
{
    $pdoStatement->execute();
}
catch( PDOException $e )
{
    if ($e->errorInfo[1] == 1054)
    {
        /* Column not found */
        return false;
    } else {
        /* some other PDO-error */
        throw $e;
    }
}

edited Nov 27, 2024 at 11:35

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

answered May 18, 2012 at 17:48

feeela

30k7 gold badges61 silver badges73 bronze badges

Comments

ali ozkara · Accepted Answer · 2024-11-27 11:26:59Z

-1

try this; pdo php7

function isColumnExists($column, $table) {
    try {
        
        if (empty ($table) || empty ($column)) {
            return FALSE;
        }

        $stmt = $this->db->prepare("SHOW COLUMNS FROM $table LIKE :column");
        $stmt->execute(array("column" => $column));
        $row = $stmt->fetchColumn();
        if ($row != FALSE) {
            return TRUE;
        } else {
            return FALSE;
        }

    } catch (PDOException $e) {
        return FALSE;
    }
}

edited Nov 27, 2024 at 11:26

answered Nov 27, 2024 at 11:09

ali ozkara

5,6512 gold badges29 silver badges28 bronze badges

1 Comment

Your Common Sense Over a year ago

FYI, you shouldn't return false in case of mysql error. On the contrary, you need to get that error, which will make you aware of the problem and will provide information that will let you to fix the problem

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related