Prepared statements when working with set operations

Question

I've got a simple query that is not so easy to execute in PHP script:

SELECT `title` from `MY_TABLE` WHERE id in (30,32,33,44)

Usually I execute sql queries with prepared statements. I place a bunch of ? and than bind parameters. This time the numbers in parenthesis are an array of data I get from the user.

I tried this, but it does not work:

$ids = [30,32,33,44];
$stmt = $mysqli->prepare("

SELECT `title` from `MY_TABLE` WHERE id in (?)

");
// $stmt->bind_param();
$stmt->bind_param("i",$ids);
$stmt->execute();
$stmt->bind_result($title);
$stmt->store_result();
//fetch

How can I execute a set operation with prepared statements?

UPDATE:

After following your advice I came up with this

$ids = [30,32,33,44];
$questionMarks  = rtrim(str_repeat('?,',count($ids)),", ");
$parameters = str_repeat('i',count($ids));
echo $questionMarks."<br>";
echo $parameters."<br>";
$stmt = $mysqli->prepare("

SELECT `title` from `MY_TABLE` WHERE id in (".$questionMarks.")

");

$scene_names = [];
$stmt->bind_param($parameters, $ids); //error here
$stmt->execute();
$stmt->bind_result($title);
$stmt->store_result();

I am still getting an error. This time it says:

Number of elements in type definition string doesn't match number of bind variables

I am not sure why it thinks that the number of elements (what is element in this case?) is wrong.

UPDATE 2:

Instead of:

$stmt->bind_param($parameters, $ids); //error here

I used:

$stmt->bind_param($parameters, ...$ids); //error gone

Taraam. Works fine.

You need the same amount of `?` as elements in the array – Liam G Jun 14 '18 at 17:04 — Liam G, Jun 14 '18 at 17:04

Liam G · Answer 1 · 2018-06-14T20:44:19.547

2

Something like:

$ids = [30,32,33,44];
$types = array();
foreach($ids as $i){
    array_push($types,'i');
}
$params = array_merge($ids,$types);
$sqlIN  = str_repeat('?,',count($ids));
$sqlIN  = rtrim($sqlIN, ',');

//Value of $sqlIN now looks like ?,?,?,?

$sql = "SELECT title from MY_TABLE WHERE id IN ($sqlIN)";
$stmt = $mysqli->prepare($sql);
call_user_func_array(array($stmt, 'bind_param'), $params);
$stmt->execute();
$stmt->bind_result($id);
$stmt->store_result();

edited Jun 14 '18 at 20:44

answered Jun 14 '18 at 17:09

Liam G

587
4
16

Thanks. We are almost there. There is unfortunately one error left. See updated question. – sanjihan Jun 14 '18 at 18:10
Sorry I'm used to PDO where you can just pass an array of elements, I'll check the mysqli documentation and get back to you – Liam G Jun 14 '18 at 18:23
Used [this](https://stackoverflow.com/questions/1913899/mysqli-binding-params-using-call-user-func-array) to guide me... I would highly recommend looking at using PDO, very similar but much better functionality. There is probably a much cleaner solution out there for mysqli – Liam G Jun 14 '18 at 18:38
Thanks. didn't work, but that post actually contains the correct answer. It involves using splat operator – sanjihan Jun 14 '18 at 21:34
Glad to know you got it sorted! I was in new waters with mysqli – Liam G Jun 14 '18 at 21:37

Prepared statements when working with set operations

1 Answers1