PHP PDO检查mySQL表中的多个列，并创建不存在的列

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).

其中$table是要更新的表，$fields包含要检查的所有字段的数组。$type包含字段的类型。VACHAR(20)。

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

函数作用于每个字段，直到遇到一个已经存在的字段。然后它返回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.

因为如果一个列已经存在，它将返回一个错误#1060 -重复的列名'subtitle'，这是它应该做的。然后，如果返回这个错误，我们需要跳到数组中的下一个条目。

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

用外行的话来说，我们需要这个，但这当然不是mySQL语句。

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

2 个解决方案

#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;
}

#2

You could try specifying IGNORE for the query:

您可以尝试为查询指定IGNORE:

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

#1