code to retrieve values from database using JSON
JSON Sample Codes

Sample Program code to retrieve values from database using JSON

Sample program code to retrieve values from database using JSON is an easy way to retrieve values from the database to the web application is as follows. In order to retrieve values from database create a database and table for retrieving values.

Sample program code to retrieve values from database using JSON

Before we proceed create a database and name it as ‘genderdb’. Take your browser type localhost/phpmyadmin  and hit enter to go to the database design page. If you get an error message while visiting the phpmyadmin page means that you have not configured your phpmyadmin page in your system. In order to configure please follow the steps in the link to configure phpmyadmin page :  . Also create a table under the database ‘genderdb’ and name it as gender. Create two field name in the gender table as ‘gen_id’ and ‘gen_name’. Populate the created  table with some data for retrieving purpose. The following image shows the database design and table populated with data.

 

code to retrieve values from database using JSON
phpmyadmin page

As you know the primary purpose of using JSON is to send data is very fast since JSON is text based format. Simply JSON is text data-interchange format. Communication between web application and server is in the form of text. To understand the basic knowledge to send and receive values between client and server check the following link : https://codeunplug.com/get-method-to-send-and-receive-javascript-object/ or https://codeunplug.com/post-method-to-send-and-receive-javascript-object/ before retrieve values from the database using JSON.

 Step 1 : Design a Home Page to retrieve values from database using JSON

 As you can see in the index.php page a new way of creating object (PDO) to establish connection with the database. The PHP Data Object (PDO) defines a lightweight, consistent interface for accessing different types of databases in PHP. For more details about PDO object and usage  please visit this link : http://php.net/manual/en/intro.pdo.php. Don’t forget to pass the username and password at the time of PDO object creation that you created at the time of php installation.

index.php

<?php

include "GenderRepository.php";

$config = include("config.php");

$db = new PDO($config["db"], $config["username"], $config["password"]);

$gender = new GenderRepository($db);

switch($_SERVER["REQUEST_METHOD"])

{

case "GET":

$result = $gender->getAll();

break;

}

header("Content-Type: application/json");

echo json_encode($result);

?>

Step 2 : Design a page to establish connection with database

 

config.php

<?php

return array(

"db" => "mysql:host=127.0.0.1;dbname=genderdb",

"username" => "root",

"password" => "admin123"

);

?>

Step 3 : Design a page to execute query and return results to the Home Page

GenderRepository.php

<?php

class Gender

{

public $gen_id;

public $gen_name;

};

class GenderRepository

{

protected $db1;

public function __construct(PDO $db1)

{

$this->db = $db1;

}

private function read($row)

{

$result = new Gender();

$result->gen_id = $row["gen_id"];

$result->gen_name = $row["gen_name"];

return $result;

}

public function getAll()

{

$sql = "SELECT * FROM gender";

$q = $this->db->prepare($sql);

$q->execute();

$rows = $q->fetchAll();

$result = array();

foreach($rows as $row)

{

array_push($result, $this->read($row));

}

return $result;

}

}

?>


Create a folder rename it as ‘jsonfromdb’ and save it in the root directory of PHP (www folder). All the three files index.php, config.php, GenderRepository.php must save in the already created folder ‘jsonfromdb’. Load the index.php page to run the code by typing localhost/jsonfromdb/index.php in the browser address field. Then you can see the result of values retrieved from the database as shown below.

 

code to retrieve values from database using JSON
Values from database using JSON

Leave a Reply

Your email address will not be published. Required fields are marked *