Category Archives: PHP

PHP, PHP 5.x

Create Read Update Delete pages in php

In this tutorial series, we will go through steps of a creating PHP CRUD grid. We want to demonstrate how PHP as a server side language, communicates with backend MySQL, and meanwhile renders front-end HTML. We hope you can learn something from this tutorial.

1. Creating a sample Database table

In this tutorial, we will work on a simple Database table as below. After this tutorial, you should use the idea here to create CRUD grid on your own Database tables.
Import the Database table below to your own MySQL Database.

CREATE TABLE  `customers` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 100 ) NOT NULL ,
`email` VARCHAR( 100 ) NOT NULL ,
`mobile` VARCHAR( 100 ) NOT NULL
) ENGINE = INNODB;

As you can see, this is a very simple table for tracking customers’ information (name, email address and mobile number). And we prefer an auto incremental primary key (id).

2. Connecting to Database

Create a PHP file “database.php”; this file contains a PHP class named “Database”. Throughout this application, Database handles all the stuff related to database connections, such as connecting and disconnecting.

<?php
class Database
{
    private static $dbName = 'crud_tutorial' ;
    private static $dbHost = 'localhost' ;
    private static $dbUsername = 'root';
    private static $dbUserPassword = 'root';

    private static $cont  = null;

    public function __construct() {
        die('Init function is not allowed');
    }

    public static function connect()
    {
       // One connection through whole application
       if ( null == self::$cont )
       {
        try
        {
          self::$cont =  new PDO( "mysql:host=".self::$dbHost.";"."dbname=".self::$dbName, self::$dbUsername, self::$dbUserPassword);
        }
        catch(PDOException $e)
        {
          die($e->getMessage());
        }
       }
       return self::$cont;
    }

    public static function disconnect()
    {
        self::$cont = null;
    }
}
?>

As you can see, we are using PDO for database access. There are a lot of benefits of using PDO. One of the most significant benefits is that it provides a uniform method of access to multiple databases.

To use this class, you will need to supply correct values for $dbName, $dbHost, $dbUsername, $dbUserPassword.

$dbName: Database name which you use to store ‘customers’ table.
$dbHost: Database host, this is normally “localhost”.
$dbUsername: Database username.
$dbUserPassword: Database user’s password.

Let us take a look at three functions of this class:

__construct(): This is the constructor of class Database. Since it is a static class, initialization of this class is not allowed. To prevent misuse of the class, we use a “die” function to remind users.
connect: This is the main function of this class. It uses singleton pattern to make sure only one PDO connection exist across the whole application. Since it is a static method. We use Database::connect() to create a connection.
disconnect: Disconnect from database. It simply sets connection to NULL. We need to call this function to close connection.

3. Create a Twitter Bootstrap powered grid

Here comes the grid without CRUD capabilities. Because CRUD operation can only be performed when there is a grid. We firstly need to build a grid. From there we can subsequently add “Create” page, “Read” page, “Update” page and finally “Delete” page.

Head to Bootstrap official page, and download a copy. In this tutorial, we are using version 2.3.2. After that, create a PHP file “index.php”.

php-crud-file-structure-1
Current file structure should look like below if you have followed the steps correctly:

Now open file “index.php”. And copy the content below:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <link   href="css/bootstrap.min.css" rel="stylesheet">
    <script src="js/bootstrap.min.js"></script>
</head>

<body>
    <div class="container">
            <div class="row">
                <h3>PHP CRUD Grid</h3>
            </div>
            <div class="row">
                <table class="table table-striped table-bordered">
                  <thead>
                    <tr>
                      <th>Name</th>
                      <th>Email Address</th>
                      <th>Mobile Number</th>
                    </tr>
                  </thead>
                  <tbody>
                  <?php
                   include 'database.php';
                   $pdo = Database::connect();
                   $sql = 'SELECT * FROM customers ORDER BY id DESC';
                   foreach ($pdo->query($sql) as $row) {
                            echo '<tr>';
                            echo '<td>'. $row['name'] . '</td>';
                            echo '<td>'. $row['email'] . '</td>';
                            echo '<td>'. $row['mobile'] . '</td>';
                            echo '</tr>';
                   }
                   Database::disconnect();
                  ?>
                  </tbody>
            </table>
        </div>
    </div> <!-- /container -->
  </body>
</html>

Let us dig into the codes.

part of this file is straightforward, we include Bootstrap’s CSS and JavaScript files.
Line 15 to 38 is the main part of the file. It is where we retrieve data from database and show it on the grid. Let dig deep into each lines carefully.
We firstly create a table with headers corresponding to database table “customers”‘s fields. Which includes “Name”, “Email Address”, “Mobile Number”:

<thead>
           <tr>
             <th>Name</th>
             <th>Email Address</th>
             <th>Mobile Number</th>
           </tr>
</thead>

Then we include “database.php”, create a PDO connection to database, and use a general “SELECT” statement to retrieve data. Lastly, we loop through each row to print content. Do not forget to close the connection as we mentioned at the beginning.

<?php
           include 'database.php';
           $pdo = Database::connect();
           $sql = 'SELECT * FROM customers ORDER BY id DESC';
           foreach ($pdo->query($sql) as $row) {
                    echo '<tr>';
                    echo '<td>'. $row['name'] . '</td>';
                    echo '<td>'. $row['email'] . '</td>';
                    echo '<td>'. $row['mobile'] . '</td>';
                    echo '</tr>';
           }
           Database::disconnect();
?>

If you have followed correctly, you should have an empty grid as below if you navigate to “index.php” from browser:

The grid is empty because there is no actual data inside “customers” table, to test if it is actually working, you can manually insert data into “customers” table. It should show them on the grid.

4. To be continued
Today we have understand the connection with database by PHP. Tomorrow we will understand next of things in PHP.

Thanks for taking interest in PHP