How to echo a MySQLi prepared statement?
PhpMysqliPhp Problem Overview
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?
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();
}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">http://www.php.net/mysqli</a>;) but without any luck.
EDIT
Well, if it's not possible from within MySQLi, maybe I'll stick with something like this:
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
// outputs: SELECT foo FROM bar WHERE id=1 AND baz=something
Php Solutions
Solution 1 - Php
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:
$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')
Solution 2 - Php
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.
//////////// 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.
Solution 3 - Php
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).
In response to a request I received on a project I created to address this same issue using PDO, I created an extension to mysqli on github that seems like it addresses your issue:
https://github.com/noahheck/E_mysqli
This is a set of classes that extend the native mysqli and mysqli_stmt classes 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 stmt object:
$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 = 'sue.o@example.com' 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.
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.
As I've outlined in the github project, I don't have any practical experience using the mysqli extension, 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.
Disclaimer - As I said, I made this extension.
Solution 4 - Php
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.
Solution 5 - Php
You can turn on log queries on mysql server. Just execute command:
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';
Solution 6 - Php
I was able to use var_dump() to at least get a little more info on the mysqli_stmt:
$postmeta_sql = "INSERT INTO $db_new.wp_postmeta (post_id, meta_key, meta_value) VALUES (?, ?, ?)";
$stmt = $new_conn->prepare($postmeta_sql);
$stmt->bind_param("sss", $post_id, $meta_key, $meta_value);
echo var_dump($stmt);
$stmt->execute();
$stmt->close();