- How to Convert MySQL Data to JSON with PHP
- An Example of Conversion from MySQL to JSON
- Describing JSON
- Description of the json_encode Function
- How to Convert MySQL Data to JSON using PHP
- PHP Script to convert MySQL to JSON
- Step 1: Create Table and Add Demo Data in Database
- Convert MySQL to JSON using PHP
- Create the Database
- Convert MySQL to JSON using PHP
- Open MySQL Database Connection in PHP
- Fetch Data from MySQL Database
- Convert MySQL Result Set to PHP Array
- Convert PHP Array to JSON String
- Complete code: PHP MySQL to JSON
- Convert MySQL to JSON using PHP
- Create the Database
- Convert MySQL to JSON using PHP
- Open MySQL Database Connection in PHP
- Fetch Data from MySQL Database
- Convert MySQL Result Set to PHP Array
- Convert PHP Array to JSON String
- Complete code: PHP MySQL to JSON
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.
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'); Convert MySQL to JSON using PHP
In this article, you will learn a simple process to convert MySQL query results to JSON using the PHP programming language.
JSON (JavaScript Object Notation) is a lightweight, open standard file format. It is an array data type and consisting of attribute–value pairs. It is easy to read and write for humans. It is used primarily to transmit data between a web application and a server. JSON is popular among developers for data serialization. It is so popular that every modern programming language has methods to generate and parse JSON formatted data.
Create the Database
First we created a MySQL table and inserted some records into it. You can use the records if you already have otherwise, you can create them manually or copy and paste the following queries in your database.
CREATE TABLE IF NOT EXISTS `employee` ( `emp_id` int(11) NOT NULL, `name` varchar(100) NOT NULL, `age` int(11) NOT NULL, `department` varchar(100) NOT NULL ) INSERT INTO `employee` (`emp_id`, `name`, `age`, `department`) VALUES (1, 'John', 30, 'Software'), (2, 'Smith', 32, 'Hardware'), (3, 'Gaga', 33, 'Software'), (4, 'Mary', 29, 'Purchase'); Convert MySQL to JSON using PHP
These are the steps for converting mysql to json string using the PHP programming language.
Open MySQL Database Connection in PHP
First, establish the MySQL database connection using the mysqli_connect() function. Make sure to replace hostname, username, password and database with your database credentials and database name.
$connection = mysqli_connect("hostname","username","password","database") or die("Error " . mysqli_error($connection)); Fetch Data from MySQL Database
After opening the connection, fetch the required table data from the database using the PHP function mysqli_query().
//fetch table rows from mysql db $sql = "select * from employee"; $result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection)); Convert MySQL Result Set to PHP Array
//create an array $emparray = array(); while($row = mysqli_fetch_assoc($result))
Convert PHP Array to JSON String
Next, we use the PHP function json_encode() to convert the PHP array to a JSON string. This function takes PHP array, string, integer, float, Boolean as an input value and converts it to JSON String.
$data = json_encode($emparray, JSON_PRETTY_PRINT); echo 'pre>'; print_r($data); Complete code: PHP MySQL to JSON
Here, we have merged the above codes to convert the MySQL to JSON.
charset="utf-8"> Convert MYSQL Data to JSON $connection = mysqli_connect("hostname","username","password","database") or die("Error " . mysqli_error($connection)); //fetch table rows from mysql db $sql = "select * from employee"; $result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection)); //create an array $emparray = array(); while($row = mysqli_fetch_assoc($result)) < $emparray[] = $row; > $data = json_encode($emparray, JSON_PRETTY_PRINT); echo 'pre>'; print_r($data); //close the db connection mysqli_close($connection); ?>