4

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?

Michael
  • 2,276
  • 15
  • 49
  • 80
  • Can you not do a SELECT * on the table, and then check in the PHP to see if $row['item_type'] is set? – andrewsi May 18 '12 at 17:31
  • 2
    Take a look at this answer. http://stackoverflow.com/a/7091984/212940 – vascowhite May 18 '12 at 17:32
  • possible duplicate of [php pdo: get the columns name of a table](http://stackoverflow.com/questions/5428262/php-pdo-get-the-columns-name-of-a-table) – vascowhite May 18 '12 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 May 18 '12 at 18:09
  • 1
    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 May 18 '12 at 18:27

4 Answers4

14

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.

VisioN
  • 143,310
  • 32
  • 282
  • 281
  • +1, but that doubles the number of queries. So it should be used with care and not in a frequent manner. – feeela May 18 '12 at 17:47
  • True. I was also thinking that both queries could be merged in one but can't check it at the moment. – VisioN May 18 '12 at 17:49
7

Use the SHOW COLUMNS query:

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

If a row is returned, the column exists.

Salman A
  • 262,204
  • 82
  • 430
  • 521
3

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 )
{
    switch( $e->errorInfo[1] )
    {
        case 1062:
            /* A PRIMARY or UNIQUE key is already assigned */
            return false;
        case 1054:
            /* Column not found */
            return false;
        default:
            /* some other PDO-error */
            log_error($e->getMessage(), 'mysql');
            return false;
    }
}
feeela
  • 29,399
  • 7
  • 59
  • 71
0

hope this will help.something you can do with PHP Mysql

<?php

$link = mysql_connect($server, $username,$password);

if(!link))  
{  
    exit('could not connect');  
}

if(!mysql_select_db($database))
{  
    exit('could not select the database');  
}
$sql="SELECT * FROM myrow WHERE mycolumn='".$columname."'";
$result=mysql_query($sql);
if(!$result){
if(mysql_errno($link)==1054){
            //column does not exist
    echo "<br />available";
    }
}
elseif($nr= mysql_num_rows($result)){
    if ($nr > 0){
                    //column exist
        die('<br />name exists');
    }
}
else
{
            //column does not exist
    echo "<br />available";
}
?>
aimme
  • 6,385
  • 7
  • 48
  • 65