Export multiple MySQL results to CSV-CodePudding

I have the following PHP script below.

It checks the person table for rows with the reference PK001 & PK002.

When I run my script below, the output is the following:

ID,Reference,Email
1,PK001,[email protected]

I would prefer the output to be:

ID,Reference,Email
1,PK001,[email protected]
2,PK002,[email protected]

PHP

$data = array("PK001","PK002");
$count = count($data);

foreach ($data as $value) {

  $sql = "SELECT * FROM person WHERE reference = '$value'";
  $result = $con->query($sql);

  $csvheaders = ["ID","Reference","Email"];

  $fp = fopen('php://output', 'w');
  if ($fp && $result) {
      header('Content-Type: text/csv');
      header('Content-Disposition: attachment; filename="export.csv"');
      header('Pragma: no-cache');
      header('Expires: 0');
      fputcsv($fp, $csvheaders);
      while ($row = $result->fetch_array(MYSQLI_NUM)) {
          fputcsv($fp, array_values($row));
      }
      die();
  }

}

die();

Can someone please explain how I resolve this?

CodePudding user response：

You can use IN() to compare to multiple values. See https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_in for documentation on IN().

Also you should use query parameters. Please do not interpolate values directly into SQL expressions. That's a good way to produce insecure, buggy code.

You need one separate parameter placeholder for each value in the array. You can't use a single parameter for a list of values.

Here's how I would write it:

$data = ["PK001","PK002"];

$placeholders = implode(",", array_fill(1, count($data), "?"));

$sql = "SELECT ID, reference, email FROM person WHERE reference IN ({$placeholders})";

$stmt = $con->prepare($sql);
$stmt->bind_param(str_repeat("s", count($data)), ...$data);
$stmt->execute();
$result = $stmt->get_result();

Then proceed to loop over $result as you have done.

FYI, PDO is even easier, because you don't need to do any binding. Just pass the array to execute():

$stmt = $con->prepare($sql);
$stmt->execute($data);

I would prefer to use PDO instead of mysqli in any PHP project.