Read Also : How to Parse JSON Data using jQuery Ajax into HTML
Here is the sample MySQL Dump data which are used in this tutorial, run the following sql query in your PHPMyAdmin it will create table with several records.
CREATE TABLE IF NOT EXISTS `tbl_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(25) NOT NULL,
`last_name` varchar(25) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
--
-- Dumping data for table `tbl_users`
--
INSERT INTO `tbl_users` (`id`, `first_name`, `last_name`) VALUES
(1, 'John', 'Doe'),
(2, 'Jane', 'Doe'),
(3, 'John', 'Cena'),
(4, 'Dwayne', 'Johnson');
ok, now make a connection to mysql database using PDO extension here is the code
<?php
$DBhost = "localhost";
$DBuser = "root";
$DBpass = "";
$DBname = "dbjson";
try{
$DBcon = new PDO("mysql:host=$DBhost;dbname=$DBname",$DBuser,$DBpass);
$DBcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(PDOException $ex){
die($ex->getMessage());
}
Select Records From MySQL
to convert mysql records first of all fetch all records from the users table .
$query = "SELECT * FROM tbl_users";
$stmt = $DBcon->prepare($query);
$stmt->execute();
Convert MySQL Rows to array
Here is the code to convert mysql results to an array
$userData = array();
while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
$userData['AllUsers'][] = $row;
}
Convert Array to JSON String
In PHP there’s a function called json_encode();. is used to convert array to JSON string. just add array name created above and put in this function like this.
echo json_encode($userData);
Here we got JSON Sting
this is the final JSON String we have converted MySQL to Array – Array to JSON, contains root element Allusers
{
"AllUsers": [
{
"id": "1",
"first_name": "John",
"last_name": "Doe"
},
{
"id": "2",
"first_name": "Jane",
"last_name": "Doe"
},
{
"id": "3",
"first_name": "John",
"last_name": "Cena"
},
{
"id": "4",
"first_name": "Dwayne",
"last_name": "Johnson"
}
]
}
Final Code
here is the final code of above all small pieces..
<?php
require_once 'dbconfig.php';
$query = "SELECT * FROM tbl_users";
$stmt = $DBcon->prepare($query);
$stmt->execute();
$userData = array();
while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
$userData['AllUsers'][] = $row;
}
echo json_encode($userData);
?>
Hope you like it, actually recently i had a work on JSON Apis, so i have decided share a small piece of json code. and guys please keep visiting and do share it with your dev friends
that’s it …