PHP Database

PHP is a server-side programming language that can be used to interact with a database.

Imagine you have a database on your server, and you want to send a request to retrieve the first 10 rows from a table called “customers.”

On the client side, create a JSON object that specifies the number of rows you want to return.

Before sending the request to the server, convert the JSON object into a string and include it as a parameter in the URL of the PHP page.

Example

Use JSON.stringify() to convert the JavaScript object into a JSON string.

const limit = {“limit”:10};
const dbParam = JSON.stringify(limit);
xmlhttp = new XMLHttpRequest();
xmlhttp.onload = function() {
document.getElementById(“demo”).innerHTML = this.responseText;
}
xmlhttp.open(“GET”,“json_demo_db.php?x=” + dbParam);
xmlhttp.send();

Example explained:

Create an object containing a “limit” property and its value.
Convert the object into a JSON string.
Send a request to the PHP file, including the JSON string as a parameter.
Wait for the request to return the result (in JSON format).
Display the result received from the PHP file.

Now, review the PHP file.

PHP file

<?php
header(“Content-Type: application/json; charset=UTF-8”);
$obj = json_decode($_GET[“x”], false);

$conn = new mysqli(“myServer”, “myUser”, “myPassword”, “Northwind”);
$stmt = $conn->prepare(“SELECT name FROM customers LIMIT ?”);
$stmt->bind_param(“s”, $obj->limit);
$stmt->execute();
$result = $stmt->get_result();
$outp = $result->fetch_all(MYSQLI_ASSOC);

echo json_encode($outp);
?>

PHP File explained:

Use the PHP function json_decode() to convert the request into an object.
Access the database and populate an array with the requested data.
Add the array to an object and return the object as JSON by using the json_encode() function.