Android MySQL Retrofit Full Course – INSERT SELECT UPDATE DELETE SEARCH PAGINATION → PHP MySQL – INSERT SELECT UPDATE DELETE SEARCH PAGINATION – Camposha

Android MySQL Retrofit Full Course – INSERT SELECT UPDATE DELETE SEARCH PAGINATION

PHP MySQL – INSERT SELECT UPDATE DELETE SEARCH PAGINATION

Introduction

PHP is the most popular web programming language, like it or not. It’s powering WordPress which has itself become a dorminant platform. PHP is fast enough and beginner friendly. Web apps or platforms like Facebook and Wikipedia were written in PHP, at least originally.

PHP and MySQL seems to be made up for each other. MySQL is a powerful RDBMS database and is also dorminant in it’s niche. However in recent years these two technologies have started facing stiff competition. Node.js has become a rising star. Nodejs allows writing of server code in Javascript and is also fast, taking advantage of the famous V8 engine. MySQL on the other hand is facing NoSQL database systems, especially MongoDB.

However, these two are still the go to technologies in the web arena. It’s not that hard to write your first hello world with PHP and MySQL. In fact as long as you have interpreter installed, you can even write it in a notepad. There is no need to install third party modules. This can be regarded by some as one of the advantages of PHP, it’s tight integration with MySQL. Frameworks like Nodejs and Python web frameworks do require installation of packages to work with database system like MySQL.

In this lesson you will learn how to write code in PHP that can:

  1. INSERT
  2. SELECT
  3. UPDATE
  4. DELETE
  5. PAGINATE
  6. SEARCH

Thus we will not only perform CRUD operations but also perform pagination and search. The output will be an API in pure PHP without REST libraries or frameworks. Thus our android app will be able to take advantage and send and receive data to and from mysql. PHP is the middle man here.

Let’s start.

1. Create index.php file

Well first create a folder in your root folder called php. In that folder create another called scientists. In that create a file called index.php. This is where we will place all our PHP code.

If you are using XAMPP like we are then place it in htdocs folder. So your structure should be like this:

htdocs/php/scientists/index.php

We will write object oriented PHP code.

Start by specifying PHP opening tag:

<?php

//Then our code here

The above tag will tell PHP interpretor of where our PHP code is beginning. There is no need to write the closing tag these days.

Live Video Lesson(Recommended)

2. Create Constants Class

Well we will have a Constants class holding our database credentials.

First add the following code inside your index.php after your php tag you had specified earlier:

class Constants{
    //DATABASE CREDENTIALS
    static $DB_SERVER="localhost";
    static $DB_NAME="scientistsDB";
    static $USERNAME="root";
    static $PASSWORD="";

    //SQL STATEMENT
    static $SQL_SELECT_ALL="SELECT * FROM scientistsTB";
}

Clearly you can see we have:

No. Credential Explanation
1. DB_SERVER Our database server. Specify localhost if you are hosted locally
2. DB_NAME Our database name. Specify the name of your database.
3. USERNAME The database user.In production don’t use root.
4. PASSWORD The user password. Don’t use empty password in prodction.

3. Create a Scientists CRUD class

I have just called it Scientists. Just create inside the index.php file.

Then add the class definition as below:

class Scientists{
    //...

4. Connect to MySQL

We said we would write object oriented code. Well we will also use mysqli, a class that allows us connect to mysql in an object oriented manner.

Start by adding the following code inside our Scientists class:

    public function connect()
    {
        $con=new mysqli(Constants::$DB_SERVER,Constants::$USERNAME,Constants::$PASSWORD,
        Constants::$DB_NAME);
        if($con->connect_error) {
            return null;
        }else{
            return $con;
        }
    }

In the above code, we have created a function called connect(). Inside it we start by instantiating the mysqli class. In the mysqli constructor we are passing the database server, user as well as password and name.

Then we’ve used the connect_error property to check if our connection attempt is raising any errors. If so we simply return null. Otherwise we return the mysqli object.

5. Insert into MySQL

Let’s now define a function to allow us INSERT into MySQL.

Start by adding the following code below the connect() function:

    public function insert(){
        $name = $_POST['name'];
        $description = $_POST['description'];
        $galaxy = $_POST['galaxy'];
        $star = $_POST['star'];
        $dob = $_POST['dob'];
        $died = $_POST['died'];

        $con=$this->connect();
        if($con != null)
        {
            $sql = "INSERT INTO scientiststb (name, description, galaxy,star, dob, died) VALUES
            ('$name','$description','$galaxy','$star','$dob','$died')";
            $result = $con->query($sql);
            if($result == TRUE){
                 print(json_encode(array('code' =>1, 'message' => 'Data Successfully Inserted')));
            }else{
                print(json_encode(array('code' =>2,
                'message' => 'Unable to INSERT Data. However Connection was successful')));
            }
            $con->close();
        }else{
            print(json_encode(array('code' =>3,
            'message' => 'ERROR: PHP WAS UNABLE TO CONNECT TO MYSQL DUE TO NULL CONNECTION.')));
        }
    }

Well inside our insert() function, we start by retrieving the data contained in our HTTP request.

The $_POST is an associative array of variables passed to the current script via the HTTP POST method.

Our android app will be making a HTTP POST request with data packed into it. Those data can be accessed in our PHP code via the _POST array in form of simple variables.

After which we establish our connection via our connect() function. If the connection fails we print out an encoded array containing our error code and error message.

Otherwise we define our SQL insert state. The query() method of our mysqli class will execute the SQL statement, returning a boolean value.

If that result equals TRUE, then we had success, thus we print a success message, otherwise we print out a failure message.

5. Update MySQL

The next step is to update our mysql update.

Start by adding the following code:

    public function update(){
        $id = $_POST['id'];
        $name = $_POST['name'];
        $description = $_POST['description'];
        $galaxy = $_POST['galaxy'];
        $star = $_POST['star'];
        $dob = $_POST['dob'];
        $died = $_POST['died'];

        $con=$this->connect();
        if($con != null){
            $sql = "UPDATE  scientiststb SET name = '$name',description = '$description',
             galaxy = '$galaxy', star = '$star', dob = '$dob',died = '$died' WHERE id='$id'";

            $result = $con->query($sql);
            if($result == TRUE){
                print(json_encode(array('code' =>1, 'message' => 'Data Successfully Updated')));
            }else{
                print(json_encode(array('code' =>2,
                'message' => 'Unable to UPDATE Data. However Connection was successful')));
            }
            $con->close();
        }else{
            print(json_encode(array('code' =>3,
            'message' => 'ERROR: PHP WAS UNABLE TO CONNECT TO MYSQL DUE TO NULL CONNECTION.')));
        }
    }

Well it’s pretty similar to our insert() function. However this time round take note of the id we are receiving from our HTTP POST request. Well it’s important. It will help identify the row that is to be updated.

Then you can see after we have established connection and if it is successful we execute our SQL UPDATE statement.Well that statement is different from the INSERT statement. You can see how the id is helping us identify the target row.

6. Delete From MySQL

Well delete again. We’ll use a HTTP POST request to send a delete request.

Write the following code:

    public function delete(){
        $id = $_POST['id'];

        $con=$this->connect();
        if($con != null){
            $sql = "DELETE FROM scientiststb WHERE id ='$id'";
            $result = $con->query($sql);
            if($result == TRUE){
                print(json_encode(array('code' =>1, 'message' => 'Data Successfully Deleted')));
            }else{
                print(json_encode(array('code' =>2,
                'message' => 'Unable to DELETE Data. However Connection was successful')));
            }
            $con->close();

        }else{
            print(json_encode(array('code' =>3,
            'message' => 'ERROR: PHP WAS UNABLE TO CONNECT TO MYSQL DUE TO NULL CONNECTION.')));
        }
    }

We only need the id of the row to be deleted sent to us. Then we establish our connection and execute our DELETE stetment. We then return a response to the client.

7. Select All Rows

If you don’t have so much data in the database, you can safely select all the data.

Here’s a PHP function to select for us all the data:

    public function select()
    {
        $con=$this->connect();
        if($con != null)
        {
            $result=$con->query(Constants::$SQL_SELECT_ALL);
            if($result->num_rows > 0)
            {
                $scientists = array();
                while($row=$result->fetch_array())
                {
                    array_push($scientists, array("id"=>$row['id'],"name"=>$row['name'],
                    "description"=>$row['description'],"galaxy"=>$row['galaxy'],"star"=>$row['star'],
                    "dob"=>$row['dob'],"died"=>$row['died']));
                }
                print(json_encode(array("code" => 1,"message"=>"Success", "result"=>$scientists)));
            }else{
                print(json_encode(array("code" => 0, "message"=>"Data Not Found")));
            }
            $con->close();

        }else{
            print(json_encode(array('code' =>3,
            'message' => 'ERROR: PHP WAS UNABLE TO CONNECT TO MYSQL DUE TO NULL CONNECTION.')));
        }
    }

You had seen that we had earlier on, in our Constants class, defined the SELECT all statement. Well we simply execute it using the query() function.

Then take note that we are looping through our result, pushing an array of individuals cells into an outer array. That outer then is json encoded and sent to the client.

8. Search and Paginate data

Well now we want to create a function to allow us perform search and pagination. These two are important because they put a restraint in the quantity of data we are sending to the client. It’s not a good idea to be pushing thousands of rows of data to the client. It consumes alot of memory and the users bandwith. Moreover nobody can read through hundreds of rows at least in these times.

Add the following code:

    public function search()
    {
        $query=$_POST['query'];
        $limit=$_POST['limit'];
        $start=$_POST['start'];

        $sql="SELECT * FROM scientistsTB WHERE name LIKE '%$query%' OR galaxy LIKE '%$query%'
         LIMIT
         $limit OFFSET $start ";

        $con=$this->connect();
        if($con != null)
        {
            $result=$con->query($sql);
            if($result->num_rows>0)
            {
                $scientists=array();
                while($row=$result->fetch_array())
                {
                    array_push($scientists, array("id"=>$row['id'],"name"=>$row['name'],
                    "description"=>$row['description'],"galaxy"=>$row['galaxy'],"star"=>$row['star'],
                    "dob"=>$row['dob'],"died"=>$row['died']));
                }
                print(json_encode(array("code" => 1, "message"=>"Success", "result"=>$scientists)));
            }else{
                print(json_encode(array("code" => 0, "message"=>"Data Not Found")));
            }
            $con->close();

        }else{
            print(json_encode(array('code' =>3,
            'message' => 'ERROR: PHP WAS UNABLE TO CONNECT TO MYSQL DUE TO NULL CONNECTION.')));
        }
    }

You can see we are receiving some interesting variables being passed us from the client. The first is the search term or query. Then we have the limit and the start. The query, as you can guess is the user’s search constraint. The limit is the amount of data to be passed. The start is the also called the offset. It’s where our retrieval starts. For example in page 2, we can have our retrieval starting at row number 6 or 11 based on whether our limit is 5 or 10.

Then we perform a select query with those parameters specified.

9. Handle requests

We now need a sort of controller. We will create a function to listen to our HTTP requests and react by invoking the right methods.

Add the following code:

    public function handleRequest() {

        if($_SERVER['REQUEST_METHOD'] == 'POST')
        {
            if (isset($_POST['action'])) {

                $action=$_POST['action'];

                if($action == 'INSERT'){
                    $this->insert();
                }else if($action == 'UPDATE'){
                    $this->update();
                }else if($action == 'DELETE'){
                    $this->delete();
                }else if($action == 'GET_PAGINATED'){
                    $this->search();
                }else if($action == 'GET_PAGINATED_SEARCH'){
                    $this->search();
                }else{
                    print(json_encode(array('code' =>4, 'message' => 'INVALID REQUEST.')));
                }
            } else{
                print(json_encode(array('code' =>5, 'message' => 'POST TYPE UNKNOWN.')));
            }

        }else{
            $this->select();
        }
    }

We have started by checking the request method. If it is not a HTTP POST request, we invoke the select(). Our app will only be making a HTTP POST and HTTP GET request.

If it is a HTTP POST request, we obtain the action string. This string will be explicitly specified in our client. It identifies for us the intended action. Then we react according to that action.

Now make sure you close the Scientists class:

}

Then OUTSIDE the class:

//Outside the class. Instantiate Scientist then invoke the handleRequest() to listen to our requests.
$s=new Scientists();
$s->handleRequest();

//end

FULL CODE:

Here is the full code:

<?php
/**
* Let's Create a class to hold our database constants.
*/
class Constants{
//DATABASE CREDENTIALS
static $DB_SERVER="localhost";
static $DB_NAME="scientistsDB";
static $USERNAME="root";
static $PASSWORD="";
//SQL STATEMENT
static $SQL_SELECT_ALL="SELECT * FROM scientistsTB";
}
/**
* This class will contain methods to receive our http requests, manipulate the
*  database and send
* result back to the client
*/
class Scientists{
/**
* 1. CONNECT TO DATABASE.
* 2. RETURN CONNECTION OBJECT
* 3. IF NO CONNECTION THEN RETURN NULL.
*/
public function connect()
{
$con=new mysqli(Constants::$DB_SERVER,Constants::$USERNAME,Constants::$PASSWORD,
Constants::$DB_NAME);
if($con->connect_error) {
return null;
}else{
return $con;
}
}
/**
* 1. Receieve data from our HTTP POST Request.
* 2. Connect to mysql database.
* 3. Save those data to mysql database.
* 4. Return a response to the client.
*/
public function insert(){
$name = $_POST['name'];
$description = $_POST['description'];
$galaxy = $_POST['galaxy'];
$star = $_POST['star'];
$dob = $_POST['dob'];
$died = $_POST['died'];
$con=$this->connect();
if($con != null)
{
$sql = "INSERT INTO scientiststb (name, description, galaxy,star, dob, died) VALUES
('$name','$description','$galaxy','$star','$dob','$died')";
$result = $con->query($sql);
if($result == TRUE){
print(json_encode(array('code' =>1, 'message' => 'Data Successfully Inserted')));
}else{
print(json_encode(array('code' =>2,
'message' => 'Unable to INSERT Data. However Connection was successful')));
}
$con->close();
}else{
print(json_encode(array('code' =>3,
'message' => 'ERROR: PHP WAS UNABLE TO CONNECT TO MYSQL DUE TO NULL CONNECTION.')));
}
}
/**
* This method will:
* 1. Receiev data from the HTTP POST request of the client
* 2. Connect to mysql and update the id specified.
* 3. Return a response to the client.
*/
public function update(){
$id = $_POST['id'];
$name = $_POST['name'];
$description = $_POST['description'];
$galaxy = $_POST['galaxy'];
$star = $_POST['star'];
$dob = $_POST['dob'];
$died = $_POST['died'];
$con=$this->connect();
if($con != null){
$sql = "UPDATE  scientiststb SET name = '$name',description = '$description',
galaxy = '$galaxy', star = '$star', dob = '$dob',died = '$died' WHERE id='$id'";
$result = $con->query($sql);
if($result == TRUE){
print(json_encode(array('code' =>1, 'message' => 'Data Successfully Updated')));
}else{
print(json_encode(array('code' =>2,
'message' => 'Unable to UPDATE Data. However Connection was successful')));
}
$con->close();
}else{
print(json_encode(array('code' =>3,
'message' => 'ERROR: PHP WAS UNABLE TO CONNECT TO MYSQL DUE TO NULL CONNECTION.')));
}
}
/**
* This method will delete the row with the specified id.
* 1. Connect to MySQL.
* 2. Receive the id from our HTTP request.
* 3. Delete the row with that id.
* 4. Return a response.
*/
public function delete(){
$id = $_POST['id'];
$con=$this->connect();
if($con != null){
$sql = "DELETE FROM scientiststb WHERE id ='$id'";
$result = $con->query($sql);
if($result == TRUE){
print(json_encode(array('code' =>1, 'message' => 'Data Successfully Deleted')));
}else{
print(json_encode(array('code' =>2,
'message' => 'Unable to DELETE Data. However Connection was successful')));
}
$con->close();
}else{
print(json_encode(array('code' =>3,
'message' => 'ERROR: PHP WAS UNABLE TO CONNECT TO MYSQL DUE TO NULL CONNECTION.')));
}
}
/**
* This method will:
* 1. Connect to MySQL.
* 2. Select all data from database.
* 3. Return those data as a response.
*/
public function select()
{
$con=$this->connect();
if($con != null)
{
$result=$con->query(Constants::$SQL_SELECT_ALL);
if($result->num_rows > 0)
{
$scientists = array();
while($row=$result->fetch_array())
{
array_push($scientists, array("id"=>$row['id'],"name"=>$row['name'],
"description"=>$row['description'],"galaxy"=>$row['galaxy'],"star"=>$row['star'],
"dob"=>$row['dob'],"died"=>$row['died']));
}
print(json_encode(array("code" => 1,"message"=>"Success", "result"=>$scientists)));
}else{
print(json_encode(array("code" => 0, "message"=>"Data Not Found")));
}
$con->close();
}else{
print(json_encode(array('code' =>3,
'message' => 'ERROR: PHP WAS UNABLE TO CONNECT TO MYSQL DUE TO NULL CONNECTION.')));
}
}
/**
* This method will:
* 1. Receive a HTTP POST request from a client.
* 2. Get the query,limit and start data from that request.
* 3. Perform a paginated search against our mysql database after connecting.
* 4. Return a response with either data or error message.
*/
public function search()
{
$query=$_POST['query'];
$limit=$_POST['limit'];
$start=$_POST['start'];
$sql="SELECT * FROM scientistsTB WHERE name LIKE '%$query%' OR galaxy LIKE '%$query%'
LIMIT
$limit OFFSET $start ";
$con=$this->connect();
if($con != null)
{
$result=$con->query($sql);
if($result->num_rows>0)
{
$scientists=array();
while($row=$result->fetch_array())
{
array_push($scientists, array("id"=>$row['id'],"name"=>$row['name'],
"description"=>$row['description'],"galaxy"=>$row['galaxy'],"star"=>$row['star'],
"dob"=>$row['dob'],"died"=>$row['died']));
}
print(json_encode(array("code" => 1, "message"=>"Success", "result"=>$scientists)));
}else{
print(json_encode(array("code" => 0, "message"=>"Data Not Found")));
}
$con->close();
}else{
print(json_encode(array('code' =>3,
'message' => 'ERROR: PHP WAS UNABLE TO CONNECT TO MYSQL DUE TO NULL CONNECTION.')));
}
}
/**
* This method will handle our HTTP Requests.
* Basically it checks the request type and then determines the method that needs to be
* executed. It's kind of a controller.
*/
public function handleRequest() {
if($_SERVER['REQUEST_METHOD'] == 'POST')
{
if (isset($_POST['action'])) {
$action=$_POST['action'];
if($action == 'INSERT'){
$this->insert();
}else if($action == 'UPDATE'){
$this->update();
}else if($action == 'DELETE'){
$this->delete();
}else if($action == 'GET_PAGINATED'){
$this->search();
}else if($action == 'GET_PAGINATED_SEARCH'){
$this->search();
}else{
print(json_encode(array('code' =>4, 'message' => 'INVALID REQUEST.')));
}
} else{
print(json_encode(array('code' =>5, 'message' => 'POST TYPE UNKNOWN.')));
}
}else{
$this->select();
}
}
}
//Outside the class. Instantiate Scientist then invoke the handleRequest() to listen to our requests.
$s=new Scientists();
$s->handleRequest();
//end

That’s it. Now move over to the next lesson.

Leave a Reply

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

WordPress › Error

The site is experiencing technical difficulties.