MySQLi Prepared Statements: dealing with problem (Dynamic query)

PHP
1

Hello, i m converting to MySQLi Prepared Statements and i m dealing with problem now.

I have query that i want to add extra and if $query has some value. As i put them in bind_param() and $query is not set i get error:

Warning: mysqli_stmt::bind_param(): Number of variables doesn’t match number of parameters in prepared statement in

How can i solve this?

if($query){$worker=" AND person=?";}else{$worker=null;}

$stmt = $conn->prepare("SELECT something FROM table WHERE year = ? AND month = ? AND object = ? ".$worker." ORDER BY something LIMIT 1");
$stmt->bind_param("ssss", $y, $m, $object, $query);
$stmt->execute();
$query_st = $stmt->get_result();
$row_st = $query_st->fetch_assoc();
$stmt->close();

Thanks in advance!

2

Enclose your bind_param() in an if clause, and only provide the additional parameter if you need to?

if ($worker == null) { 
  //do the bind-param without the fourth parameter
}
else {
  // do the bind-param with the extra parameter
}
2 Likes
3

LOL so simple :rofl: Thanks man! :slightly_smiling_face:

4

If you use PDO, you can pass the parameters as an array, so in this specific case that might be a little simpler. You’d create the array of parameters beforehand, then you could add the extra parameter if you needed to. Something like:

$param = array();
$param[':year'] = $y;
$param[':month'] = $m;
$param[':object'] = $object;
if ($query) { 
    $worker = " AND person = :person "; 
    $param[':person'] = $query;
} else {
$worker = "";
}
$stmt = $conn->prepare("SELECT something FROM table WHERE year = :year AND month = :month AND object = :object ".$worker." ORDER BY something LIMIT 1");
$result = $stmt->execute($param);

or something like that.

Probably would make more difference to simplicity if there were many parameters that might or might not be present, rather than just the one.

2 Likes
5

Thanks for PDO example but i choosed to stay with MySQLi

1 Like
6

I found this post: www.sitepoint.com that with research did lead me to stackoverflow.com and found out that unpacking operator (...) is amazing for dynamic querys!

I hope this will help someone.

7

It’s just another reason why mysqli sucks.

8

Can you explain what you mean?

9

This whole variaric function stuff ist just to convert an array to function parameters and vice versa. If you have a dynamic list of unknown length that already is an array, then you can just give that to the function als one parameter - as an array. As to the unknown length, the function can’t use fixed keys to access the parameters, and has to re-convert the parameters to an array to loop over them. There may be (didn’t find any) edge-cases when variaric functons are useful, but it’s mostly broken design. Plain function parameters can only be set manually, so they are pretty useless for anything dynamic and variaric functions are just a workaround. Guess what bind_param has to do internally?

bind_param(string $types, ...$params){ foreach($params as $param){ do shit } }

So $params is just an array again like it has been from the start. All bind_param would have to do is just fix their signature

bind_param(string $types, array $params)

Perfectly clear, no conversion, no workaround. That’s what PDO does.

Just another example:

sprintf ( string $format [, mixed $... ] ) : string
vsprintf ( string $format , array $args ) : string

sprintf is pretty useless for anything dynamic, if i already have an array and would not set function parameters by hand. So there’s vsprintf.

1 Like
10

I see your point! I was already into MySQLi when find out that there are limited actions with bind_param() and yeah bind_param(string $types, array $params) would be cool!

11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.