PHP 绑定(bind) PDO 数组

Question

我正在尝试通过一个数组用 PDO 组装一个数据库插入，但我只是在某处遗漏了它，并且正在寻找一些帮助来解决我所遗漏的问题。该数组是一个关联数组。抛出的错误是:

Fatal error:  Uncaught exception \'PDOException\' with message \'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens\' in /var/www/html/themonastery.org/mot/receiver.php:70
Stack trace:
#0 /var/www/html/themonastery.org/mot/receiver.php(70): PDOStatement->execute()
#1 {main}
  thrown in /var/www/html/themonastery.org/mot/receiver.php on line 70

我使用的代码是:

    /** PDO Stuff **/

    //require and instantiate pdo instance
    require_once "dependancies/pdo.func.php";
    $dbh = pdo_connect();

    //implode query
    $keys = implode(',', array_keys($clean));
    $vals = implode(',', array_fill(0, count($clean), '?'));
    
    $insert = array_values($clean);

    //pdo prepare
    $sth = $dbh->prepare("INSERT INTO backupDB ($keys) VALUES ($vals)");

    //set loop condition
    $waiting = true; 
    while($waiting) {
        try {
        $dbh->beginTransaction();

        $i=1;
        foreach($clean as $insert) {

            // bindvalue is 1-indexed, so $k+1
            $sth->bindValue($i++, $insert, PDO::PARAM_STR);

            $sth->execute();
            sleep(1);
        }
        
        $dbh->commit();
        $waiting = false;

        } catch(PDOException $e) {
        if(stripos($e->getMessage(), 'DATABASE IS LOCKED') !== false) {
            //sleep for 0.25 seconds and try again.
            $dbh->commit();
            usleep(250000);
        } else {
            $dbh->rollBack();
            throw $e;
        }
        }
    }

这是关联数组，

array (
  'full_name' => 'First Middle Last Suffix',
  'first_name' => 'First',
  'middle_name' => 'Middle',
  'last_name' => 'Last Suffix',
  'address' => 'The Address',
  'city' => 'City',
  'state' => 'State Abbr',
  'zip' => 'Zip code',
  'country' => 'Country Abbr',
  'email' => 'dev@null.com',
  'password' => 'd41d8cd98f00b204e9800998ecf8427e',
  'ordinationDate' => '2012-04-15',
  'birthday' => '1982-14-01',
  'isValidAge' => '1',
)

根据要求，这里有一个 $keys 和 $vals

的 var_dump

$keys = string(123) "full_name,first_name,middle_name,last_name,address,city,state,zip,country,email,password,ordinationDate,birthday,isValidAge"
$vals = string(27) "?,?,?,?,?,?,?,?,?,?,?,?,?,?"

这是数据库中的列名

    id  full_name   first_name  middle_name last_name   address city    state   zip country email   password    ordinationDate  birthday    isValidAge  sex timestamp   ulc_edit_time   osc_sync    guid

Answer 1

改变这个:

$i=1;
foreach($clean as $insert) {

    // bindvalue is 1-indexed, so $k+1
    $sth->bindValue($i++, $insert, PDO::PARAM_STR);

    $sth->execute();
    sleep(1);
}

为此:

$i=1;
foreach($clean as $insert) {

    // bindvalue is 1-indexed, so $k+1
    $sth->bindValue($i++, $insert, PDO::PARAM_STR);

    sleep(1);
}

$sth->execute();

PDO::execute() 需要在所有 bindValues() 的末尾(参见 http://php.net/manual/en/pdostatement.execute.php#example-995)

另外，我有以下函数来绑定(bind)正确的数据类型(需要根据您的情况进行一些更改):

public function bindValue($key = null, $value = null) {
    if($key == null) {
        return;
    }

    if(is_int($value)) {
        $param = PDO::PARAM_INT;
    } elseif(is_bool($value)) {
        $param = PDO::PARAM_BOOL;
    } elseif(is_null($value)) {
        $param = PDO::PARAM_NULL;
    } elseif(is_string($value)) {
        $param = PDO::PARAM_STR;
    } else {
        $param = FALSE;
    }

    $this->_query->bindValue($key, $value, $param);
}