php MySQLi 中的 SELECT * FROM
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/750648/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT * FROM in MySQLi
提问by johnnietheblack
My site is rather extensive, and I just recently made the switch to PHP5 (call me a late bloomer).
我的网站相当广泛，我最近才切换到 PHP5（称我为大器晚成者）。
All of my MySQL query's before were built as such:
我之前所有的 MySQL 查询都是这样构建的：
"SELECT * FROM tablename WHERE field1 = 'value' && field2 = 'value2'";
This made it very easy, simple and friendly.
这使它变得非常容易、简单和友好。
I am now trying to make the switch to mysqli for obvious security reasons, and I am having a hard time figuring out how to implement the same SELECT * FROMqueries when the bind_paramrequires specific arguments.
出于明显的安全原因，我现在正在尝试切换到 mysqli，并且我很难弄清楚如何SELECT * FROM在bind_param需要特定参数时实现相同的查询。
Is this statement a thing of the past?
这种说法是否已成为过去？
If it is, how do I handle a query with tons of columns involved? Do I really need to type them all out every time?
如果是，我该如何处理涉及大量列的查询？我真的需要每次都把它们都打出来吗？
回答by Alec
This was already a month ago, but oh well.
这已经是一个月前了，但哦，好吧。
I could be wrong, but for your question I get the feeling that bind_paramisn't really the problem here. You always need to define some conditions, be it directly in the query string itself, of using bind_paramto set the ?placeholders. That's not really an issue.
我可能是错的，但对于你的问题，我觉得这bind_param并不是真正的问题。您总是需要定义一些条件，无论是直接在查询字符串本身中，bind_param用于设置?占位符。这真的不是问题。
The problem I had using MySQLi SELECT *queries is the bind_resultpart. That's where it gets interesting. I came across this post from Jeffrey Way: http://jeff-way.com/2009/05/27/tricky-prepared-statements/(This link is no longer active). The script basically loops through the results and returns them as an array — no need to know how many columns there are, and you can still use prepared statements.
我在使用 MySQLiSELECT *查询时遇到的问题是bind_result部分。这就是它变得有趣的地方。我从 Jeffrey Way 那里看到了这篇文章：http://jeff-way.com/2009/05/27/tricky-prepared-statements/（此链接不再有效）。该脚本基本上遍历结果并将它们作为数组返回——无需知道有多少列，您仍然可以使用准备好的语句。
In this case it would look something like this:
在这种情况下，它看起来像这样：
$stmt = $mysqli->prepare(
'SELECT * FROM tablename WHERE field1 = ? AND field2 = ?');
$stmt->bind_param('ss', $value, $value2);
$stmt->execute();Then use the snippet from the site:
然后使用站点中的代码段：
$meta = $stmt->result_metadata();
while ($field = $meta->fetch_field()) {
$parameters[] = &$row[$field->name];
}
call_user_func_array(array($stmt, 'bind_result'), $parameters);
while ($stmt->fetch()) {
foreach($row as $key => $val) {
$x[$key] = $val;
}
$results[] = $x;
}And $resultsnow contains all the info from SELECT *. So far I found this to be an ideal solution.
而$results现在包含了所有来自信息SELECT *。到目前为止，我发现这是一个理想的解决方案。
回答by tpdi
"SELECT * FROM tablename WHERE field1 = 'value' && field2 = 'value2'";
becomes
变成
"SELECT * FROM tablename WHERE field1 = ? && field2 = ?";
which is passed to the $mysqli::prepare:
传递给$mysqli::prepare：
$stmt = $mysqli->prepare(
"SELECT * FROM tablename WHERE field1 = ? && field2 = ?");
$stmt->bind_param( "ss", $value, $value2);
// "ss' is a format string, each "s" means string
$stmt->execute();
$stmt->bind_result($col1, $col2);
// then fetch and close the statement
OP comments:
OP评论：
so if i have 5 parameters, i could potentially have "sssis" or something (depending on the types of inputs?)
所以如果我有 5 个参数，我可能会有“sssis”或其他东西（取决于输入的类型？）
Right, one type specifier per ?parameter in the prepared statement, all of them positional (first specifier applies to first ?which is replaced by first actual parameter (which is the second parameter to bind_param)).
对，?准备好的语句中每个参数一个类型说明符，所有这些说明符都是位置说明符（第一个说明符适用于 first ?，它被第一个实际参数（这是 的第二个参数bind_param）替换）。
mysqli will take care of escaping and quoting (I think).
mysqli 将负责转义和引用（我认为）。
回答by olle
While you are switching, switch to PDO instead of mysqli, It helps you write database agnositc code and have better features for prepared statements.
当您切换时，切换到 PDO 而不是 mysqli，它可以帮助您编写数据库无关代码，并为准备好的语句提供更好的功能。
Bindparam for PDO: http://se.php.net/manual/en/pdostatement.bindparam.php
PDO 的 Bindparam：http://se.php.net/manual/en/pdostatement.bindparam.php
$sth = $dbh->prepare("SELECT * FROM tablename WHERE field1 = :value1 && field2 = :value2");
$sth->bindParam(':value1', 'foo');
$sth->bindParam(':value2', 'bar');
$sth->execute();
or:
或者：
$sth = $dbh->prepare("SELECT * FROM tablename WHERE field1 = ? && field2 = ?");
$sth->bindParam(1, 'foo');
$sth->bindParam(2, 'bar');
$sth->execute();
or execute with the parameters as an array:
或将参数作为数组执行：
$sth = $dbh->prepare("SELECT * FROM tablename WHERE field1 = :value1 && field2 = :value2");
$sth->execute(array(':value1' => 'foo' , ':value2' => 'bar'));
It will be easier for you if you would like your application to be able to run on different databases in the future.
如果您希望您的应用程序将来能够在不同的数据库上运行，这对您来说会更容易。
I also think you should invest some time in using some of the classes from Zend Framwework whilst working with PDO. Check out their Zend_Dband more specifically [Zend_Db_Factory][2]. You do not have to use all of the framework or convert your application to the MVC pattern, but using the framework and reading up on it is time well spent.
我还认为您应该在使用 PDO 的同时花一些时间使用 Zend Framwework 的一些类。查看他们的Zend_Db，更具体地说是 [Zend_Db_Factory][2]。您不必使用所有框架或将您的应用程序转换为 MVC 模式，但使用该框架并阅读它是值得的。
回答by kquinn
Is this statement a thing of the past?
这种说法是否已成为过去？
Yes. Don't use SELECT *; it's a maintenance nightmare. There are tons of other threads on SO about why this construct is bad, and how avoiding it will help you write better queries.
是的。不要使用SELECT *; 这是一个维护噩梦。关于为什么这个结构不好，以及如何避免它会帮助你编写更好的查询，SO 上有很多其他线程。
See also:
也可以看看：
回答by soulmerge
You can still use it (mysqli is just another way of communicating with the server, the SQL language itself is expanded, not changed). Prepared statementsare safer, though - since you don't need to go through the trouble of properly escaping your values each time. You can leave them as they were, if you want to but the risk of sql piggybacking is reduced if you switch.
您仍然可以使用它（mysqli 只是与服务器通信的另一种方式，SQL 语言本身是扩展的，而不是更改的）。但是，准备好的语句更安全 - 因为您不需要每次都经历正确转义值的麻烦。如果您愿意，您可以保留它们原样，但如果您切换，SQL 捎带的风险会降低。
回答by Sugumar Venkatesan
you can use get_result()on the statement.
你可以get_result()在语句上使用。
回答by mbuster
I was looking for a nice and complete example of how to bind multiple query parameters dynamically to any SELECT, INSERT, UPDATE and DELETE query. Alecmentions in his answer a way of how to bind result, for me the get_result() after execute() function for SELECT queries works just fine, and am able to retrieve all the selected results into an array of associative arrays.
我正在寻找一个很好的完整示例，说明如何将多个查询参数动态绑定到任何 SELECT、INSERT、UPDATE 和 DELETE 查询。Alec在他的回答中提到了一种如何绑定结果的方法，对我来说，SELECT 查询的 execute() 函数之后的 get_result() 工作得很好，并且能够将所有选定的结果检索到关联数组的数组中。
Anyway, I ended up creating a function where I am able to dynamically bind any amount of parameters to a parametrized query ( using call_user_func_array function) and obtain a result of the query execution. Below is the function with its documentation (please read before it before using - especially the $paremetersTypes - Type specification charsparameter is important to understand)
无论如何，我最终创建了一个函数，我可以在其中将任意数量的参数动态绑定到参数化查询（使用 call_user_func_array 函数）并获得查询执行的结果。下面是函数及其文档（请在使用前阅读 - 特别是$paremetersTypes - 类型规范 chars参数对于理解很重要）
/**
* Prepares and executes a parametrized QUERY (SELECT, INSERT, UPDATE, DELETE)
*
* @param[in] $dbConnection mysqli database connection to be used for query execution
* @param[in] $dbQuery parametrized query to be bind parameters for and then execute
* @param[in] $isDMQ boolean value, should be set to TRUE for (DELETE, INSERT, UPDATE - Data manipulaiton queries), FALSE for SELECT queries
* @param[in] $paremetersTypes String representation for input parametrs' types as per http://php.net/manual/en/mysqli-stmt.bind-param.php
* @param[in] $errorOut A variable to be passed by reference where a string representation of an error will be present if a FAUILURE occurs
* @param[in] $arrayOfParemetersToBind Parameters to be bind to the parametrized query, parameters need to be specified in an array in the correct order
* @return array of feched records associative arrays for SELECT query on SUCCESS, TRUE for INSERT, UPDATE, DELETE queries on SUCCESS, on FAIL sets the error and returns NULL
*/
function ExecuteMySQLParametrizedQuery($dbConnection, $dbQuery, $isDMQ, $paremetersTypes, &$errorOut, $arrayOfParemetersToBind)
{
$stmt = $dbConnection->prepare($dbQuery);
$outValue = NULL;
if ($stmt === FALSE)
$errorOut = 'Failed to prepare statement for query: ' . $dbQuery;
else if ( call_user_func_array(array($stmt, "bind_param"), array_merge(array($paremetersTypes), $arrayOfParemetersToBind)) === FALSE)
$errorOut = 'Failed to bind required parameters to query: ' . $dbQuery . ' , parameters :' . json_encode($arrayOfParemetersToBind);
else if (!$stmt->execute())
$errorOut = "Failed to execute query [$dbQuery] , erorr:" . $stmt->error;
else
{
if ($isDMQ)
$outValue = TRUE;
else
{
$result = $stmt->get_result();
if ($result === FALSE)
$errorOut = 'Failed to obtain result from statement for query ' . $dbQuery;
else
$outValue = $result->fetch_all(MYSQLI_ASSOC);
}
}
$stmt->close();
return $outValue;
}
usage:
用法：
$param1 = "128989";
$param2 = "some passcode";
$insertQuery = "INSERT INTO Cards (Serial, UserPin) VALUES (?, ?)";
$rowsInserted = ExecuteMySQLParametrizedQuery($dbConnection, $insertQuery, TRUE, 'ss', $errorOut, array(&$param1, &$param2) ); // Make sure the parameters in an array are passed by reference
if ($rowsInserted === NULL)
echo 'error ' . $errorOut;
else
echo "successfully inserted row";
$selectQuery = "SELECT CardID FROM Cards WHERE Serial like ? AND UserPin like ?";
$arrayOfCardIDs = ExecuteMySQLParametrizedQuery($dbConnection, $selectQuery, FALSE, 'ss', $errorOut, array(&$param1, &$param2) ); // Make sure the parameters in an array are passed by reference
if ($arrayOfCardIDs === NULL)
echo 'error ' . $errorOut;
else
{
echo 'obtained result array of ' . count($arrayOfCardIDs) . 'selected rows';
if (count($arrayOfCardIDs) > 0)
echo 'obtained card id = ' . $arrayOfCardIDs[0]['CardID'];
}
