How to convert mysql database table data to json using php

Question

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

Below is the php code I'm 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 it 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

I assume that you have correctly entered the data in the MySQL database table?

In this case, take a look at PHP json_encode () .

You can extract data from db into an array and then hide it until JSON.

Answer 3

put the query results in 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 can use the id table as an array index.

Answer 4

 <?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 will convert your mysql data to phpmyadmin in json. Works great

Answer 5

using this code:

 json_encode($array) 

eg:

 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()); } } 

Answer 6

As long as you are using MySQL 5.7 or later, you can create JSON data using only SQL and nothing else, that is, you need PHP to just pass SQL and get the JSON result. For instance:

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

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

Answer 7

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

?>