JSON response by using PHP and MYSQL

To create a JSON response in PHP while fetching data from MySQL, you can follow these steps:

 

1. Connect to the MySQL database: Before retrieving data, establish a connection to your MySQL database using the appropriate credentials. You can use the mysqli or PDO extension in PHP for database connectivity.

 

<?php 
$host = 'localhost';//host name
$username = 'root'; //user name
$password = ''; //password
$database = 'my_db'; //database name

// Create a new mysqli object
$mysqli = new mysqli($host, $username, $password, $database);

// Check for connection errors
if ($mysqli->connect_errno) {
    die('Failed to connect to MySQL: ' . $mysqli->connect_error);
}
?>

 

2. Execute the MySQL query: Write your SQL query to fetch the desired data from the database. Execute the query using the query() method of the mysqli object.

<?php
$query = "SELECT * FROM users";
$result = $mysqli->query($query);

if (!$result) {
    die('Error in query: ' . $mysqli->error);
}

?>

 

3. Fetch and format the data: Use the appropriate method (fetch_assoc(), fetch_array(), etc.) to retrieve rows from the result set. Store the data in an array or an object as needed.

 

$data = array();

while ($row = $result->fetch_assoc()) {
    $data[] = $row;
}

 

4. Close the database connection: After fetching the data, close the database connection to free up resources.

$mysqli->close();

 

5. Create JSON response: Convert the fetched data to JSON format using the json_encode() function. Set the appropriate headers to indicate that the response is JSON.

<?php 
header('Content-Type: application/json');
echo json_encode($data);
?>

 

Putting it all together, here's a complete example:

<?php 
$host = 'localhost';
$username = 'your_username';
$password = 'your_password';
$database = 'your_database';

$mysqli = new mysqli($host, $username, $password, $database);

if ($mysqli->connect_errno) {
    die('Failed to connect to MySQL: ' . $mysqli->connect_error);
}

$query = "SELECT * FROM your_table";
$result = $mysqli->query($query);

if (!$result) {
    die('Error in query: ' . $mysqli->error);
}

$data = array();

while ($row = $result->fetch_assoc()) {
    $data[] = $row;
}

$mysqli->close();

header('Content-Type: application/json');
echo json_encode($data);

?>

 

This code will fetch the data from the MySQL database, convert it to JSON, and output it as the response.

 

example:

[
{
"id": "1",
"name": "test",
"user_name": "test",
"password": "dGVzdEAxMjM0IUAjJCNAIQ==",
"mobile": "9999999999",
"email": "demo@demo.com",
"user_type": "1",
"tables": "employee,student,temp1,temp2,temp3,users",
"timestamp": "2023-03-24 08:07:24"
}, 
{
"id": "2",
"name": "test2",
"user_name": "test2",
"password": "dGVzdEAxMjM0IUAjJCNAIQ==",
"mobile": "9999999998",
"email": "test2@test.com",
"user_type": "2",
"tables": "employee,student,temp1,temp2,temp3,users",
"timestamp": "2023-03-24 08:13:32"
}
]