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 insert an object into an SQL database from javascript using php. If you haven’t already, check out my previous tutorial on making SQL queries from PHP using fetch API.
Fetch Post Method
The first thing that we need to do is create a fetch post request. However, whenever you do a post with PHP it usually expects the _content type _to be _application/x-www-form-_urlencoded. PHP is commonly used with HTML form submissions. Unfortunately, that’s not what we are going to be doing. So we will need to encode our object keys and values into the proper format before making the request. Suppose I have the following Javascript object:
myobj = {name: "Dave"}
So here’s what my request will look like:
const person = Object.keys(myobj).map(key => `${encodeURIComponent(key)}=${encodeURIComponent(myobj[key])}`).join('&');
fetch('submit.php', {
method: 'post',
headers: {
'Content-Type': 'application/x-www-form-urlencoded;charset=UTF-8',
},
body: person,
}).then((response) => {
//do something if you want
}).catch((data) => {
console.log(data);
});
Notice that I encoded the object (credit to this github issue). From there, I perform the usual POST request. 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.
INSERT into SQL Database
Now create a file called submit.php. Include the db_config.php file within that. We are then going to use the isset_ _ function to make sure that there’s a name field set in the POST keys. If not, we return a 400 response.
<?php
include "db_config.php";
if(isset($_POST['name']))){
$name=$_POST['name'];
$sql = "INSERT INTO people (name) VALUES (?)";
$stmt = $con->prepare($sql);
$stmt->bind_param("s", $name);
if($stmt->execute()){
http_response_code(200);
die();
}
else{
http_response_code(500);
die();
}
}
}else{
http_response_code(400);
die();
}
?>
When posting to a SQL database, you always want to use prepared statements. This is so that you can avoid SQL injection. The following lines take care of that:
$sql = "INSERT INTO people (name) VALUES (?)";
$stmt = $con->prepare($sql);
$stmt->bind_param("s", $name);
I put a question mark for the value of name. If I had multiple values, I would use multiple question marks. Then for the binding command, I use S because the name parameter is a string. Again, if I had multiple values I would use multiple S’s or their corrsponding datatypes.
When you insert something into a SQL database, you don’t really get anything back. So how do you know if a php SQL insert was successful? That’s where the following line comes in:
if($stmt->execute()){
http_response_code(200);
die();
}
If the code fails to execute, the conditional will return false and we return a 500 response. Otherwise, we return a 200 response. That’s it!