PHP Mysql Prepared statement different result

Question

I have this mysql query in php:

$sql2 = "SELECT id, nazev, poradi FROM system WHERE id IN($idIs) ORDER BY poradi";
$result2 = mysqli_query($conn, $sql2);

The variable $idIs is a string '2,3' (two ids of system). When I try to fill array $nazevSystemu, there are two values (beacause of the two ids from $idIs)

$i = 0;
$nazevSystemu = [];
while($row2 = mysqli_fetch_assoc($result2)) {
  $nazevSystemu[$i] = $row2['nazev'];
  echo $row2['nazev'];
  $i++;
}

Result of echo $row2['nazev'];: Value1Value2

I want to make it safe, avert SQl inj., so I use prepared statement like this (instead of the first two rows of code on this page):

$stmt2 = $conn->prepare("SELECT id, nazev, poradi FROM system WHERE id IN(?) ORDER BY poradi");
$stmt2->bind_param("s", $idIs);
$stmt2->execute();
$result2 = $stmt2->get_result();

But now I get only this as result of echo $row2['nazev']; - just one value: Value1

What did I do wrong in prepared statement?

Check the second part of the accepted answer of the duplicate. This gives a good example of how to dynamically bind params in an `WHERE IN ()` clause — Cid, Nov 18 '19 at 10:43

thephper · Answer 1 · 2019-11-18T11:47:59.130

-1

You have to provide all id's as individual parameters. So instead of IN(?) you have to write IN(?,?,?) and parse each parameter individual.

Code example:

$ids = explode(',', $idIs);
$stmt2 = $conn->prepare("SELECT id, nazev, poradi FROM system WHERE id IN(".trim(str_repeat('?,', count($ids)), ',').") ORDER BY poradi");
foreach ($ids as $id) {
   $stmt2->bind_param("i", $id);
}
$stmt2->execute();
$result2 = $stmt2->get_result();

edited Nov 18 '19 at 11:47

answered Nov 18 '19 at 10:40

thephper

2,342
22
21

1

I cant do that. I dont know how many ids will be there... – SheldonCopper Nov 18 '19 at 10:41
You can split the ids automatically. So explode the $idIs variable into an array (using explode() function) and then auto-fill the questionmarks. Something like `('.trim(str_repeat('?,', count($ids)), ',').')`. – thephper Nov 18 '19 at 10:43
I have added a code example to the original answer now. – thephper Nov 18 '19 at 10:46
already in comma seprated please check this line in his questions: The variable $idIs contains string '2,3' (two ids of system). When I try to fill array $nazevSystemu, there are two values (beacause of the two ids from $idIs) – BhAvik Gajjar Nov 18 '19 at 10:51
Can u pls check your answer, maybe there is some syntax mistake with brackets on line 2? – SheldonCopper Nov 18 '19 at 10:59
There was an bracket-syntax error in the code example. This is fixed now. Since the $idIs is a comma separeted string we must explode it as we need it as an array. – thephper Nov 18 '19 at 11:11
Unfortunately it doesnt work "Fatal error: Uncaught Error: Call to a member function bind_param() on bool in ..." – SheldonCopper Nov 18 '19 at 11:16
I have changed the code example once again. When the prepare() method returns false there is probably a mistake in the query sentence. This was also the case here. – thephper Nov 18 '19 at 11:21
1

It still doesnt work, same error. Leave it, they closed this question anyway. Thanks 4 trying to help...:) – SheldonCopper Nov 18 '19 at 11:24
So what you want to do is debug the query for syntax errors. I did remove a comma now. Please try my new edit. – thephper Nov 18 '19 at 11:48
The reason we provide duplicate page closures is not to silence the asker. It is to provide you with solutions. – mickmackusa Nov 18 '19 at 13:00

PHP Mysql Prepared statement different result

1 Answers1