SQLSTATE [HY093]:参数号无效:参数未定义(PDO) [英] SQLSTATE[HY093]: Invalid parameter number: parameter was not defined (PDO)
问题描述
我知道:这已经死了.
但是，相信我，我已经研究了很长时间来解决这个问题.我想要实现的是一个与PDO一起使用的PDO MySQL数据库包装器，我可以将其合并到我的代码中.我的主要问题尤其来自两个函数，以及我要达到的参数的实际绑定.我声明了两个功能而不是一个功能的原因是，尽管我付出了很多努力，但我仍无法找出哪个功能正在尝试解决此问题.我已经var_dump ed的变量已经确认它不是变量，而是其他东西.不过，我首先遇到此错误的事实意味着某事必须与代码有误.
But, believe me, I've been researching for quite a while on how to fix this one. What I'm trying to achieve is a PDO MySQL Database wrapper for use with PDO that I can just incorporate into my code. My main problem stems from two functions in particular, as well as the actual binding of the parameters I'm trying to reach. The reason why I state two functions as opposed to one is, despite my efforts, I haven't been able to spot which one is attempting the problem. The variable's I've var_dumped have confirmed that it's not the variable, it's something else. Still, the fact that I'm getting this error in first place means that something has to be wrong with the code.
展览A: fetch( $table, $columns, $whereArgs )
此功能的目的是简单地获取一行.这是通过接受要获取的行的表以及需要专门用于完成任务的任何列和子句来实现的.提交参数后，将调用一个或两个循环，以动态形成查询.
The purpose of this function is to simply fetch a row. This is achieved by accepting the table of the row to fetch, along with whatever columns and where-clauses are needed in order to accomplish the task specifically. Once the parameters have been submitted one or two loops are invoked which dynamically form the query.
public function fetch( $table, $columns, $whereArgs )
{
if ( $whereArgs === NULL && $columns === NULL ) return false;
$select = "SELECT ";
$where = " WHERE ";
$iQuery = 0;
$sqlParams = array();
$columnCount = count( $columns ) - 1;
foreach( $whereArgs as $key => $value )
{
$paramKey = sprintf( ':%s', $key );
$where .= sprintf( "`%s`= %s", $key, $paramKey );
$sqlParams[ "{$paramKey}" ] = sprintf( "%s", $value );
if ( $iQuery <= $columnCount )
{
$select .= sprintf( '`%s`', $columns[ $iQuery ] );
$select .= ', ';
}
else
{
$select .= ' ';
}
++$iQuery;
}
if ( $iQuery <= $columnCount )
{
for( ; $iQuery < $columnCount; ++$iQuery )
{
if ( $iQuery < $columnCount )
{
$select .= sprintf( '`%s`', $columns[ $iQuery ] );
$select .= ', ';
}
else
{
$select .= ' ';
}
}
}
$select .= sprintf( "FROM `%s`", $table );
$query = $select . $where;
return $this->doQuery( $query, $sqlParams, TRUE, QueryType::Row );
}
附件B: doQuery( $query, $sqlParams, $return = FALSE, $queryType = QueryType::None )
此函数相对简单:它所做的只是绑定值并执行该语句，同时迅速检查要返回的类型(返回类型为'row'，'column'或'all'由类QueryType指定，该类超出了此问题的范围)，然后返回所要求的内容.
This function is relatively simple: all it does is bind values and execute the statement, while promptly checking for which type to return (the return types, which are either 'row', 'column', or 'all', are specified by the class QueryType, a class which outside the scope of this issue), and then returning whatever is asked for.
protected function doQuery( $query, $sqlParams, $return = FALSE, $queryType = QueryType::None )
{
$statement = $this->mConnection->prepare( $query );
foreach( $sqlParams as $param => $value )
{
$statement->bindValue( $param, $value );
}
$statement->execute( );
if ( $return )
{
switch( $queryType )
{
case QueryType::Row:
return $statement->fetch( );
case QueryType::Column:
return $statement->fetchColumn( );
case QueryType::All:
return $statement->fetchAll( );
case QueryType::None:
return $statement;
default:
return false;
}
}
}
附件C: test.php
这只是我为测试数据库而编写的一个小测试脚本.
This is simply a little test script I wrote to test the database.
$database = new Database( 'evolve_admin' );
$res = $database->fetch(
'evol_users',
array( 'user.id', 'user.email', 'user.firstname' ),
array( 'user.email' => 'test1234@test.com' )
);
var_dump( $res );
其他评论
我了解到，有为的有毛病我的代码，我只是失去了，究竟它可能是.就我的调试技能而言，我已经对这个问题进行了相当多的研究，看来这个错误很常见.我的主要目标是使该包装器正常工作，如果有人发现代码本身存在任何错误(包括特别是不在此问题范围之内的错误)，请告诉我.
I've learned that there is something wrong with my code, I'm just lost as to what exactly it could be. As far as my debugging skills go, I have researched this issue quite a bit, and it seems that this error is very common. My main goal is to get this wrapper working, and if anyone sees any faults within the code itself (including ones which outside the scope of this issue in particular), please let me know.
对于任何对此有帮助的人:非常感谢.
To anyone who offers a hand in this one: thank you very much.
推荐答案
我认为您忘记了那里的一些空格:
I think you're forgeting some spaces there:
$where .= sprintf( "`%s`= %s", $key, $paramKey );
下次使用$wher变量是将其附加到$select变量.
The next time you used the $whervariable was when you append it to the $select one.
如果更多，其中arg在哪里:
If more that one where arg the where is :
WHERE `%s`= %s`%s`= %s`%s`= %s`%s`= %s`%s`= %s
您没有对您的SELECT一代思想犯错.
顺便说一句，对于您选择的if ( $iQuery <= $columnCount )代，您有两个相同的循环和测试.一个在where循环中，另一个在外面.有什么用?
You did not do the error with your SELECT generation thought.
BTW you have the two same loops ànd testfor your select generation if ( $iQuery <= $columnCount ). One in the where loop and another outside. What is the use?
当然，我忘了指出为什么会有这个异常错误: 在
And of course i forgot to point out why you have this freaking error: It's in
$sqlParams[ "{$paramKey}" ] = sprintf( "%s", $value );
您创建一个看起来像这样的表:array ( "{:akey}" => "avalue")(我认为该值是字符串.
为什么要使用大括号({})，它会完全更改键名(应为:keyname而不是{:keyname}
You create a table that will look like that: array ( "{:akey}" => "avalue") (i considered the value as string.
Why did you use curly brackets ({} ), it completly change the key name (should be :keynamenot {:keyname}
心情很好，因此这是您提取方法的简化版本(未经测试，但应该可以正常工作)
Edit 2: Was in a good mood so here is a simplified version of your fetch method.(not tested but should work fine)
/*
* $whereArgs default value is an array, so you can call a select
* without an empty array supplied if you does not have some where clause arguments
* The separator is how the element will be binded alltogether in the where clause
*/
public function fetch( $table, $columns, $whereArgs = array(), $separator= 'AND' )
{
/* We return false, if the columns variable is not set, or is not an array,
* or (if it is an array) does not contain anything
* or the $whereArgs is not and array (it would mean something bad have been given)
*/
if ( false == isset($columns) || false == is_array($columns)
|| 0 == count($columns) || false == is_array($whereArgs) )
{
return false;
}
$select = "SELECT";
$from = " FROM `$table`";
$where = " WHERE ";
/* SELECT generation */
for ( $columIt = 0; $columIt < count($columns); $columIt++)
{
$select .= " " . $columns[$columIt];
// We check if we need to add a ','
if ( $columIt+1 < count($columns) )
{
$select .= ",";
}
}
/* WHERE clause generation */
$sqlParams = array();
$whereIt = 0;
foreach( $whereArgs as $key => $value )
{
$stripedKey = preg_replace('/\\./', '_', $key);
$where .= " $key= :$stripedKey";
$sqlParams[ ":$stripedKey" ] = "$value";
// We check if we need to add a where separator
if ( $whereIt +1 < count($whereArgs ) )
{
$select .= " $separator";
}
$whereIt++;
}
/* the generated where clause is only printed if $whereArgs
* is not an empty array
*/
$query = $select . $from . ((0<count($whereArgs))?$where:"");
return $this->doQuery( $query, $sqlParams, TRUE, QueryType::Row );
}
BTW没有看到您的测试样本，但是参数名称不能包含.".字符
Edit 3: BTW didn't see your test sample, but param name can't contain '.' char
没找到您解决的方法，我添加了一个pre_replace来替换.".在您的子集中. 在回显键时也要摆脱'`'字符，否则查询将失败
Edit 4: Didn't see yous solved it, I added a pre_replace to replace the '.' in your subset. Also get rid of the '`' char when echoing the key, the query will fail otherwise
`user.email`=:arg
不喜欢=)
user.email=:arg or user.`email`=:arg
Preferred(`)用于在列名中包含特殊字符，因此像以前一样，该列名与任何现有的字符都不匹配.
Prefered (`) is used to include special char in column name, so used like before, the column name didn't match to any existing one.
而不是剥离键来创建参数和参数数组.通过利用$ whereIt计数器，您可以使用一种安全的方法来防止出现误导性的小人物:
Edit 5: Instead of stripping your key to create your arguments and argument array. You could use a safe way what that prevent the apparition of misleading characheters, by taking advantage of the $whereIt counter:
$where .= " $key= :arg_$whereIt";
$sqlParams[ ":arg_$whereIt" ] = "$value";
致谢
这篇关于SQLSTATE [HY093]:参数号无效:参数未定义(PDO)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
