To debug a mysqli prepare statement we could place the prepare statement within an if as follows:

if($stmt = $mysqli->prepare("SELECT filmID, filmName FROM movies WHERE filmID = ?")){
    $stmt->bind_param('i', $_GET['filmID']);
    $stmt->execute();
    $stmt->bind_result($filmName, $filmCert);
    $stmt->fetch();
    $stmt->close();
}else{
    echo $mysqli->error;
}

The above can help but it often you want to see the SQL that was created by the prepare statement. Unfortunately there is no easy way to do this. However, the function below might help. You can feed it with the three parameters used to build the prepare ie:

Syntax

debug_bind_param(sqlFromPrepare:string, flagsFromBindParams:string, listOfValuesAsUsedInBindParams)

Hopefully someone might find this useful:

//////////// 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']);

And if that doesn’t help then why not try this little tool.

Mysqli Prepare Statement Checker

Just add in the three components of the prepare – the SQL, the flags and the variables and it should build the SQL for you to debug in the likes of phpMyAdmin.

2 Thoughts to “Debugging mysqli Prepare Statements – Output the SQL Statement Helper Function”

  1. Hello,

    Small error on line:
    for($i=0;$i<$numargs;$i++){

    Should it not be < $numVars instead of numargs ? I get an out of bound error.

    Thank you for this great function!

    1. admin

      What where the parameters you tried that gave you the out of bounds?

Leave a Reply to admin Cancel reply