PHP

[ Solved -8 Answers ] PHP – JSON encode MySQL results

JSON encode MySQL results How do we use the json_encode() function with MySQL query results Do we need to iterate through

  • How do we use the json_encode() function with MySQL query results?
  • Do we need to iterate through the rows or can we just apply it to the entire results object?

Sql Code
$sth = mysqli_query("SELECT ...");
$rows = array();
while($r = mysqli_fetch_assoc($sth)) {
    $rows[] = $r;
}
print json_encode($rows);

The function json_encode needs PHP >= 5.2 and the php-json package – as mentioned here

NOTE: mysql is deprecated as of PHP 5.5.0, use mysqli extension instead http://php.net/manual/en/migration55.deprecated.php.

  • Try this, this will create your object properly
Sql Code
$result = mysql_query("SELECT ...");
 $rows = array();
   while($r = mysql_fetch_assoc($result)) {
     $rows['object_name'][] = $r;
   }

 print json_encode($rows);

  • Try this code:
Sql Code
$sqldata = mysql_query("SELECT * FROM `$table`");

$rows = array();
while($r = mysql_fetch_assoc($sqldata)) {
  $rows[] = $r;
}

echo json_encode($rows);

  • Before you name the root-element in the array to something, we have not been able to access anything in the final json before that.
Sql Code
$sth = mysql_query("SELECT ...");
$rows = array();
while($r = mysql_fetch_assoc($sth)) {
    $rows['root_name'] = $r;
}
print json_encode($rows);

  • The code below works fine here!
Php Code
<?php

  $con=mysqli_connect("localhost",$username,$password,databaseName);

  // Check connection
  if (mysqli_connect_errno())
  {
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
  $query = "the query here";

  $result = mysqli_query($con,$query);

  $rows = array();
  while($r = mysqli_fetch_array($result)) {
    $rows[] = $r;
  }
  echo json_encode($rows);
  mysqli_close($con);
?>

Sql Code
if ($result->num_rows > 0) {
            # code...
            $arr = [];
            $inc = 0;
            while ($row = $result->fetch_assoc()) {
                # code...
                $jsonArrayObject = (array('lat' => $row["lat"], 'lon' => $row["lon"], 'addr' => $row["address"]));
                $arr[$inc] = $jsonArrayObject;
                $inc++;
            }
            $json_array = json_encode($arr);
            echo $json_array;
        }
        else{
            echo "0 results";
        }

  • Here the simple fix to stop it putting speech marks around numeric values…
Sql Code
while($r = mysql_fetch_assoc($rs)){
    while($elm=each($r))
    {
        if(is_numeric($r[$elm["key"]])){
                    $r[$elm["key"]]=intval($r[$elm["key"]]);
        }
    }
    $rows[] = $r;
} 

  • we could simplify Paolo Bergantino answer like this
Sql Code
$sth = mysql_query("SELECT ...");
print json_encode(mysql_fetch_assoc($sth));

About the author

Wikitechy Editor

Wikitechy Founder, Author, International Speaker, and Job Consultant. My role as the CEO of Wikitechy, I help businesses build their next generation digital platforms and help with their product innovation and growth strategy. I'm a frequent speaker at tech conferences and events.

Add Comment

Click here to post a comment