Generating a Json string by using data from database and getting JavaScript object by json parser

Return database rows as JSON

Fetch records as an array, set the JSON response content type, and let json_encode() build the response instead of assembling JSON by hand. For database examples, keep query logic separate from response formatting and use prepared statements when values come from a request.

Related concepts: SELECT · jQuery getJSON()

<?php
header('Content-Type: application/json; charset=UTF-8');
$stmt = $dbo->prepare('SELECT id, name, class, mark FROM student WHERE id < :max_id');
$stmt->execute(['max_id' => 5]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo json_encode(['data' => $rows], JSON_THROW_ON_ERROR);
?>

XMLHttpRequest between browser and server

User browser sends xmlhttprequest to backend server scripts to send data. We will be sending data back to the main page from web server by using Json formatted strings. These strings will contain number of Jason data value pairs taken from database table along with some other data.

Json string carries data to the parent application which we will display using JavaScript objects. To generate json string we will use php function json_encode().

We have our student table which has four fields ( id, name, class , mark). By using this we will develop some sample scripts to generate json string and then get back the data after parsing the string using JSON.parse.

Download the demo script with sample data at end of this tutorial

Getting a single record

Let us first try by collecting and returning a single record from our student table. For better security we have used PDO to connect to MySQL data base and getting the records. We have not discussed the mysql data collection part here, only the data formatting is shown.

$count=$dbo->prepare("select id,name,class as class1,mark from student where id=:id");
$count->bindParam(":id",$id,PDO::PARAM_INT,5);
$count->execute();
$row = $count->fetch(PDO::FETCH_OBJ);
$main = array('data'=>array($row));
echo json_encode($main);

In above code we have used json_encode function to generate the json string. Here is the output or the Json string

{"data":[{"id":"2","name":"Max Ruin","class1":"Three5","mark":"85"}]}

The above JSON string can be parsed with JSON.parse() and rendered with DOM methods. Using textContent keeps database values as text instead of interpreting them as HTML.

const myObject = JSON.parse(httpxml.responseText);
const table = document.createElement('table');

for (const record of myObject.data) {
    for (const [label, value] of Object.entries({
        ID: record.id,
        Name: record.name,
        Class: record.class1,
        Mark: record.mark
    })) {
        const row = table.insertRow();
        row.insertCell().textContent = label;
        row.insertCell().textContent = value;
    }
}

const display = document.getElementById('display');
display.replaceChildren(table);

Watch the first line in above code. We have used JSON.parse to create JavaScript object. Older code sometimes used eval(); do not use it for JSON. Use JSON.parse() instead. Historical example:

var myObject = eval('(' + httpxml.responseText + ')');

Do not use eval() to parse JSON because it can execute JavaScript. Modern browsers support JSON.parse(), so the historical json2.js fallback is no longer needed for current browser code.

Along with single data row we will also try to send few more data . We are sending two arrays of data here. There are two members one is data and other one is value. Each member contains two arrays containing some objects.

$main = array('data'=>array($row),'value'=>array("bgcolor"=>"$bgcolor","message"=>"$message"));
$main = array('data'=>array($row));
echo json_encode($main);

Above two lines are required changes in main code. You can see we have added message, bgcolor ( background colour ) etc. to be posted to main script. Here it is how to get the data from JavaScript object in main script.

$main = array('data'=>array($row),'value'=>array("bgcolor"=>"$bgcolor","message"=>"$message"));
echo json_encode($main);

Array of records

We will generate json string from an array of data records. Here is the code to get data and return the Json string

$sql="select *  from student where id <5"; 
$row=$dbo->prepare($sql);
$row->execute();
$result=$row->fetchAll(PDO::FETCH_ASSOC);

$main = array('data'=>$result,'value'=>array("bgcolor"=>"#f1fff1","message"=>"All records displayed"));

echo json_encode($main);

Here is the Json string as output

{"data":[{"id":"1","name":"John Deo","class":"Four5","mark":"75","sex":"male"},
{"id":"2","name":"Max Ruin","class":"Three5","mark":"85","sex":"male"},
{"id":"3","name":"Arnold","class":"Three5","mark":"55","sex":"male"},
{"id":"4","name":"Krish Star","class":"Four5","mark":"60","sex":"male"}],
"value":{"bgcolor":"#f1fff1","message":"All records displayed"}}

Receiving data and using LIKE query to get matching rows from Database table

More about PDO parameterized query, and SQL LIKE query. Sample query string is here

https://www.plus2net.com/php_tutorial/student.php?str=ab

After receving the data as parameter the query is prepared and matching rows of data is collected.

<?php
header('Content-Type: application/json; charset=utf-8');
require 'config-pdo.php';

$search = trim((string) ($_GET['str'] ?? ''));
if (mb_strlen($search) > 100) {
    http_response_code(400);
    echo json_encode(['error' => 'Search text is too long.'], JSON_THROW_ON_ERROR);
    exit;
}

$stmt = $pdo->prepare('SELECT id, name, class, mark, sex FROM student WHERE name LIKE :search');
$stmt->execute(['search' => '%' . $search . '%']);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo json_encode($result, JSON_THROW_ON_ERROR);
?>

Sample output is here for str=b

[{"id":7,"name":"My John Rob","class":"Fifth5","mark":78,"sex":"male"},
{"id":10,"name":"Big John","class":"Four5","mark":55,"sex":"male"},
{"id":14,"name":"Bigy","class":"Seven5","mark":88,"sex":"male"},
{"id":21,"name":"Babby John","class":"Four5","mark":69,"sex":"male"},
{"id":27,"name":"Big Nose","class":"Three5","mark":81,"sex":"male"},
{"id":28,"name":"Rojj Base","class":"Seven5","mark":86,"sex":"male"},
{"id":32,"name":"Binn Rott","class":"Seven5","mark":90,"sex":"male"}]

Using mysqli database connection

<?php
header('Content-Type: application/json; charset=utf-8');
require "config.php"; // mysqli connection string 
$sql="SELECT name,class,mark FROM student LIMIT 0,10";
$result = $connection->query($sql);
$row = array();
while($rs = $result->fetch_array(MYSQLI_ASSOC)) {
$row[] = $rs;
}

echo json_encode(array("student_data"=>$row));
$connection->close();
?>

Receiving data

After parsing the response, loop through myObject.data and create table cells with DOM methods. This avoids turning database values into executable HTML.

const myObject = JSON.parse(httpxml.responseText);
const table = document.createElement('table');
const header = table.insertRow();

for (const label of ['ID', 'Name', 'Class', 'Mark']) {
    const cell = document.createElement('th');
    cell.textContent = label;
    header.appendChild(cell);
}

for (const record of myObject.data) {
    const row = table.insertRow();
    for (const value of [record.id, record.name, record.class, record.mark]) {
        row.insertCell().textContent = value;
    }
}
document.getElementById('display').replaceChildren(table);

In addition to these array of records we have some single records stored in value. Here one sample to retrieve them

var message=myObject.value.message

Similarly another one

var bgcolor=myObject.value.bgcolor

Displaying data or calling from a file.

Different scripts can call any file which displays data in Json string. For example we can keep all the code inside one PHP file and give the name as data1.php

<?php
$my_data = new stdClass();
$my_data->name="plus2net";
$my_data->area="PHP";
echo json_encode($my_data);
?>

A current browser can request the JSON file with fetch() and write plain values with textContent.

<div id="display"></div>
<script>
fetch('data1.php')
    .then(response => {
        if (!response.ok) {
            throw new Error('Request failed');
        }
        return response.json();
    })
    .then(data => {
        document.getElementById('display').textContent =
            `NAME: ${data.name} AREA: ${data.area}`;
    })
    .catch(error => {
        document.getElementById('display').textContent = error.message;
    });
</script>

Or we can call this page by using jQuery

$.getJSON("student-data.php", function(return_data){

We can also use Ajax to call function

To see the example on how this code is used, you can read our Ajax based PHP MySQL Paging script

json_encode()
Json XML Ajax

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect: