Save JavaScript variables to PHP/MySQL DataBase Securely with Ajax Post

We will show you the way how to save JavaScript variables to a PHP/MySQL DataBase easily and securely. In my demonstration we’ll go through an example in which we want to store an array for my users. The users can be identified by their unique names.

Log in to to your cPanel hosting, find the “MySQL Databases” in the menu and create a new database. Create a user and add it to the database, checking all privileges. We’ll use this user to connect to the DB.

Open phpMyAdmin and you should see the new DB in the list. Select it and make a new table. In my example I’ll call it usertimes.
Set to auto increment (A_I) the primary key and make sure the name field is a unique value. We’ll store the current save date and the rest of the variables depends on your specifications. Save the settings when it’s done. My database is ready to receive input.

Writing in the DataBase

The JavaScript function below collects the variables and posts them to the savesettings.php file.
The #saveWarningText div will display the success message returned from the PHP file or the error message if something went wrong.
The three variables we’re passing are the name, amount and times because the id is automatically incremented and the date can be generated on the server side.

function saveUserTimes() {
    $.post("savesettings.php",
    {
        name: $("#userName").val(),
        amount: aGlobalVariable,
        times: '1,2,3,4,5,6,7',
    },
    function(data,status){
        document.getElementById("saveWarningText").innerHTML = data;
        $( "#saveWarningText" ).fadeIn(100);
        setTimeout(function(){ $( "#saveWarningText" ).fadeOut(100); }, 3000);
    });
}

Create a PHP file to decode the post parameters and insert them into the DB.
At the beginning of the file we specify the database connection string.
Next we receive the three variables passed with the post method and escape the string to avoid SQL injections and ensure security.
Remember that JavaScript is executed on the client-side so everyone check and see the parameters we’re posting to our PHP file. Knowing our post variables hackers can try to execute database queries through our file. This is why beside the string escaping I’m also testing the length of the ‘times’ string and I’m not allowing to store it if the data is too large. You can include additional security steps to avoid hackers. Allowing only a limited amount of queries from one IP address can reduce the risk that someone will flood our database.
In the following lines we create the $sql query. We insert into the usertimes database table the name, date, amount and times variables. The id is automatically set, and the date is using the CURDATE() query. In case the current name value already exists in the database the 3 other variables are updated in that row. This is how the duplicate key is handled in my example.

Contents of savesettings.php file:

<?php
$servername = "localhost";
$username = "databaseUserName";
$password = "userPassword";
$dbname = "databaseName";

$conn = new mysqli($servername, $username, $password, $dbname); // Create connection
if ($conn->connect_error) {     // Check connection
    die("Connection failed: " . $conn->connect_error);
} 

$name = mysqli_real_escape_string($conn, $_POST['name']);
$amount = mysqli_real_escape_string($conn, $_POST['amount']);
$times = mysqli_real_escape_string($conn, $_POST['times']);

if (strlen($times) > 200000) {  $times = "";    }

$sql = "INSERT INTO usertimes (name,date,amount,times)
VALUES ('$name', CURDATE(), '$amount', '$times') ON DUPLICATE KEY UPDATE    
date=CURDATE(), amount='$amount', times='$times'";

if ($conn->query($sql) === TRUE) {
    echo "Page saved!";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>

At this point we’re able to write to the database, let’s see how to read the data.

Reading from the DataBase

Just like for the writing, we need a JavaScript function to send the variable to the PHP file and to process the retrieved data.

function openUserTimes(username) {
    $.post(
        "returndata.php",
        { name: username },
        function(response) {
            var myvariable = response.amount;
            var times = response.times;

            console.log('Retreived data: ', myvariable, times);
        }, 'json'
    );  
}

The only data we send in this example is the user name for which we want to get the two variables and log them in the console.

The PHP file starts with the connection string, then receives the username posted from the JavaScript. Next is the SQL query which selects everything in the DB table where the name field matches the current username.
Finally we package the returned data into JSON format which can be easily decoded by JavaScript.

The JSON format generated by the PHP:

{"name":"JohnDoe","date":"2017-02-01","amount":"4","times":"1,2,3,4"}

Contents of returndata.php file:

<?php
header('Content-type: application/json');

$servername = "localhost";
$username = "databaseUserName";
$password = "userPassword";
$dbname = "databaseName";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$name = mysqli_real_escape_string($conn, $_POST['name']);

$sql = 'SELECT * FROM usertimes WHERE name ="'. $name. '"';

$result = $conn->query($sql);
$response = array();

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $response['name'] = $row["name"];
        $response['date'] = $row["date"];
        $response['amount'] = $row["amount"];
        $response['times'] = $row["times"];
    }
    echo json_encode($response);
} else {
    echo "  0 results";
}
$conn->close();     
?>