Mysqli statement binding using arrays

I am upgrading some PHP code from using mysql_ to mysqli_ but I’m having a real problem when it comes to “building” sql statements. The below code shows how I “build” a sql statement based on what is completed on the form.

if (isset($_POST[‘submit’])) {

$query = "SELECT profile FROM profiles WHERE state != 9 AND blocked = 0";
$conditions = [];
$lookingfor = [];

// Validate minimum age
if ((isset($_POST[‘minimumage’])) and (preg_match(‘/^\d{2}$/’, $_POST[‘minimumage’]))) {
$conditions[‘minage >= ?’] = [‘i’, $_POST[‘minimumage’]];
$lookingfor = "Minimum age " . $_POST[‘minimumage’];
}

// Validate maximum age
if ((isset($_POST[‘maximumage’])) and (preg_match(‘/^\d{2}$/’, $_POST[‘maximumage’]))) {
$conditions[‘maxage <= ?’] = [‘i’, $_POST[‘maximumage’]];
$lookingfor = "Maximum age " . $_POST[‘maximumage’];
}

if (!empty($lookingfor)) {
    foreach ($lookingfor as $var) {
        echo "<li>" . $var . "</li>";
    }
}
$where_clauses = $types = $values = [];
if (!empty($conditions)) {
    foreach ($conditions as $clause => $condition) {
        $where_clauses[] = $clause;
        list($types[], $values[]) = $condition;
    }
  $query .= " AND ";
    $query .= join(" AND ", $where_clauses);
}
$stmt = mysqli_prepare($sql_connect, $query);
if (!empty($conditions)) {
    mysqli_stmt_bind_param($stmt, x, x); // <<< NEED HELP HERE
}
mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);
mysqli_stmt_fetch($stmt);
if (mysqli_stmt_num_rows($stmt) > 0) {
    echo "Found " . mysqli_stmt_num_rows($stmt) . " profiles which match this criteria";
}

}

As you can see $types holds the bind type, $value holds the bind value. However I can’t pass these directly into mysqli_stmt_bind_param as they are arrays. I therefore tried using $types = implode(‘’, $types); to convert this into a string before binding.

Now I am getting this error: Warning: mysqli_stmt_bind_param(): Number of elements in type definition string doesn’t match number of bind variables in /home/pof/public_html/pbot/messaging.php on line 114. While I understand what this means I don’t understand what’s causing it, or how to fix it. Several forum posts have suggested call_user_func_array but I can’t get this to work :frowning:

you could use either call mysqli_stmt_bind_param() via call_user_func_array() or use PDO (which binds parameters separately) instead of mysqli.

Like I said, several forum posts have suggested call_user_func_array but I can’t get this to work

This is one of those cases where you outsmarted yourself and didn’t follow the KISS principle. If you are doing it as an exercise to work on binding, that’s fine, and just follow the advice of @Dormilich

But you only really need to bind parameters to prevent SQL injection. Considering the simplicity of the query in question, and you’re doing the required validation using regex, there’s no need to handle it that way in this case.

 if (isset($_POST['submit'])) {
     $query = "SELECT profile FROM profiles WHERE state != 9 AND blocked = 0";

	// Validate minimum age
     if ((isset($_POST['minimumage'])) and (preg_match('/^\d{2}$/', $_POST['minimumage']))) {
		$query .= " AND minage >= " . $_POST['minimumage'];
		echo "<li>Minimum age " . $_POST['minimumage'] . "</li>";
     }

	// Validate maximum age
     if ((isset($_POST['maximumage'])) and (preg_match('/^\d{2}$/', $_POST['maximumage']))) {
         $query .= " AND maxage <= " . $_POST['maximumage']];
         echo "<li>Maximum age " . $_POST['maximumage'] . "</li>";
     }

     $stmt = mysqli_prepare($sql_connect, $query);
     mysqli_stmt_execute($stmt);
     mysqli_stmt_store_result($stmt);
     mysqli_stmt_fetch($stmt);

     if (mysqli_stmt_num_rows($stmt) > 0) {
         echo "Found " . mysqli_stmt_num_rows($stmt) . " profiles which match this criteria";
     }
 }

well, since I don’t know how you tried it, I can’t tell what you did wrong. but for mysqli this is the only way to get it to work.

something like this (didn’t test)

if (!empty($conditions)) {
    $typesString = join("", $types);
    $arguments = [$stmt, $typesString];
    foreach($values as $value){
        $arguments[] = $value;
    }
    call_user_func_array("mysqli_stmt_bind_param", $arguments);
}
1 Like

In the example posted I would agree, however there are about 20 other $_POST variables which I have not posted in (as that would be pointless).

@megazoid I’m going to give this a try and let you know how I get on.

Thanks

@megazoid sorry that didn’t work either :frowning:

Warning: Parameter 3 to mysqli_stmt_bind_param() expected to be a reference, value given in /home/pof/public_html/pbot/messaging.php on line 113

Try to change that line:

$arguments[] = $value;

to:

$arguments[] = &$value;
1 Like

@megazoid F****** BRILLIANT! If I could buy you a beer I would :thumbsup:

What does & do?

It passes reference to variable instead of value.
That means if you change original variable second one will be changed too

$a = 5;
$b = &$a;
$a = 9;
echo $b; // will output 9

Don’t know why but mysqli_stmt_bind_param() asks arguments to be passed as references instead of values

Okay I understand.

There is a slight defect in the bind values now, but I’m working on debugging that myself.

Thanks you for helping to get this working :smile:

it might be worth a try to use the underlying OOP variant:

call_user_func_array([$stmt, 'bind_param'], $params);

I don’t understand OOP, so no :smiley:

that doesn’t matter much in this case.

for comparison, the bind statement in both variants:

// procedural
mysqli_stmt_bind_param($stmt, 's', 'value');
// OOP
$stmt->bind_param('s', 'value');

good thing is in this case they’re interchangeable.

When testing this I set the minage is 18 and the maxage is 21 but the results were not that I expected. After a little investigation it appears it is searching sql using minage 21 and maxage 21.

$_POST is holding the correct values
$values is also holding the correct values

This is what $arguments is holding…

Array ( [0] => mysqli_stmt Object ( [affected_rows] => 0 [insert_id] => 0 [num_rows] => 0 [param_count] => 2 [field_count] => 1 [errno] => 0 [error] => [error_list] => Array ( ) [sqlstate] => 00000 [id] => 1 ) [1] => ii [2] => 18 ) 

Array ( [0] => mysqli_stmt Object ( [affected_rows] => 0 [insert_id] => 0 [num_rows] => 0 [param_count] => 2 [field_count] => 1 [errno] => 0 [error] => [error_list] => Array ( ) [sqlstate] => 00000 [id] => 1 ) [1] => ii [2] => 21 [3] => 21 )

Not sure where this is going wrong, this is beyond my knowledge of MySQLi and Arrays :frowning:

how do you build those arrays and why do you have two of them?

There is where I am so far…

<?php

if (isset($_POST['submit'])) {
	$query = "SELECT profile FROM profiles WHERE state != 9 AND blocked = 0";
	$conditions = [];
	$lookingfor = [];

	// Validate minimum age
	if ((isset($POST['minimumage'])) and (pregmatch('/^\d{2}$/', $_POST['minimumage']))) {
	$conditions['minage >= ?'] = ['i', $_POST['minimumage']];
	$lookingfor[] = "Minimum age " . $_POST['minimumage'];
	}

	// Validate maximum age
	if ((isset($POST['maximumage'])) and (pregmatch('/^\d{2}$/', $_POST['maximumage']))) {
	$conditions['maxage <= ?'] = ['i', $_POST['maximumage']];
	$lookingfor[] = "Maximum age " . $_POST['maximumage'];
	}

	// Display what we're looking for
	if (!empty($lookingfor)) {
		foreach ($lookingfor as $var) {
			echo "<li>" . $var . "</li>";
		}
	}

	// Build the sql query and prepare
	$where_clauses = $types = $values = [];
	if (!empty($conditions)) {
		foreach ($conditions as $clause => $condition) {
			$where_clauses[] = $clause;
			list($types[], $values[]) = $condition;
		}
		$query .= " AND ";
		$query .= join(" AND ", $where_clauses);
	}
	$stmt = mysqli_prepare($sql_connect, $query);

	// Bind the variables to the statement
	if (!empty($conditions)) {
		$typesString = join("", $types);
		$arguments = [$stmt, $typesString];
		foreach($values as $value){
			$arguments[] = &$value;
		}
		call_user_func_array("mysqli_stmt_bind_param", $arguments);
	}

	mysqli_stmt_execute($stmt);
	mysqli_stmt_store_result($stmt);
	mysqli_stmt_fetch($stmt);

	if (mysqli_stmt_num_rows($stmt) > 0) {
		echo "Found " . mysqli_stmt_num_rows($stmt) . " profiles which match this criteria";
	}
	
	print_r($arguments);
}

?>

Hypothetically speaking would coding in OOP make this any easier to do?

there are a lot of _ missing in your code.