php 如何回显 MySQLi 准备好的语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/962986/
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
How to echo a MySQLi prepared statement?
提问by Alec
I'm playing around with MySQLi at the moment, trying to figure out how it all works. In my current projects I always like to echo out a query string while coding, just to make sure that everything is correct, and to quickly debug my code. But... how can I do this with a prepared MySQLi statement?
我现在正在玩 MySQLi，试图弄清楚它是如何工作的。在我目前的项目中，我总是喜欢在编码时回显查询字符串，以确保一切正确，并快速调试我的代码。但是……我怎么能用准备好的 MySQLi 语句来做到这一点？
Example:
例子：
$id = 1;
$baz = 'something';
if ($stmt = $mysqli->prepare("SELECT foo FROM bar WHERE id=? AND baz=?")) {
$stmt->bind_param('is',$id,$baz);
// how to preview this prepared query before acutally executing it?
// $stmt->execute();
}I've been going through this list (http://www.php.net/mysqli) but without any luck.
我一直在浏览这个列表（http://www.php.net/mysqli）但没有任何运气。
EDIT
编辑
Well, if it's not possible from within MySQLi, maybe I'll stick with something like this:
好吧，如果在 MySQLi 中不可能，也许我会坚持这样的事情：
function preparedQuery($sql,$params) {
for ($i=0; $i<count($params); $i++) {
$sql = preg_replace('/\?/',$params[$i],$sql,1);
}
return $sql;
}
$id = 1;
$baz = 'something';
$sql = "SELECT foo FROM bar WHERE id=? AND baz=?";
echo preparedQuery($sql,array($id,$baz));
// outputs: SELECT foo FROM bar WHERE id=1 AND baz=something
Far from perfect obviously, since it's still pretty redundant — something I wanted to prevent — and it also doesn't give me an idea as to what's being done with the data by MySQLi. But I guess this way I can quickly see if all the data is present and in the right place, and it'll save me sometime compared to fitting in the variables manually into the query — that can be a pain with many vars.
显然远非完美，因为它仍然非常多余——我想防止这种情况——而且它也没有让我知道 MySQLi 对数据做了什么。但是我想通过这种方式我可以快速查看所有数据是否都存在并且位于正确的位置，并且与手动将变量拟合到查询中相比，它可以为我节省一些时间 - 对于许多变量来说这可能会很痛苦。
采纳答案by karim79
I don't think you can - at least not in the way that you were hoping for. You would either have to build the query string yourself and execute it (ie without using a statement), or seek out or create a wrapper that supports that functionality. The one I use is Zend_Db, and this is how I would do it:
我不认为你可以 - 至少不是你希望的方式。您要么必须自己构建查询字符串并执行它（即不使用语句），要么寻找或创建支持该功能的包装器。我使用的是Zend_Db，这就是我的做法：
$id = 5;
$baz = 'shazam';
$select = $db->select()->from('bar','foo')
->where('id = ?', $id)
->where('baz = ?', $baz); // Zend_Db_Select will properly quote stuff for you
print_r($select->__toString()); // prints SELECT `bar`.`foo` FROM `bar` WHERE (id = 5) AND (baz = 'shazam')
回答by mustbebuilt
I have struggled with this one in the past. So to get round it I wrote a little function to build the SQL for me based on the SQL, flags and variables.
过去我一直在努力解决这个问题。所以为了解决这个问题，我写了一个小函数来根据 SQL、标志和变量为我构建 SQL。
//////////// Test Data //////////////
$_GET['filmID'] = 232;
$_GET['filmName'] = "Titanic";
$_GET['filmPrice'] = 10.99;
//////////// Helper Function //////////////
function debug_bind_param(){
$numargs = func_num_args();
$numVars = $numargs - 2;
$arg2 = func_get_arg(1);
$flagsAr = str_split($arg2);
$showAr = array();
for($i=0;$i<$numargs;$i++){
switch($flagsAr[$i]){
case 's' : $showAr[] = "'".func_get_arg($i+2)."'";
break;
case 'i' : $showAr[] = func_get_arg($i+2);
break;
case 'd' : $showAr[] = func_get_arg($i+2);
break;
case 'b' : $showAr[] = "'".func_get_arg($i+2)."'";
break;
}
}
$query = func_get_arg(0);
$querysAr = str_split($query);
$lengthQuery = count($querysAr);
$j = 0;
$display = "";
for($i=0;$i<$lengthQuery;$i++){
if($querysAr[$i] === '?'){
$display .= $showAr[$j];
$j++;
}else{
$display .= $querysAr[$i];
}
}
if($j != $numVars){
$display = "Mismatch on Variables to Placeholders (?)";
}
return $display;
}
//////////// Test and echo return //////////////
echo debug_bind_param("SELECT filmName FROM movies WHERE filmID = ? AND filmName = ? AND price = ?", "isd", $_GET['filmID'], $_GET['filmName'], $_GET['filmPrice']);
I have also build a little online tool to help.
我还构建了一个小的在线工具来提供帮助。
回答by myesain
I recently updated this project to include composer integration, unit testing and to better handle accepting arguments by reference (this requires updating to php 5.6).
我最近更新了这个项目以包含 Composer 集成、单元测试并更好地处理通过引用接受参数（这需要更新到 php 5.6）。
In response to a request I received on a project I created to address this same issue using PDO, I created an extension to mysqlion github that seems like it addresses your issue:
为了响应我在创建的一个项目上收到的请求，我使用 来解决同样的问题PDO，我mysqli在 github 上创建了一个扩展，它似乎解决了您的问题：
https://github.com/noahheck/E_mysqli
https://github.com/noahheck/E_mysqli
This is a set of classes that extend the native mysqliand mysqli_stmtclasses to allow you to view an example of the query to be executed on the db server by interpolating the bound parameters into the prepared query then giving you access to resultant query string as a new property on the stmtobject:
这是一组扩展本机mysqli和mysqli_stmt类的类，允许您通过将绑定参数插入到准备好的查询中来查看要在数据库服务器上执行的查询的示例，然后让您访问结果查询字符串作为新属性在stmt对象上：
$mysqli = new E_mysqli($dbHost, $dbUser, $dbPass, $dbName);
$query = "UPDATE registration SET name = ?, email = ? WHERE entryId = ?";
$stmt = $mysqli->prepare($query);
$stmt->bindParam("ssi", $_POST['name'], $_POST['email'], $_POST['entryId']);
$stmt->execute();
echo $stmt->fullQuery;
Will result in:
会导致：
UPDATE registration SET name = 'Sue O\'reilly', email = '[email protected]' WHERE entryId = 5569
Note that the values in the fullQuery are escaped appropriately taking into account the character set on the db server, which should make this functionality suitable for e.g. log files, backups, etc.
请注意，考虑到 db 服务器上的字符集，fullQuery 中的值被适当地转义，这应该使此功能适用于例如日志文件、备份等。
There are a few caveats to using this, outlined in the ReadMe on the github project, but, especially for development, learning and testing, this should provide some helpful functionality.
使用它有一些注意事项，在 github 项目的自述文件中概述，但是，特别是对于开发、学习和测试，这应该提供一些有用的功能。
As I've outlined in the github project, I don't have any practical experience using the mysqliextension, and this project was created at the request of users of it's sister project, so any feedback that can be provided from devs using this in production would be greatly appreciated.
正如我在 github 项目中概述的那样，我没有使用该mysqli扩展的任何实践经验，并且该项目是应其姊妹项目用户的要求创建的，因此在生产中使用此扩展的开发人员可以提供任何反馈将不胜感激。
Disclaimer- As I said, I made this extension.
免责声明- 正如我所说，我做了这个扩展。
回答by Langerz
Just set it to die and output the last executed query. The Error handling should give you meaningful information which you can use to fix up your query.
只需将其设置为 die 并输出最后执行的查询。错误处理应该为您提供有意义的信息，您可以使用这些信息来修复您的查询。
回答by alchemist
You can turn on log queries on mysql server. Just execute command:
您可以在 mysql 服务器上打开日志查询。只需执行命令：
sql> SHOW VARIABLES LIKE "general_log%";
sql> SET GLOBAL general_log = 'ON';
And watch queries in the log file. After testing turn log off:
并观察日志文件中的查询。测试后关闭注销：
sql> SET GLOBAL general_log = 'OFF';
