PHP Mysql Prepared statement different result [duplicate]

89
November 18, 2019, at 9:00 PM

This question already has an answer here:

  • I have an array of integers, how do I use each one in a mysql query (in php)? 5 answers
  • mySQL bind_param with IN(?) 5 answers

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?

Answer 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();
Answer 2
$stmt = $mysqli->prepare("SELECT id, nazev, poradi FROM system WHERE id IN(?) ORDER BY poradi");
$stmt->bind_param("s", $idIs);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows === 0) exit('No rows');
while($row = $result->fetch_assoc()) {
  $ids[] = $row['id'];
  $names[] = $row['name'];
  $ages[] = $row['age'];
}
var_export($ages);
$stmt->close();
Rent Charter Buses Company
READ ALSO
perl - insert value if column word is missing

perl - insert value if column word is missing

in perl, i want to insert missing line if

100
Routine import data from Excel to MySQL Database

Routine import data from Excel to MySQL Database

I know that it's possible to do a manual import of data from an Excel file to a MySQL database

94
Slow SELECT optimization with JOINs

Slow SELECT optimization with JOINs

My database consists of the following tables: articles, prices, suppliers, manufacturers and others

130
select max from multiple columns mysql with corresponding column value

select max from multiple columns mysql with corresponding column value

I have a query result in MySQL with the following structure

117