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);
?>

It would be good if you show us the suggested json data format. In general @Secator is right. — Minras
– Minras, Commented Mar 20, 2012 at 11:17
Upgrade PHP bro! You're living in that Dark Age where PHP couldn't goto. — greut
– greut, Commented Mar 20, 2012 at 17:35

Mariusz Jamro · Accepted Answer · 2012-03-20 12:23:19Z

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) . '}';
    }
  }
}

Fatal error: Call to undefined function: json_encode() in this gives error using this code
Server version: 5.0.91-log this is given on my data base which i am using
Have you added the json_encode before you actually use it ?

shri · Accepted Answer · 2014-10-08 21:53:01Z

1

<?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

edited Oct 8, 2014 at 21:53

answered Oct 7, 2014 at 19:09

shri

112 bronze badges

2 Comments

Mike Brant Over a year ago

PHPMyAdmin is an application providing an admin UI to MySQL database backends, not a data hosting platform in and of itself. Besides, The OP made no mention of PHPMyAdmin.

Satyam Over a year ago

if we don't format the date into proper ISO format, how will programming languages like Java detect it during parsing on client apps?

neuromancer · Accepted Answer · 2017-10-28 14:18:27Z

1

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

edited Oct 28, 2017 at 14:18

answered Oct 26, 2017 at 20:51

neuromancer

212 bronze badges

Comments

Christofer Eliasson · Accepted Answer · 2012-03-20 11:13:42Z

0

I guess you mean the data in a MySQL-database table right?

In that case, have a look at PHP's json_encode().

You can fetch the data from the db into an array and then covert it to JSON.

answered Mar 20, 2012 at 11:13

Christofer Eliasson

34k7 gold badges77 silver badges103 bronze badges

Comments

wdog · Accepted Answer · 2012-03-20 11:35:40Z

0

put the resultset of the query into an array and then use json_encode

$sql="Select * from table";
$l= array();
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) {
  $l[] = $row;
}
$j = json_encode($l);
echo $j;

you may use id table as index of the array.

answered Mar 20, 2012 at 11:35

wdog

6216 silver badges7 bronze badges

1 Comment

wdog Over a year ago

you must upgrade php or install pecl.php.net/package/json, look at the version php.net/manual/en/function.json-encode.php

Masoud Siahkali · Accepted Answer · 2016-12-10 13:02:31Z

using this code :

json_encode($array)

for example :

public function SELECT($tableName,$conditions){

      $connection = mysqli_connect($hostname, $userName, $password,$dbName);
      try {

        if (!$connection)
            die("Connection failed: " . $connection->connect_error);
        else
        {
            $qry = "";
            if(!$this->IsNullOrEmptyString($conditions))
               $qry = "SELECT * FROM `".$tableName."` WHERE ".$conditions;
            else
               $qry = "SELECT * FROM `".$tableName."`";

            $result = mysqli_query( $connection, $qry);
            if($result) {
                $emparray = array();
                while($row =mysqli_fetch_assoc($result))
                    $emparray[] = $row;

                echo(json_encode($emparray));           
            }
            else
                echo(mysqli_error($connection));       
            } 
            mysqli_close($connection); 
      } catch(Exception $ex) {
          mysqli_close($connection);
          echo($ex->getMessage());
      }  
 }

Neoistone · Accepted Answer · 2019-06-26 03:01:38Z

<?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);

?>

Collectives™ on Stack Overflow

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

7 Answers 7

4 Comments

2 Comments

Comments

Comments

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

4 Comments

2 Comments

Comments

Comments

1 Comment

Comments

Comments

Linked

Related