Convert PDO recordset to JSON in PHP

Question

Im using PHP and I need a way to convert an entire recordset to a JSON string.

While searching Stack Overflow I found this solution that works:

function recordSetToJson($mysql_result) {
  $rs = array();
  while($rs[] = mysql_fetch_assoc($mysql_result)) {
    // you don´t really need to do anything here.
  }
  return json_encode($rs);
}

The problem with this code is that I found that the function mysql_fetch_assoc() is deprecated in PHP 5.5.0. Another thing is that im using PDO to connect to my database.

Given the circunstances above, what would be the best solution to convert a PDO recordset to JSON? I want it to work at later versions of PHP too.

Answer 1

You should use something like that

somewhere previously

$stmt = $pdo->prepare("SELECT * FROM fruit WHERE name = ?");
$stmt->execute(array("Apple"));
....


function recordSetToJson($stmt) {
  $json_result = array();
  while($tmp = $stmt->fetch() ) {
       $json_result[] = $tmp;
  }
  return json_encode($json_result);
}

But final solution will be totally depends form too many factors.

Answer 2

The solution is simple. Considering that the variable $stmt is your PDO recordset, you can convert it to JSON like this:

json_encode($stmt->fetchAll(PDO::FETCH_ASSOC));

For more info about the functions used in this piece of code:

http://www.php.net/manual/en/function.json-encode.php

http://www.php.net/manual/en/pdostatement.fetchall.php