PHP PDO Check for multiple columns in mySQL table and create the ones that don't exist

Question

Looking for a way to check whether a table in a database has been assembled with the columns it required.

I build this function:

function verifydbtable($table,$fields){
    global $fsdbh;
    foreach($fields as $field => $type){
        $verify = $fsdbh->prepare("ALTER TABLE `$table` ADD `$field` $type;");
        $verify->execute();
    }
    return 1;
}

Where $table is the table to update and $fields contains an array of all the fields to check. $type contains the type of field eg. VACHAR(20).

The function works for each field until it comes across a field that already exists. Then it returns false.

The problem is that it then won't check the remaining columns from the array. How do we make it continue checking the remaining fields.

Caused by the fact that if a column already exists it will return an error #1060 - Duplicate column name 'subtitle' which is what it should do. We then need to skip to the next item in the array if this error is returned.

In layman's terms we need this but of course this is not a mySQL statement.

prepare("ALTER TABLE `$table` ADD IF NOT EXIST `$field` $type");

No error messages. The function is working perfectly. The problem is that if one of the fields already exists the error will be returned: #1060 - Duplicate column name 'url' . And then rather than keep on going with the next item in the array the entire function returns false. We need to get around this problem. Is there some way of telling it to continue IF ERROR. — Walrus
– Walrus, Commented Aug 23, 2012 at 17:35
You can check INFORMATION_SCHEMA database for column names already in the table and remove them from your array before trying alter statement. — piotrm
– piotrm, Commented Aug 23, 2012 at 18:18

kapa · Accepted Answer · 2012-08-23 17:23:19Z

2

You could try specifying IGNORE for the query:

$fsdbh->prepare("ALTER IGNORE TABLE `$table` ADD `$field` $type;");

answered Aug 23, 2012 at 17:23

kapa

78.9k21 gold badges166 silver badges178 bronze badges

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

2 Comments

Walrus Over a year ago

Does not fix the problem. The SQL returns an error if the column already exists and then exits the function rather than checking the next item in the array. We need to get around this so that it keeps going even if their is an error. Any ideas?

kapa Over a year ago

@RobinKnight Could you check what the PDO::ATTR_ERRMODE is set to? Maybe it throws an exception that you have to catch. See prodigitalson's answer.

prodigitalson · Accepted Answer · 2012-08-23 17:57:32Z

1

Use a try catch:

function verifydbtable($table,$fields){
    global $fsdbh;
    $errors = array();
    foreach($fields as $field => $type){
        $verify = $fsdbh->prepare("ALTER TABLE `$table` ADD `$field` $type;");
        try {
          $verify->execute();
        } catch (PDOException $e) {
           // you might want to check and make sure its actually the right error
           // but you can figure that out if when you need to do so
           // we'll also store the field name in an array jsut in case we want to use it
           $errors[] = $field;
        }
    }

     return 1;
}

answered Aug 23, 2012 at 17:57

prodigitalson

60.6k10 gold badges106 silver badges118 bronze badges

Collectives™ on Stack Overflow

PHP PDO Check for multiple columns in mySQL table and create the ones that don't exist

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related