Php mysql object to json

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

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:

7 Answers 7

$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 ''; > > > 

 $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

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.

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

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οο; 

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.

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.

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

Источник

How to Convert MySQL Data to JSON using PHP

One of the important responsibilities in web development for PHP is the conversion of data from MySQL to JSON format. JSON is favoured over XML as a data interchange format between web applications and has grown in popularity over time.

JSON has its own benefits, such as being lightweight, allowing for the storage of sophisticated data structures in plain text, and being extremely readable by humans. Here, we’ve already spoken about translating JSON data to MySQL. Let’s now examine how to use PHP to translate the MySQL result set to JSON.

PHP Script to convert MySQL to JSON

Step 1: Create Table and Add Demo Data in Database

If you do not have a database table ready, use the following SQL query to create a technologies table with id and tech_name table properties and add some data into the technologies table to check that data using the jQuery live search box in PHP.

CREATE TABLE technologies ( id INT NOT NULL AUTO_INCREMENT , tech_name VARCHAR(255) NOT NULL , PRIMARY KEY (id) ); INSERT INTO technologies VALUES(1,'HTML'); INSERT INTO technologies VALUES(2,'CSS'); INSERT INTO technologies VALUES(3,'JAVASCRIPT'); INSERT INTO technologies VALUES(4,'JQUERY'); INSERT INTO technologies VALUES(5,'PHP'); INSERT INTO technologies VALUES(6,'AJAX'); INSERT INTO technologies VALUES(7,'NODEJS'); INSERT INTO technologies VALUES(8,'EXPRESSJS'); INSERT INTO technologies VALUES(9,'ANGULARJS'); INSERT INTO technologies VALUES(10,'REACTJS'); 

Источник

How to Convert MySQL Data to JSON with PHP

Almost all web applications expect external data in JSON format. Consequently, converting data to JSON has become a common practice for developers.

Besides, JSON is quite comfortable in usage, as it is human readable and lightweight.

You can straightforwardly convert Data from the MySQL database to JSON with PHP.

An Example of Conversion from MySQL to JSON

In this section, we will consider an example by using the Sakila sample database, which works with standard MySQL installation. It is capable of fetching the first three rows of actor table into an associative array with the help of mysqli_fetch_assoc() . Afterwards, the array is encoded to JSON with json_encode .

Below, you can see the full example:

 // Initialize variable for database credentials $dbhost = 'hostname'; $dbuser = 'username'; $dbpass = 'password'; $dbname = 'sakila'; //Create database connection $dblink = new mysqli($dbhost, $dbuser, $dbpass, $dbname); //Check connection was successful if ($dblink->connect_errno) < printf("Failed to connect to database"); exit(); > //Fetch 3 rows from actor table $result = $dblink->query("SELECT * FROM actor LIMIT 3"); //Initialize array variable $dbdata = []; //Fetch into associative array while ($row = $result->fetch_assoc()) < $dbdata[] = $row; > //Print array in JSON format echo json_encode($dbdata); ?>

The output of the example is the following:

So, the output of the code is a valid JSON. It can be used along with jQuery / AJAX, and Fetch for passing data to web applications.

Describing JSON

JSON (JavaScript Object Notation) is considered a lightweight format applied to store and transport data.

It is commonly applied once data is forwarded from a server to a web page. JSON is quite simple to understand and use.

Syntactically, it is equivalent to the code used for generating JavaScript objects.

Description of the json_encode Function

This function is used for returning a string that contains a JSON representation of the provided value.

Its encoding is impacted by the provided options. Additionally, the encoding of float values relies on the value of serialize_precision.

For more information about the json_encode function and its usage, you can check out this page.

Источник