我刚学会使用PHP PDO,遇到了以下问题:
Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
错误指的是这个代码:
if( $this->_query->execute() ){
这是我的代码:
public function query($dbUse='', $sql, $params = array(), $datatypes = array(), $orderby='', $limit=''){
$this->_error = false; //always first initialize to false
/* Check which DB will be used */
$this->_pdo = $this->_pdoPostgres;
$FlagSelectWithCount = ( substr($sql, 0, 6) == 'SELECT' ? true : false );
if( $FlagSelectWithCount ){ // received SELECT statement
if( $this->_query = $this->_pdo->prepare( "SELECT COUNT(*) as computedrow FROM ( {$sql} ) AS X", array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL) ) ){
$x = 1;
if( count($params) ){
foreach($params as $param){
$this->_query->bindValue($x, $param);
$x++;
}
}
if( $this->_query->execute() ){
$this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);
foreach ($this->_results as $obj){
$this->_count = $obj->computedrow;
}
if($this->_count){
if( $this->_query = $this->_pdo->prepare( $sql . $orderby , array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL) ) ){
$x = 1;
if( count($params) ){
foreach($params as $param){
$this->_query->bindValue($x, $param);
$x++;
}
}
if( $this->_query->execute() ){
$this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);
}else {
$this->_error = true;
}
}
}
else{
$this->_count = 0;
}
//$rows = $this->_query->fetchColumn();
//$this->_count = count($rows); //for select
} else {
$this->_error = true;
}
}
}
else{
if( $this->_query = $this->_pdo->prepare( $sql ) ){
$x = 1;
if( count($params) ){
foreach($params as $param){
$this->_query->bindValue($x, $param, $datatypes[$x-1]);
$x++;
}
}
//$this->_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//$this->_pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
if( $this->_query->execute() ){
$this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);
$this->_count = $this->_query->rowCount();
} else {
$this->_error = true;
}
}
}
/*
if( substr($sql, 0, 6) == 'SELECT' ){
$sql = "SELECT COUNT(*) FROM ( {$sql} ) AS X";
}
//$sql = "SELECT * FROM user_profile WHERE user_name='husni'";
if( $this->_query = $this->_pdo->prepare( "SELECT COUNT(*) FROM ( {$sql} ) AS X", array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL) ) ){
//echo "SELECT COUNT(*) FROM ( {$sql} ) AS X";
$x = 1;
if( count($params) ){
foreach($params as $param){
$this->_query->bindValue($x, $param);
$x++;
}
}
if( $this->_query->execute() ){
$this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);
echo $rows = $this->_query->fetchColumn();
echo ' tryCount'.count($rows);
$this->_count = count($rows); //for select
//echo ' countReturned--'.$this->_count = $this->_query->rowCount(); //for other than select
//echo '--';
} else {
$this->_error = true;
}
}
*/
return $this;
}
这意味着,与传递给函数的$param数组中的元素相比,$sql字符串中的问号(未命名标记)更少。
你可以在你的代码中添加这个测试,当它们不相等时,它会输出一条消息:
if (substr_count($sql, "?") != count($param)) {
printf ("Error: SQL has %d tokens, while %d parameters were provided.",
substr_count($sql , "?"), count($param));
}
这个测试不是防弹的,因为字符串文字中可能有问号:这些数字会被错误地计算在内。
但这可以在调试代码时发挥作用。
注意:您可以使用$x =>
编写循环
像这样:
foreach($params as $x => $param){
$this->_query->bindValue($x+1, $param);
}