PHP / MySQL - prepared statements debugging

Question

I have the following code:

$sql = "SELECT...";
$stmt = $db->prepare($sql);
$stmt->bind_param("<types>", $params...);
$stmt->execute();
$resultSet = $stmt->get_result();

My question is how exactly I can see the query that is being actually executed?

If I execute this query directly (without the bindings) via MySQL client, it works great. But in this piece of code something goes wrong and I am trying to understand what.

Types and params in $stmt->bind_param() seem to be correct but it returns an empty result set.

Somebody?

Also enable errors so you can see exactly what is wrong ini_set('display_errors', 1); — mandza, May 07 '15 at 07:45
var_dump on which object? the query is "SELECT education, treat, investigation FROM wizard WHERE question1 = ? AND question2 = ? AND question3 = ? AND question4 = ? AND question5 = ?" questions 1-4 are integers, question 5 is a string — darxysaq, May 07 '15 at 07:51
There is no way to retrieve "final" query. The only thing you can do - dump query and binded parameters. — Ilia Kondrashov, May 07 '15 at 08:04

score 4 · Answer 1 · answered May 07 '15 at 07:55

4

Error handling usually helps when debugging code:

if (false === ($stmt = $db->prepare($sql))) {
    echo 'error preparing statement: ' . $db->error;
} elseif (!$stmt->bind_param("<types>", $params...)) {
    echo 'error binding params: ' . $stmt->error;
} elseif (!$stmt->execute()) {
    echo 'error executing statement: ' . $stmt->error;
}

answered May 07 '15 at 07:55

RonaldPK

760
4
18

3

The query is being executed without problems but returns an empty result set. – darxysaq May 07 '15 at 08:00

score 2 · Answer 2 · answered May 07 '15 at 11:22

2

There is no final query. The server receives your SQL-statement and your params and executes that optimized. The query itself will never get stringified together with the params.

answered May 07 '15 at 11:22

Vortex852456

721
6
23

score 1 · Answer 3 · answered May 07 '15 at 08:02

You can try below code :

$sql = "SELECT education, treat, investigation FROM wizard WHERE question1 = :question1 AND question2 = :question2 AND question3 = :question3 AND question4 = :question4 AND question5 = :question5";
$stmt = $db->prepare($sql);
$stmt->bindParam(':question1', $question1);
$stmt->bindParam(':question2', $question2);
$stmt->bindParam(':question3', $question3);
$stmt->bindParam(':question4', $question4);
$stmt->bindParam(':question5', $question5);
$stmt->execute();
$resultSet = $stmt->get_result();

score 0 · Answer 4 · answered Dec 11 '16 at 20:10

A possible solution is to implement another custom function to show any errors. Note you will need to modify for your specific situation:

<?php
    function showerror($sql, $types, $params){
        echo "There is an error:\r\n";
        print_r(error_get_last()); //return the error, as well as file and line number
        echo "SQL: ".$sql."\r\nTypes: ".$types;
        var_dump($params);
    }

    $sql = "SELECT * FROM users WHERE firstName=? AND lastName=?";
    $types = "sss";
    $params = array("Bob", "Smith");
    if(strlen($types)!=count($params)) showerror($sql, $types, $params); //make sure number of types is equal to the number of params
    if(!$stmt = $db->prepare($sql)) showerror($sql, $types, $params);
    if(!$stmt->bind_param($types, $params...)) showerror($sql, $types, $params);
    if(!$stmt->execute()) showerror($sql, $types, $params);
    $resultSet = $stmt->get_result();
?>

score 0 · Answer 5 · answered Jul 14 '20 at 06:08

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories FROM fruit WHERE calories < 
:calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindValue(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();

$sth->debugDumpParams();

?>

The above example will output:

SQL: [96] SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour = :colour

Params:  2
Key: Name: [9] :calories
paramno=-1
name=[9] ":calories"
is_param=1
param_type=1
Key: Name: [7] :colour
paramno=-1
name=[7] ":colour"
is_param=1
param_type=2

https://www.php.net/manual/en/pdostatement.debugdumpparams.php

PHP / MySQL - prepared statements debugging

5 Answers5