How to convert mysql data base table data in json using php

Question

How to convert MySQL data base table into JSON data using PHP. Is there any way to do this?

Below is the php code I am using:

<?php 
$host = "emriphone.db.6420177.hostedresource.com"; 
$user = "emriphone"; 
$pass = "Light12-"; 
$database = "emriphone"; 

$linkID = mysql_connect($host, $user, $pass) or die("Could not connect to host."); 
mysql_select_db($database, $linkID) or die("Could not find database."); 

$sth = mysql_query("SELECT * FROM ProviderAppointmentListings");
$rows = array();
while($r = mysql_fetch_assoc($sth)) {
   $rows[] = $r;
}
print json_encode($rows);
?>

Answer 1

Try like this:

$query = mysql_query("SELECT * FROM table");
$rows = array();
while($row = mysql_fetch_assoc($query)) {
    $rows[] = $row;
}
print json_encode($rows);

If you don't have json_encode add this before the code above:

if (!function_exists('json_encode'))
{
  function json_encode($a=false)
  {
    if (is_null($a)) return 'null';
    if ($a === false) return 'false';
    if ($a === true) return 'true';
    if (is_scalar($a))
    {
      if (is_float($a))
      {
        // Always use "." for floats.
        return floatval(str_replace(",", ".", strval($a)));
      }

      if (is_string($a))
      {
        static $jsonReplaces = array(array("\\", "/", "\n", "\t", "\r", "\b", "\f", '"'), array('\\\\', '\\/', '\\n', '\\t', '\\r', '\\b', '\\f', '\"'));
        return '"' . str_replace($jsonReplaces[0], $jsonReplaces[1], $a) . '"';
      }
      else
        return $a;
    }
    $isList = true;
    for ($i = 0, reset($a); $i < count($a); $i++, next($a))
    {
      if (key($a) !== $i)
      {
        $isList = false;
        break;
      }
    }
    $result = array();
    if ($isList)
    {
      foreach ($a as $v) $result[] = json_encode($v);
      return '[' . join(',', $result) . ']';
    }
    else
    {
      foreach ($a as $k => $v) $result[] = json_encode($k).':'.json_encode($v);
      return '{' . join(',', $result) . '}';
    }
  }
}

Answer 2

<?php
    $username = "user_name"; 
    $password = "password";   
    $host = "url";
    $database="database";

    $server = mysql_connect($host, $username, $password);
    $connection = mysql_select_db($database, $server);

    $myquery = "SELECT date,close FROM data2";
    echo "hi";
    $query = mysql_query($myquery);

    if ( ! $query ) {
        echo mysql_error();
        die;
    }    
    $data = array();    

    for ($x = 0; $x < mysql_num_rows($query); $x++) {
        $data[] = mysql_fetch_assoc($query);
    }

    echo json_encode($data);     

mysql_close($server);

?>

This code converts your mysql data in phpmyadmin to json. Works perfect

Answer 3

As long as you are using MySQL server 5.7 or later, you can produce JSON data by using just SQL and nothing more, that is, you need PHP just to pass the SQL and get the JSON result. For example:

SELECT JSON_OBJECT( 'key1', column1,
                    'key2', JSON_OBJECT('key3', column2)) as fοο;

There is more that JSON_OBJECT! Please check this page: https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html