How to create JSON using data from MySQL database
JSON
(JavaScript Object Notation) is more preferred nowadays over XML as
it’s lightweight, readable and easily manageable for exchanging
data across various platforms.
we’ll see how JSON
Data can be created from Employee table stored in MySQL database.
1. Create Employee table in MySQL database.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE TABLE IF NOT EXISTS `employee` ( | |
| `id_employee` int(3) unsigned NOT NULL AUTO_INCREMENT, | |
| `emp_name` varchar(10) DEFAULT NULL, | |
| `designation` varchar(9) DEFAULT NULL, | |
| `date_joined` date DEFAULT NULL, | |
| `salary` decimal(7,2) DEFAULT NULL, | |
| `id_dept` int(2) DEFAULT NULL, | |
| PRIMARY KEY (`id_employee`) | |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ; | |
| -- | |
| -- Dumping data for table `employee` | |
| -- | |
| INSERT INTO `employee` (`id_employee`, `emp_name`, `designation`, `date_joined`, `salary`, `id_dept`) VALUES | |
| (1, 'Himmat', 'CLERK', '2010-12-12', 2500.00, 20), | |
| (2, 'Manoj', 'SALESMAN', '2015-02-20', 3500.00, 30), | |
| (3, 'Mukesh', 'SALESMAN', '2011-02-22', 3550.00, 30), | |
| (4, 'Naresh', 'MANAGER', '2011-04-02', 3975.00, 20), | |
| (5, 'Ankush', 'SALESMAN', '2012-09-28', 3300.00, 30), | |
| (6, 'Ram', 'MANAGER', '2008-05-01', 3800.00, 30), | |
| (7, 'Minaxi', 'MANAGER', '2003-06-09', 3850.00, 10), | |
| (8, 'Satyam', 'SALESMAN', '2012-09-08', 3600.00, 30), | |
| (9, 'Pooja', 'CLERK', '2011-01-12', 2400.00, 20), | |
| (10, 'Jony', 'CLERK', '2009-12-03', 2600.00, 30); |
Create PHP file to read MySQL table and create JSON.get_json.php
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| //Create Database connection | |
| $db = mysql_connect("localhost","root","root"); | |
| if (!$db) { | |
| die('Could not connect to db: ' . mysql_error()); | |
| } | |
| //Select the Database | |
| mysql_select_db("test_json",$db); | |
| //Replace * in the query with the column names. | |
| $result = mysql_query("select * from employee", $db); | |
| //Create an array | |
| $json_response = array(); | |
| while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { | |
| $row_array['id_employee'] = $row['id_employee']; | |
| $row_array['emp_name'] = $row['emp_name']; | |
| $row_array['designation'] = $row['designation']; | |
| $row_array['date_joined'] = $row['date_joined']; | |
| $row_array['salary'] = $row['salary']; | |
| $row_array['id_dept'] = $row['id_dept']; | |
| //push the values in the array | |
| array_push($json_response,$row_array); | |
| } | |
| echo json_encode($json_response); | |
| //Close the database connection | |
| fclose($db); | |
| ?> | |
DONE
*************************************************************************
More Advance
*************************************************************************
More Advance
If you want to create a API which Provide data on pass parameter then you can use following code:
The following code used in for parameter pass for geting data in json format
http://localhost/test_more_advance.php?no=10
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <!DOCTYPE html> | |
| <html> | |
| <head> | |
| <title>web service for Song list </title> | |
| <meta charset="UTF-8"> | |
| <meta name="viewport" content="width=device-width, initial-scale=1.0"> | |
| </head> | |
| <body> | |
| <div>Test PHP </div> | |
| <?php | |
| // header('Content-type: html/text'); | |
| /* include db.config.php */ | |
| $db_host = 'localhost'; //hostname | |
| $db_user = 'root'; // username | |
| $db_password = ''; // password | |
| $db_name = 'test'; //database name | |
| $conn = mysql_connect($db_host, $db_user, $db_password ); | |
| mysql_select_db($db_name, $conn); | |
| $no=isset($_GET['no']) ? mysql_real_escape_string($_GET['no']) : ""; | |
| if(empty($no)){ | |
| $data = array("result" => 0, "message" => "Wrong id Let �s try once again!"); | |
| } else { | |
| $i=0; | |
| // get user data | |
| $sql = 'SELECT * FROM `employee` limit '.$no; | |
| $select = mysql_query($sql); | |
| $result = array(); | |
| while($data = mysql_fetch_assoc($select)) { | |
| $result[] = $data; | |
| $data = array("result" => 0, "data" => $result); | |
| // echo "name =".$data['data'][$i]['emp_name']; | |
| $i++; | |
| echo '<br />'; | |
| } | |
| $data = array("result" => 0, "data" => $result); | |
| } | |
| mysql_close($conn); | |
| // header('Content-type: application/json'); | |
| echo json_encode($data); | |
| ?> | |
| </body> | |
| </html> | |
| //Test your JSON on http://pro.jsonlint.com/ | |
| //*********************** | |
| //PHP interview Question | |
| //************************************************* | |
| //Click here : - See more at: http://php999.blogspot.in/ |
This post is from 2015. Why are you using mysqli? mysql was deprecated long ago.
ReplyDeletethis is a basic understanding for newbie who started programming in PHP in our new post we will post PDO and mysqli
ReplyDeleteSAP MM,SD, real Time Training in Chennai
ReplyDeleteWe provides Best SAP MM,SD Training in Chennai wih real time project assistance by our leading Materail Management Consultant.
For Free Live Demo @ Call to 8122241286.
www.thecreatingexperts.com
SAP MM
SAP SD
This site is help full for every person and easily get money through bitcoin atm card . Please visit this site for bitcoin atm card ranking.you can change any money into another currency in any where in the world.
ReplyDeleteThere is no better idea of keeping your mobile loaded with balance all the time. If you are also looking for any useful way to utilize your top up phone with bitcoin then hurry up and avail our service as much as can. There is no limit of it. Make your mobile life happier with balance recharge through NETELL.NET. COME SOON .THANK YOU .
ReplyDeleteThis comment has been removed by the author.
ReplyDelete