How to output query result set as valid JSON

Valid JSON from MySQL & PHP Query

Have some data in a MySQL database  and want to output them as valid JSON? with a little help from PHP? this sample code below shows how to accomplish just that in 2 easy step.

  1. First, we need to set the header in our .php file to behave or output the content as JSON, for that we add this code at the top of the page, before anything else.
header('Content-Type: application/json; charset=utf-8');

2. Query your database and get the data you want to parse as JSON, we need to turn the data into an array

$query = "SELECT student_id, student_name, student_lastname, studeny_dob FROM students WHERE student_id ='$student_id' LIMIT 1";
$result = $con->query($query);
if($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$results[] = array(
'student_id' => $row['student_id'],
'student_name' => $row['student_name'],
'student_last_name' => $row['student_last_name'],
'student_dob' => $row['student_dob']
);
}
}
$json = json_encode($results);
echo $json;

The complete code,

<?php
	
	header('Content-Type: application/json; charset=utf-8');
	
	
	include("db_connection.php");
	
	
	
	$student_id = $_GET['id'];
	$student_id = $con->real_escape_string($_GET['id']);
	$query = "SELECT student_id, student_name, student_lastname, studeny_dob FROM students WHERE student_id ='$student_id' LIMIT 1";
	$result = $con->query($query);
	if($result->num_rows > 0) {
		while($row = $result->fetch_assoc()) {
			$results[] = array(
			'student_id' => $row['student_id'],
			'student_name' => $row['student_name'],
			'student_last_name' => $row['student_last_name'],
			'student_dob' => $row['student_dob']
			);
		}
	}
	$json = json_encode($results);
	echo $json;
?>

A Query output may look like this,

[{"student_id":"12345","student_name":"John","student_last_name":"Doe","student_dob":"1984"}]

 

The End

Leave a Reply

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