PHP Query SQL Database return JSON

As a Javascript developer, I always prefer to use NodeJS as my API endpoint when interacting with a backend database. Unfortunately, it’s not always feasible to use NodeJS (especially in a shared hosting environment) and you might have to fall back to good old PHP. So here’s a quick refresher on how you can perform queries to a SQL database using PHP from an Ajax or Fetch call.

Fetch Call

Suppose I have the following XMLhttp request using the fetch API:

fetch(`getid.php?name=${name}`)
 .then(response => response.json())
 .then((json) => {
 console.log(json);
 })
 .catch((error) => {
 console.error(error);
 });

This request calls the getname.php file with a query string of the name that we want to find. Take note that I am using a relative path when calling my file, therefore it’s safe to assume that my front-end and back-end are being served on the same server with the same port.

Database Config

The first thing we want to do is create a file called **db_config.php. **All of our database configuration info will go in this file.

<?php
/*
* Database secret stuff....shhhh
*/
$servername = "";
$username = "";
$password = "";
$dbname = "";
$con = mysqli_connect("$servername","$username","$password","$dbname") or die ("could not connect database");
?>

The only thing to note is that I am choosing to use the newer mysqli_connect api when connecting to the database.

PHP SQL Query

Now let’s query the database. Create a file called getid.php. The first thing that we need to do is include the db_config.php file. We are also going to initialize two arrays. The query_array will hold the separate query strings that we receive. The names array will hold the final ID’s that we received from each person in the database.

<?php

include "db_config.php";

$query_array = [];
$names = [];

parse_str($_SERVER["QUERY_STRING"], $query_array);
if(isset($query_array['name']) && $query_array['name'] != null )
{
$name = $query_array['name'];
$sql = "SELECT id FROM people where name='$name'";
$result = $con->query($sql);
if ($result->num_rows > 0) {
 while($row = $result->fetch_assoc()) {
  array_push($names,$row);
 }
}
$con->close();
header('Content-Type: application/json');
echo json_encode($names);
}else{
 http_response_code(400);
 die();
}

?>

We use the **parse_str **function to get any query strings and we save them to the query_array (always save to the result array, never directly assign them to a variable). Recall that our fetch command grabs this script with a query string called name. Therefore, we will have an array with one key with the value name. We also check to make sure that the name value is defined and not null, otherwise, we return a 400 error (though this is not checking for an empty string).

Finally, it makes the SQL request and pushes all the resulting rows into our names array. We then close the connection and echo the json_encoded value of the names array as a response. So our response in javascript will look like:

{“id”: “23453”}

That’s it!