Javascript S1E5 : Ajax PHP MySQL – INSERT, SELECT, SHOW [XMLHttpRequest]

Javascript S1E5 : Ajax PHP MySQL – INSERT, SELECT, SHOW [XMLHttpRequest]



Rating : 5/5 (1)




Introduction

Ajax stands for Asynchronous Javascript and XML. With Ajax you can make HTTP requests to the server without full page reload. This is faster since we only exchange the data relevant to the given request.
In this example we aim to look at how to make ajax HTTP GET and POST requests to the php server. In short we insert data to mysql database from the client in the javascript and retrieve data also from the database and bind it material list. User enters data via input elements and clicks a save button. The data gets sent to the php server and is saved to mysql database. We then automatically retrieve data from the same database and bind it to our list.
Furthermore we use XMLHttpRequest which is the native api for working with HTTP requests in Javascript. There is no third party library we use.
We use Materializecss to theme our app.

Questions this Project answers.

  • Javascript Ajax with PHP MySQL tutorial.
  • Save and retrieve data to PHP mysql from javascript.
  • Javascript MySQL INSERT,SELECT to MySQL database.
  • Use Javascript with PHP and MySQL.
  • Make HTTP GET and POST request to server via XMLHttpRequest.

Libraries Used

These are the third parties used in this project.

Screenshot

  • Here's the screenshot of the project.

Ajax PHP MySQL - INSERT,SELECT,SHOW
Ajax PHP MySQL

Ajax PHP MySQL - Full Project Structure
Full Project Structure

Ajax PHP MySQL - Table Structure
Table Structure

Tools used

Language: PHP,Javascript,HTML,CSS
IDE: PHPStorm.
Topic: Javascript Ajax MySQL,HTTP GET and HTTP POST,XMLHTTPRequest

Source Code

Lets have a look at the source code.

Constanst.phpDBAdapter.phpRequestHandler.phpmain.jsindex.htmlVideo TutorialDownload
  • Our Constants.php class.
  • Holds our database constants like server name, database name, username, password and table name
<?php
class Constants
{
    //DATABASE DETAILS
    static $DB_SERVER="localhost";
    static $DB_NAME="codartdb";
    static $USERNAME="sisi";
    static $PASSWORD="pass";
    const TB_NAME="post";
    //STATEMENTS
    static $SQL_SELECT_ALL="SELECT * FROM post";
}

 

  • Our DBAdapter class.
  • We perform our CRUD database operation here.
  • We insert data to mysql, retrieve the data and show return json encoded data.
  • We use object oriented mysqli.
<?php
require_once("Constants.php");
class DBAdapter
{
    /*******************************************************************************************************************************************/
    /*
       1.CONNECT TO DATABASE.
       2. RETURN CONNECTION OBJECT
    */
    public function connect()
    {
       // $con=mysqli_connect(Constants::$DB_SERVER,Constants::$USERNAME,Constants::$PASSWORD,Constants::$DB_NAME);
        $con=new mysqli(Constants::$DB_SERVER,Constants::$USERNAME,Constants::$PASSWORD,Constants::$DB_NAME);
        if($con->connect_error)
        {
             echo "Unable To Connect";
            return null;
        }else
        {
            return $con;
        }
    }
    /*******************************************************************************************************************************************/
    /*
       1.INSERT SPACECRAFT INTO DATABASE
     */
    public function insert($p)
    {
        // INSERT
        $con=$this->connect();
        if($con != null)
        {
            $sql="INSERT INTO post(title,body) VALUES('$p[0]','$p[1]')";
            try
            {
                $result=$con->query($sql);
                if($result)
                {
                    print(json_encode(array("Success")));
                }else
                {
                    print(json_encode(array("UNSUCCESSFUL")));
                }
                $con->close();
            }catch (Exception $e)
            {
                print(json_encode(array("ERROR","PHP EXCEPTION : CAN'T SAVE TO MYSQL. ".$e->getMessage())));
                $con->close();
            }
        }else{
            print(json_encode(array("ERROR","PHP EXCEPTION : CAN'T CONNECT TO MYSQL. NULL CONNECTION.")));
        }
    }
    /*******************************************************************************************************************************************/
    /*
       1.SELECT FROM DATABASE.
    */
    public function select()
    {
        $con=$this->connect();
        if($con != null)
        {
            $result=$con->query(Constants::$SQL_SELECT_ALL);
            if($result->num_rows>0)
            {
                $post_titles=array();
                while($row=$result->fetch_array())
                {
                    $spacecrafts[]=$row;
                    array_push($post_titles, $row['title']);
                }
                print(json_encode(array_reverse($post_titles)));
            }else
            {
                print(json_encode(array("PHP EXCEPTION : CAN'T RETRIEVE FROM MYSQL. ")));
            }
            $con->close();
        }else{
            print(json_encode(array("PHP EXCEPTION : CAN'T CONNECT TO MYSQL. NULL CONNECTION.")));
        }
    }
}

 

  • Our requesthandler.php class.
  • Will handle incoming http get and post requests and call the appropriate methods based on the request.
<?php
require_once("DBAdapter.php");
class RequestHandler
{
    /**
     *HANDLE INCOMING REQUEST
     */
    public function handleRequest()
    {
    
        if ($_POST['action'] == "save") {
            $dbAdapter = new DBAdapter();
            $title = $_POST['title'];
            $body = $_POST['body'];
            $dbAdapter->insert(array($title, $body));
        } else{
            $dbAdapter=new DBAdapter();
            $dbAdapter->select();
        }
    }
}
$requestHandler=new RequestHandler();
$requestHandler->handleRequest();

 

  • Our javascript file.
  • HTTP CLIENT CLASS
  • We make HTTP Get and HTTP Post requests here.
  • We use xmlhhtpprequest api.
  • We then programmatically create a list to display our retrieved data.
/*
 0 	UNSENT 	Client has been created. open() not called yet.
 1 	OPENED 	open() has been called.
 2 	HEADERS_RECEIVED 	send() has been called, and headers and status are available.
 3 	LOADING 	Downloading; responseText holds partial data.
 4 	DONE 	The operation is complete.
 */
var title=document.getElementById("titleID");
var body=document.getElementById("bodyID");
var displaySection=document.getElementById("displaySectionID");
var HttpClient = function () {
    //INSTANCE VARIABLES
    var requestType = "GET";
    var isAsynchronous = true;
    //GET FUNCTION
    this.get = function (targetURL, onMyDataRetrieved) {
        //AJAX USING XMLHTTPREQUEST
        var xmlhttprequest = new XMLHttpRequest();
        xmlhttprequest.onreadystatechange = function () {
            //IF OPERATION IS COMPLETED
            if (this.readyState == 4){
                //IF HTTP RESPONSE STATUS IS OK:200
                if(this.status == 200) {
                    onMyDataRetrieved(this.responseText);
                }else
                {
                    onMyDataRetrieved("GET Error, the Request has not succeeded")
                }
            }
        };
        //OPEN CONNECTION AND SEND ASYNCHRONOUS REQUEST
        xmlhttprequest.open(requestType, targetURL, isAsynchronous);
        xmlhttprequest.send();
    }
    //POST REQUEST
    this.post = function (targetURL, onMyDataSent) {
        var data = new FormData();
        data.append('action','save');
        data.append('title', title.value);
        data.append('body', body.value);
        //AJAX USING XMLHTTPREQUEST
        var xmlhttprequest = new XMLHttpRequest();
        xmlhttprequest.onreadystatechange = function () {
            //IF OPERATION IS COMPLETED
            if (this.readyState == 4){
                //IF HTTP RESPONSE STATUS IS OK:200
                if(this.status == 200) {
                    onMyDataSent(this.responseText);
                }else
                {
                    onMyDataSent("POST Error, the Request has not succeeded")
                }
            }
        };
        //OPEN CONNECTION AND SEND ASYNCHRONOUS REQUEST
        xmlhttprequest.open('POST', targetURL, isAsynchronous);
        xmlhttprequest.send(data);
    }
}
/*
SEN DATA TO SERVER VIA POST REQUEST
 */
function sendData(targetURL)
{
    var client = new HttpClient();
    client.post(targetURL,function (response) {
        console.log(response);
        var parsedResponse = JSON.parse(response);
        //RESET TEXTBOXES IF SUCCESSFULL
        if(parsedResponse.indexOf("Success")>-1)
        {
            title.value="";
            body.value="";
            getData("RequestHandler.php");
        }
    });
}
/*
 IMPLEMENT OUR HTTP CLIENT GET REQUEST
 */
function getData(targetURL) {
    var client = new HttpClient();
    client.get(targetURL, function (receivedData) {
        //console.log(receivedData);
        var posts = JSON.parse(receivedData);
        displaySection.innerHTML="";
        displaySection.appendChild(createListVew(posts));
    });
}
/*
CREATE LIST TO SHOW OUR DATA
 */
function createListVew(spacecrafts)
{
    var listView=document.createElement('ol');
    listView.className="collection";
    for(var i=0;i<spacecrafts.length;i++)
    {
        var listViewItem=document.createElement('li');
        listViewItem.className="collection-item";
        listViewItem.appendChild(document.createTextNode(spacecrafts[i]));
        listView.appendChild(listViewItem);
    }
    return listView;
}
getData("RequestHandler.php");
//end of file

 

  • Our index.html markup.
  • Represents our user interface.
  • Will have input form and list for showing data from mysql database.
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Ajax PHP Array</title>
    <!-- CSS  -->
    <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
    <link href="assets/css/materialize.min.css" type="text/css" rel="stylesheet" media="screen,projection"/>
    <link href="assets/css/style.css" type="text/css" rel="stylesheet" media="screen,projection"/>
</head>
<body>
<nav class="light-blue lighten-1" role="navigation">
    <div class="nav-wrapper container"><a id="logo-container" href="#" class="brand-logo">Camposha.info</a>
        <ul class="right hide-on-med-and-down">
            <li><a href="#">Home</a></li>
        </ul>
        <a href="#" data-activates="nav-mobile" class="button-collapse"><i class="material-icons">menu</i></a>
    </div>
</nav>
<div class="container">
    <h1 class="header center orange-text">Ajax PHP-MySQL List CRUD</h1>
    <h4 class="center light-blue-text">INSERT,SELECT,SHOW</h4>
    <div class="row">
        <div class="col s6">
            <form name="inputform" action="RequestHandler.php" method="post">
                <input type="text" id="titleID" placeholder="Post Title" value="" name="title">
                <textarea id="bodyID" placeholder="Post Body" class="materialize-textarea" value=""
                          name="body"></textarea>
                <input type="hidden" value="place" name="organization">
            </form>
            <a onclick="sendData('RequestHandler.php');" class="btn-floating btn-large waves-effect waves-light red"><i
                    class="material-icons">add</i></a>
        </div>
        <div id="displaySectionID">
            <p>In this example we see how INSERT,SELECT to and From MySQL via Ajax</p>
        </div>
    </div>
</div>
</div>
<br>
<br>
<br>
<br>
<br>
<footer class="page-footer orange">
    <div class="container">
        <div class="row">
            <div class="col l6 s12">
                <h5 class="white-text">Company Bio</h5>
                <p class="grey-text text-lighten-4">We are a team of college students working on this project like it's
                    our full time job. Any amount would help support and continue development on this project and is
                    greatly appreciated.</p>
            </div>
            <div class="col l3 s12">
                <h5 class="white-text">Settings</h5>
                <ul>
                    <li><a class="white-text" href="#!">Link 1</a></li>
                    <li><a class="white-text" href="#!">Link 2</a></li>
                    <li><a class="white-text" href="#!">Link 3</a></li>
                    <li><a class="white-text" href="#!">Link 4</a></li>
                </ul>
            </div>
            <div class="col l3 s12">
                <h5 class="white-text">Connect</h5>
                <ul>
                    <li><a class="white-text" href="#!">Link 1</a></li>
                    <li><a class="white-text" href="#!">Link 2</a></li>
                    <li><a class="white-text" href="#!">Link 3</a></li>
                    <li><a class="white-text" href="#!">Link 4</a></li>
                </ul>
            </div>
        </div>
    </div>
</footer>
<script src="main.js"></script>
</body>
</html>

 

Below is the video version of this tutorial.

Javascript S1E5 : Ajax PHP MySQL - INSERT,SELECT, SHOW [XMLHTTPRequest]

  • Download the Project below.
Download

How to Download and Run.

  1. Download the project above.
  2. You'll get a zipped file,extract it.
  3. You'll have these files : html,css ,javascript and php files.
  4. Go to mysql database and create a table with the table structure defined in the image above : id, title and body.
  5. Come to constants .php and update those database details.
  6. Place the project in a directory in your server and proceed over to index.html page.

More

  • Visit our channel for more examples like these.
  • Lets share tips and ideas in our Facebook Page.

Oclemy,Cheers.



Rating :

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    five × one =

    COMMENTS