PHP Mysql Prepared statement different result [duplicate]

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
– Cid, Commented Nov 18, 2019 at 10:43

thephper · Accepted Answer · 2019-11-18 11:47:59Z

-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, 2019 at 11:47

answered Nov 18, 2019 at 10:40

thephper

2,70028 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

SheldonCopper Over a year ago

I cant do that. I dont know how many ids will be there...

thephper Over a year ago

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 Over a year ago

I have added a code example to the original answer now.

BhAvik Gajjar Over a year ago

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)

SheldonCopper Over a year ago

It still doesnt work, same error. Leave it, they closed this question anyway. Thanks 4 trying to help...:)

|

Collectives™ on Stack Overflow

PHP Mysql Prepared statement different result [duplicate]

1 Answer 1

11 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Linked

Related