I'm working on learning to use prepared statements with mysqli in PHP and usually, if I'm having a problem with a query I just echo it to the screen to see what it looks like as a first step.
How can I do that with a prepared statement?
I'd like to see the SQL statement after the variables are substituted.
A prepared statement is a feature used to execute the same (or similar) SQL statements repeatedly with high efficiency. Prepared statements basically work like this: Prepare: An SQL statement template is created and sent to the database. Certain values are left unspecified, called parameters (labeled "?").
public interface PreparedStatement extends Statement. An object that represents a precompiled SQL statement. A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.
Prepared statements can help increase security by separating SQL logic from the data being supplied. This separation of logic and data can help prevent a very common type of vulnerability called an SQL injection attack.
Using prepared statements:
There is no "building" of an SQL query on the PHP side, so, there is no way to actually get that query.
Which means that if you want to see an SQL query, you have to use, well, SQL queries, and not prepared statements.
For prepared statements that are executed with the mysql_stmt_prepare() and mysql_stmt_execute() C API functions, the server writes Prepare and Execute lines to the general query log so that you can tell when statements are prepared and executed.
[...] the server writes the following lines to the general query log:
Prepare [1] SELECT ?
Execute [1] SELECT 3
So for debugging purposes active the general log and keep an eye on that file.
edit: oh, the question has a [mysqli] tag... completely overlooked that.
If the statement isn't executed at all have you (double/tripple) checked that no error occurred along the way?
echo "<pre>Debug: start</pre>\n"; $mysqli = new mysqli('localhost', 'localonly', 'localonly', 'test'); if ($mysqli->connect_error) { die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error); } $result = $mysqli->query('CREATE TEMPORARY TABLE foo (id int auto_increment, x int, primary key(id))'); if ( false=== $result) { die('error : '. $mysqli->error); } $stmt = $mysqli->prepare('INSERT INTO foo (x) VALUES (?)'); if ( false===$stmt ) { die ('prepare() failed: ' . $mysqli->error); } $result = $stmt->bind_param('i', $x); if ( false===$result ) { die('bind_param() failed'); } $x = 1; $result = $stmt->execute(); if ( false===$result ) { die('execute() failed: '.$stmt->error); } echo "<pre>Debug: end</pre>\n";
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With