How to use jQuery, AJAX and PHP to insert and get database data - Digital Park - Newmarket Web Design
17137
post-template-default,single,single-post,postid-17137,single-format-standard,cookies-not-set,ajax_fade,page_not_loaded,,qode_grid_1300,qode-theme-ver-9.4.2,wpb-js-composer js-comp-ver-5.5.2,vc_responsive

How to use jQuery, AJAX and PHP to insert and get database data

How to use jQuery, AJAX and PHP to insert and get database data

I usually blog about the business side of why you need a website and focus mostly on marketing but today I want to start an era of educating you with some coding. This blog is intended for intermediate coders who want to know how they can do ‘on-page’ loading of data from a  database, based on a users interaction with an element.

Typically to do something like in PHP or HTML would be to reload the page, which is frustrating for the user. jQuery has been around for many, many years and I was an early adopter, due to its incredible ability to move and animate elements on web pages. This was a trend at one point.

This blog is going to assume you know, PHP, MySQL, Basic Javacript and jQuery.

PHP is an incredibly easy language to learn which is a dynamic language. HTML is static. We however, in this tutorial, going to turn static HTML to dynamic with the magic of jQuery and AJAX.

MySQL is the database of choice for most developers and so I would suggest we start with that.

First you want to have a database table. Below is a basic set up of a login table for user data. This can be posts, items, photos whatever you wish.


CREATE TABLE `users` (
`id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL,
`telephone` varchar(22) NOT NULL,
`birthday` varchar(100) NOT NULL,
`password` varchar(100) NOT NULL,
`thedate` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I don’t want to explain in detail what we are doing here, you should have some basic knowledge of MySQL each column holds a unique bit of data that we want to store.

Now for some PHP code. We need to create our database connection, we are using MySQLi.

This is our dbconfig.php file.

<?php
define("DB_SERVER", "localhost or server IP address");
define("DB_USER", "your db username");
define("DB_PASS", "your db password");
define("DB_NAME", "your db name");
?>

still straight forward save this file.  Now for our database class file:


<?php
class myDBC {
public $mysqli = null;
public function __construct() {
include_once "dbconfig.php";
$this->mysqli =
new mysqli(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
if ($this->mysqli->connect_errno) {
echo "Error MySQLi: ("&nbsp. $this->mysqli->connect_errno
. ") " . $this->mysqli->connect_error;
exit();
}
$this->mysqli->set_charset("utf8");
}
public function __destruct() {
$this->CloseDB();
}
public function runQuery($qry) {
$result = $this->mysqli->query($qry);
return $result;
}
public function runMultipleQueries($qry) {
$result = $this->mysqli->multi_query($qry);
return $result;
}
public function CloseDB() {
$this->mysqli->close();
}
public function clearText($text) {
$text = trim($text);
return $this->mysqli->real_escape_string($text);
}
public function lastInsertID() {
return $this->mysqli->insert_id;
}
public function totalCount($fieldname, $tablename, $where = "")
{
$q = "SELECT count(".$fieldname.") FROM "
. $tablename . " " . $where;
$result = $this->mysqli->query($q);
$count = 0;
if ($result) {
while ($row = mysqli_fetch_array($result)) {
$count = $row[0];
}
}
return $count;
}
}
?>

Save this file as dbclass.php this file will allow us to pass data to and from it with out having to keep rewriting the same queries over and over.

So we now have the basic PHP set up, we still have one more file to create. But first lets go ahead and set up our index.html or which ever page you want to add this too and the jQuery.


<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="user-scalable=no, initial-scale=1, maximum-scale=1, minimum-scale=1, width=device-width" />
<link rel="stylesheet" type="text/css" href="css/index.css" />
</head>
<body>
<form id="signUp" method="post" action="">
<input type="text" id="signUp_name" name="signUp_name" placeholder="Name">
<input type="text" id="signUp_email" name="signUp_email" placeholder="Email address">
<input type="tel" id="signUp_telephone" name="signUp_telephone" placeholder="Mobile Number">
<input type="text" id="signUp_birthday" name="signUp_birthday" placeholder="Date of birth">
<input type="password" id="signUp_password" name="signUp_password" placeholder="Password">
<input type="submit" value="Sign up" name="signup" id="signupbtn">
</form>
<script type="text/javascript" src="js/jquery.min.js"></script>
<script>
// add jQuery code here
</script>
</body>
</html>

Okay our basic html file with a sign up form in it. notice the part with the jQuery code here, this is where we need to add in our code that will submit this form to our PHP file, sanity check it, insert in to the database and return a success message. Note also, we do not need to add an action as the default for submission will not be working as jQuery is going to handle that.

Here is the jQuery that will submit our form data. In this example I am missing lots of steps to check fields are not empty but it is advised you add checks so that your database is not spammed with lots of null fields or leave yourself vulnerable to injection.

We bind the on click function of jQuery to our submit button.

$('#signupbtn').on('click',function(e){

We serialize the #signUp data, this is the ID associated to our sign up form. Serializing the data is the same as json_encoding in PHP this allows us to talk to the data quite easily.

var form_data=$("#signUp").serialize();


$.ajax({
url: "URL on your website that has the php handling code",
type: "post",
data: formData,
success: function(d) {
//do something fun
},
error: function (request, status, error) {
//console.log(request.responseText);
}
});

The AJAx now takes our data and passes it though to the PHP script. We have not yet made this PHP script so don’t worry we’ll get to that bit. Some useful things to know about AJAX calls. in the data section you can pass in serialized arrays which is what we are doing in this example but you can also use specific variables like so:

data: {'name': signupName,'email': signupEmail},

To make this work you would set your variables up outside by which I mean before your AJAX such as:

var signupName = $('#signUp_name');

Another useful tidbit is if you ever want to use the data you send back from your PHP code set your dataType to JSON. This makes it super easy to use the data in your success code.

$.ajax({
url: "URL on your website that has the php handling code",
type: "post",
data: formData,
dataType:"json",
success: function(d) {

If you don’t set the data type you will have lots of issues getting your data from the PHP files mostly your data will appear as [object Object]. Other dataType can be text or html. But set it to JSON is best.

So our full jQuery should look like this:


$('#signupbtn').on('click',function(e){
formData = $("#signUp").serialize();
$.ajax({
url: "Your php code URL",
type: "post",
data: formData,
success: function(d) {
$('.success').show();
$('.success').delay(3000).fadeOut();
},
error: function (request, status, error) {
//console.log(request.responseText);
}
});
}
}
e.preventDefault();
});

e.preventDefault(); stops the default form action of submitting the form data and using the action=”” in the form. We stop it reloading the page, otherwise you will end in a loop and your data will not submit to your PHP file.

Now lets make our success return do something fun. Lets show a

with a message. You will need to add a div with a style of display:none; in your HTML file. I would add it above your form.


$('.success').show();
$('.success').delay(3000).fadeOut();

As you can see in the jQuery code I added in these elements. I want them to show the success message, then wait 3 seconds then fade the message away. The HTML would look like this:

Thank you for signing up.

 

So your HTML file will now look like this:


<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="user-scalable=no, initial-scale=1, maximum-scale=1, minimum-scale=1, width=device-width" />
<link rel="stylesheet" type="text/css" href="css/index.css" />
</head>
<body>
<div class="success" style="display:none;">Thank you for signing up.</div>
<form id="signUp" method="post" action="">
<input type="text" id="signUp_name" name="signUp_name" placeholder="Name">
<input type="text" id="signUp_email" name="signUp_email" placeholder="Email address">
<input type="tel" id="signUp_telephone" name="signUp_telephone" placeholder="Mobile Number">
<input type="text" id="signUp_birthday" name="signUp_birthday" placeholder="Date of birth">
<input type="password" id="signUp_password" name="signUp_password" placeholder="Password">
<input type="submit" value="Sign up" name="signup" id="signupbtn">
</form>
<script type="text/javascript" src="js/jquery.min.js"></script>
<script>
// add jQuery code here
</script>
</body>
</html>

You may want to set the display:none in your CSS file like this:

.success{display:none;}

Okay so, now we have our database table set up, we have our database config file, we have our database class, static HTML page and the jQuery to handle the sign up data. We can now finally create our PHP file to handle the data.

<?php
include "dbclass.php";
$mydb = new myDBC();
$name = $mydb->clearText($_POST['signUp_name']);
$email = $mydb->clearText($_POST['signUp_email']);
$tel = $mydb->clearText($_POST['signUp_telephone']);
$dob = $mydb->clearText($_POST['signUp_birthday']);
$dob = strtotime($dob);
$pw = $mydb->clearText($_POST['signUp_password']);
$sql = "INSERT INTO users (name,email,telephone,birthday,password,thedate) VALUES ('$name','$email','$tel','$dob','$pw ',NOW())";
$mydb->runQuery($sql);
$id = $mydb->lastInsertID();
echo 'success';
?>

This PHP script is basic, you can add lots more to validation etc, but the principle is that it takes the serialized for data from the AJAX and passes in using $_POST.

We first include our dbclass.php this allows us to do the insert to the database, check our fields for any MySQL shenanigans.

Next, we initiate the class with $mydb = new myDBC(); Note this is the name of our class in dbclass.php.

Using the $mydb->clearText(); cleans our data before inserting in to the database.

We insert the data with the same column names in our database and set the values to the submitted variables we have created. We then runQuery.

The next part, $id = $mydb->lastInsertID(); can be passed back if we wanted to but in this case its not really needed, but just added it in so you can see how it works from the class file.

Finally we echo our result.

When we go back to our HTML jQuery code, the d in the function (d) is the echo returned from the PHP code.

success: function(d) {

You can use the console.log(d); in the success code if you want it to see return the ‘success’ message.

Be aware that 9 times out of 10, the success will trigger even if your php code did not execute properly. All the AJAX is doing is checking if the file was called and response was made. You would need to check the response for errors.

The error code on the AJAX will return only errors with AJAX and not your PHP code.

error: function (request, status, error) {
//console.log(request.responseText);
}

Now, here is how I would check for errors using both PHP and the jQuery code which uses the dataType: json. This is for people that want to send data back to the front end to do something with it.

First the PHP using a select query which will return a verify that the data was successfully pulled from the database:

Save this as the file you will call in your jQuery AJAX call in the url: section.

<?php
$newsql = "SELECT id,name,email,telephone,birthday from users where id = '$id' LIMIT 1";
$res = $mydb->runQuery($newsql);
if(mysqli_num_rows($res) > 0)
{
$row1 = mysqli_fetch_array($res,MYSQLI_ASSOC);
$arr = array('data'=>array($row1),'verify'=>'Y');
echo json_encode($arr);
}
else{
$arr = array('data'=>'','verify'=>'N');
echo json_encode($arr);
}
}
else
{
$arr = array('data'=>'','verify'=>'N');
echo json_encode($arr);
}
?>

I do things differently and how I feel most comfortable doing it and other coders will either hate my way or get on with their own way. Here I call the database and pull the results back, I check to see if the result is greater than 0. Usually you might get a null back if the query was wrong or the column or id was not found.

I then fetch the data and bind it to $row1.

Next, I build an array with the data and add a little verify which can be Y or N. I then json_encode($arr) the data to echo it back to the front end. remember you need to set the dataType to JSON.

Front end code:


var id = 1;
$.ajax({
url: "my php URL",
type: "post",
data: {'id': id},
dataType: "json",
success: function(d) {
if(d.verify == 'Y'){
window.location.href ='dashboard.html';
}
else
{
window.location.href ='index.html';
}
}
});

In this example, I send an ID which would link to a column id in the database and look for user 1. I would then send that data to the PHP code above. The response would return to the function(d) in the success I can then use the statement of if(d.verify == ‘Y’) take a user to the dashboard.html but if the return is anything else take the user to the index.html

Clearly, this code is stripped down from what I would use fully, such as using the data to add to localStorage etc. But you should get a general idea of how you can use MySQL, PHP, jQuery, AJAX and static HTML to submit data or get data from the database without reloading the page.

Thanks for reading.