Creating a Database Driven Application With PHP

Lesson 4: Optimizing the Code with Classes and Objects

Content on this page applies to NetBeans IDE 7.2, 7.3, 7.4 and 8.0

In this lesson you optimize the code to facilitate maintaining it in the future. This affects the files createNewWisher.php and wishlist.php. Additionally, a new file called db.php is created.

Your application's code contains several similar blocks with queries to the database. To make the code easier to read and maintain in the future, you can extract these blocks, implement them as functions of a separate class called WishDB, and place WishDB in db.php. Afterwards you can include the db.php file in any PHP file and use any function from WishDB without code duplication. Such an approach ensures that any changes to queries or functions will be made in one place and you will not have to parse the entire application code.

When you use a function from WishDB, you do not change the value of any of WishDB's variables. Instead, you use the WishDB class as a blueprint for creating an object of WishDB, and you change the values of variables in that object. When you finish working with that object, it is destroyed. Because the values of the WishDB class itself are never changed, you can reuse the class an unlimited number of times. In some cases you may want to have multiple instances of a class in existance at the same time, and in other cases you may prefer a "singleton" class, where you only have one instance in existance at any one time. WishDB in this tutorial is a singleton class.

Note that the term for creating an object of a class is "instantiating" that class, and that another word for an object is an "instance" of a class. The general term for programming with classes and objects is "object-oriented programming," or OOP. PHP 5 uses a sophisticated OOP model. See php.net for more information.

In this tutorial, you move the database call functionality from individual PHP files to the WishDB class. Users of MySQL also replace the procedural-style mysqli calls with object-oriented calls. This is in keeping with new, object-oriented design of the application

The current document is a part of the Creating a CRUD Application in the NetBeans IDE for PHP tutorial.


Application Source Code from the Previous Lesson

MySQL users: Click here to download the source code that reflects the project state after the previous lesson is completed.

Oracle Database users: Click here to download the source code that reflects the project state after the previous lesson is completed.

Creating the db.php File

Create a new subfolder in the Source Files folder. Name the folder Includes. Create a new file named db.php and place it in Includes. Later you can add more files to this folder that will be included in other PHP files.

To create db.php in a new folder:

  1. Click the right mouse button on the Source files node and choose New > Folder from the context menu. The New Folder dialog opens.
  2. In the Folder Name field, type Includes. Then click Finish.
  3. Click the right mouse button on the Includes node and choose New > PHP File from the context menu. The New PHP File dialog opens.
  4. In the File Name field, type db. Then click Finish.

Creating the WishDB Class

To create the WishDB class, you need to initialize the variables of the class and implement a constructor of the class. MySQL users please note that the WishDB class extends mysqli. This means that WishDB inherits the functions and other characteristics of the PHP mysqli class. You will see the importance of this when you add mysqli functions to the class.

Open the file db.php and create the WishDB class. In the class, declare database configuration variables for storing the name and password of the database owner (user), the name of the database, and the database host. All these variable declarations are "private," meaning that the initial values in the declarations cannot be accessed from outside the WishDB class (See php.net). You also declare the private static $instance variable, which stores the instance of WishDB. The "static" keyword means that functions in the class can access the variable even when there is no instance of the class.

For MySQL Database:

class WishDB extends mysqli {


    // single instance of self shared among all instances
    private static $instance = null;


    // db connection config vars
    private $user = "phpuser";
    private $pass = "phpuserpw";
    private $dbName = "wishlist";
    private $dbHost = "localhost";
}

For Oracle Database:

class WishDB {

// single instance of self shared among all instances private static $instance = null;

// db connection config vars private $user = "phpuser"; private $pass = "phpuserpw"; private $dbName = "wishlist"; private $dbHost = "localhost/XE"; private $con = null;

}

Instantiating the WishDB class

For other PHP files to use functions in the WishDB class, these PHP files need to call a function that creates an object of ("instantiates") the WishDB class. WishDB is designed as a singleton class, meaning that only one instance of the class is in existance at any one time. It is therefore useful to prevent any external instantiation of WishDB, which could create duplicate instances.

Inside the WishDB class, type or paste the following code:

 //This method must be static, and must return an instance of the object if the object
 //does not already exist.
 public static function getInstance() {
   if (!self::$instance instanceof self) {
     self::$instance = new self;
   }
   return self::$instance;
 }

 // The clone and wakeup methods prevents external instantiation of copies of the Singleton class,
 // thus eliminating the possibility of duplicate objects.
 public function __clone() {
   trigger_error('Clone is not allowed.', E_USER_ERROR);
 }
 public function __wakeup() {
   trigger_error('Deserializing is not allowed.', E_USER_ERROR);
 }

The getInstance function is "public" and "static." "Public" means that it can be freely accessed from outside the class. "Static" means that the function is available even when the class has not been instantiated. As the getInstance function is called to instantiate the class, it must be static. Note that this function accesses the static $instance variable and sets its value as the instance of the class.

The double-colon (::), called the Scope Resolution Operator, and the self keyword are used to access static functions. Self is used from within the class definition to refer to the class itself. When the double-colon is used from outside the class definition, the name of the class is used instead of self. See php.net on the Scope Resolution Operator.

Adding a Constructor to the WishDB Class

A class can contain a special method known as a 'constructor' which is automatically processed whenever an instance of that class is created. In this tutorial, you add a constructor to WishDB that connects to the database whenever WishDB is instantiated.

Add the following code to WishDB:

For the MySQL database:

// private constructor
private function __construct() {
parent::__construct($this->dbHost, $this->user, $this->pass, $this->dbName);
if (mysqli_connect_error()) {
exit('Connect Error (' . mysqli_connect_errno() . ') '
. mysqli_connect_error());
}
parent::set_charset('utf-8');
}

For the Oracle database:

// private constructor
private function __construct() {
    $this->con = oci_connect($this->user, $this->pass, $this->dbHost);
    if (!$this->con) {
        $m = oci_error();
        echo $m['message'], "\n";
        exit;
    }
}

Note the use of the pseudovariable $this instead of the variables $con, $dbHost, $user, or $pass. The pseudovariable $this is used when a method is called from within an object context. It refers to the value of a variable within that object.

Functions in the WishDB Class

In this lesson you will implement the following functions of the WishDB class:

Function get_wisher_id_by_name

The function requires the name of a wisher as the input parameter and returns the wisher's id.

Type or paste the following function into the WishDB class, after the WishDB function:

For the MySQL database:

public function get_wisher_id_by_name($name) {
$name = $this->real_escape_string($name);
$wisher = $this->query("SELECT id FROM wishers WHERE name = '"
. $name . "'"); if ($wisher->num_rows > 0){
$row = $wisher->fetch_row();
return $row[0];
} else
return null; }

For the Oracle database:

public function get_wisher_id_by_name($name) {
    $query = "SELECT id FROM wishers WHERE name = :user_bv";
    $stid = oci_parse($this->con, $query);
    oci_bind_by_name($stid, ':user_bv', $name);
    oci_execute($stid);
//Because user is a unique value I only expect one row
    $row = oci_fetch_array($stid, OCI_ASSOC);
if ($row)
return $row["ID"];
else
return null; }
The code block executes the query SELECT ID FROM wishers WHERE name = [variable for name of the wisher]. The query result is an array of IDs from the records that meet the query. If the array is not empty this automatically means that it contains one element because the field name is specified as UNIQUE during the table creation. In this case the function returns the first element of the $result array (the element with the zero numbered). If the array is empty the function returns null.

Security Note: For the MySQL database, the $name string is escaped in order to prevent SQL injection attacks. See Wikipedia on SQL injections and the mysql_real_escape_string documentation. Although in the context of this tutorial you are not at risk of harmful SQL injections, it is best practice to escape strings in MySQL queries that would be at risk of such an attack. The Oracle database avoids this issue by using bind variables.

Function get_wishes_by_wisher_id

The function requires the id of a wisher as the input parameter and returns the wishes registered for the wisher.

Enter the following code block:

For the MySQL database:

public function get_wishes_by_wisher_id($wisherID) {
return $this->query("SELECT id, description, due_date FROM wishes WHERE wisher_id=" . $wisherID);
}

For the Oracle database:

public function get_wishes_by_wisher_id($wisherID) {
    $query = "SELECT id, description, due_date FROM wishes WHERE wisher_id = :id_bv";
    $stid = oci_parse($this->con, $query);
    oci_bind_by_name($stid, ":id_bv", $wisherID);
    oci_execute($stid);
    return $stid;
}

The code block executes the query "SELECT id, description, due_date FROM wishes WHERE wisherID=" . $wisherID and returns a resultset which is an array of records that meet the query. (The Oracle database uses a bind variable for database performance and security reasons.) The selection is performed by the wisherID, which is the foreign key for the wishes table.

Note: You do not need the id value until Lesson 7.

Function create_wisher

The function creates a new record in the wishers table. The function requires the name and password of a new wisher as the input parameters and does not return any data.

Enter the following code block:

For the MySQL database:

public function create_wisher ($name, $password){
    $name = $this->real_escape_string($name);
$password = $this->real_escape_string($password);
$this->query("INSERT INTO wishers (name, password) VALUES ('" . $name . "', '" . $password . "')"); }

For the Oracle database:

public function create_wisher($name, $password) {
    $query = "INSERT INTO wishers (name, password) VALUES (:user_bv, :pwd_bv)";
    $stid = oci_parse($this->con, $query);
    oci_bind_by_name($stid, ':user_bv', $name);
    oci_bind_by_name($stid, ':pwd_bv', $password);
    oci_execute($stid);
}
The code block executes the query "INSERT wishers (Name, Password) VALUES ([variables representing name and password of new wisher]). The query adds a new record to the "wishers" table with the fields "name" and "password" filled in with the values of $name and $password respectively.

Refactoring Your Application Code

Now that you have a separate class for working with the database, you can replace duplicated blocks with calls to the relevant functions from this class. This will help avoid misspelling and inconsistency in the future. Code optimization that does not affect the functionality is called refactoring.

Refactoring the wishlist.php File

Start with the wishlist.php file because it is short and the improvements will be more illustrative.
  1. At the top of the <?php ?> block, enter the following line to enable the use of the db.php file:
    require_once("Includes/db.php");
  2. Replace the code that connects to the database and gets the ID of the wisher with a call to the get_wisher_id_by_name function.

    For the MySQL database, the code you replace is:

    $con = mysqli_connect("localhost", "phpuser", "phpuserpw");
    if (!$con) {
        exit('Connect Error (' . mysqli_connect_errno() . ') '
                . mysqli_connect_error());
    }
    //set the default client character set 
    mysqli_set_charset($con, 'utf-8');
    
    mysqli_select_db($con, "wishlist");
    $user = mysqli_real_escape_string($con, $_GET['user']);
    $wisher = mysqli_query($con, "SELECT id FROM wishers WHERE name='" . $user . "'");
    if (mysqli_num_rows($wisher) < 1) {
        exit("The person " . $_GET['user'] . " is not found. Please check the spelling and try again");
    }
    $row = mysqli_fetch_row($wisher);
    $wisherID = $row[0]; mysqli_free_result($wisher);


    $wisherID = WishDB::getInstance()->get_wisher_id_by_name($_GET["user"]); if (!$wisherID) { exit("The person " .$_GET["user"]. " is not found. Please check the spelling and try again" ); }

    For the Oracle database, the code you replace is:

    $con = oci_connect("phpuser", "phpuserpw", "localhost/XE", "AL32UTF8");
    if (!$con) {
       $m = oci_error();
       echo $m['message'], "\n";
       exit;
    }
    $query = "SELECT id FROM wishers WHERE name = :user_bv";
    $stid = oci_parse($con, $query);
    $user = $_GET["user"];
    
    oci_bind_by_name($stid, ':user_bv', $user);
    oci_execute($stid);
    
    //Because user is a unique value I only expect one row
    $row = oci_fetch_array($stid, OCI_ASSOC); if (!$row) { echo("The person " . $user . " is not found. Please check the spelling and try again" );
    exit;
    } $wisherID = $row["ID"];

    $wisherID = WishDB::getInstance()->get_wisher_id_by_name($_GET["user"]); if (!$wisherID) { exit("The person " .$_GET["user"]. " is not found. Please check the spelling and try again" ); }

    The new code first calls the getInstance function in WishDB. The getInstance function returns an instance of WishDB, and the code calls the get_wisher_id_by_name function within that instance. If the requested wisher is not found in the database, the code kills the process and displays an error message.

    No code is necessary here for opening a connection to the database. The connection is opened by the constructor of the WishDB class. If the name and/or password changes, you need to update only the relevant variables of the WishDB class.

  3. Replace the code that gets wishes for a wisher identified by ID with code that calls the get_wishes_by_wisher_id function.

    For the MySQL database, the code you replace is:

    $result = mysqli_query($con, "SELECT description, due_date FROM wishes WHERE wisher_id=". $wisherID);
                    
    $result = WishDB::getInstance()->get_wishes_by_wisher_id($wisherID);

    For the Oracle database, the code you replace is:

    $query = "select * from wishes where wisher_id = :id_bv";
    $stid = oci_parse($con, $query);
    oci_bind_by_name($stid, ":id_bv", $wisherID);
    oci_execute($stid);
    $stid = WishDB::getInstance()->get_wishes_by_wisher_id($wisherID);
  4. Remove the line that closes the database connection.
     mysqli_close($con);
                        or
     oci_close($con);                
    The code is not necessary because the connection to the database is automatically closed when the WishDB object is destroyed. However, keep the code that frees the resource. You need to free all resources that use a connection to ensure that a connection is properly closed, even if you call a close function or destroy the instance with the database connection.

 

Refactoring the createNewWisher.php File

Refactoring will not affect the HTML input form or the code for displaying the related error messages.

  1. At the top of the <?php?> block, enter the following code to enable the use of the db.php file:
    require_once("Includes/db.php");
  2. Delete the database connection credentials ($dbHost, etc). These are now in db.php.
  3. Replace the code that connects to the database and gets the ID of the wisher with a call to the get_wisher_id_by_name function.

    For the MySQL database, the code you replace is:

    
    $con = mysqli_connect("localhost", "phpuser", "phpuserpw");
    if (!$con) {
        exit('Connect Error (' . mysqli_connect_errno() . ') '
                . mysqli_connect_error());
    }
    //set the default client character set 
    mysqli_set_charset($con, 'utf-8');
    
    
    
    /** Check whether a user whose name matches the "user" field already exists */ mysqli_select_db($con, "wishlist"); $user = mysqli_real_escape_string($con, $_POST['user']); $wisher = mysqli_query($con, "SELECT id FROM wishers WHERE name='".$user."'"); $wisherIDnum=mysqli_num_rows($wisher); if ($wisherIDnum) { $userNameIsUnique = false; }

    $wisherID = WishDB::getInstance()->get_wisher_id_by_name($_POST["user"]);
    if ($wisherID) {
    $userNameIsUnique = false;
    }

    For the Oracle database, the code you replace is:

    
    $con = oci_connect("phpuser", "phpuserpw", "localhost");
    if (!$con) {
        $m = oci_error();
        echo $m['message'], "\n";
        exit;
    }
    $query = "select ID from wishers where name = :user_bv";
    $stid = oci_parse($con, $query);
    $user = $_POST['user'];
    $wisherID = null;
    oci_bind_by_name($stid, ':user_bv', $user);
    oci_execute($stid);
    
    //Each user name should be unique. Check if the submitted user already exists.
    $row = oci_fetch_array($stid, OCI_ASSOC);
    if ($row) {
    $wisherID = $row["ID"];
    }
    if ($wisherID != null) {
    $userNameIsUnique = false;
    }
    $wisherID = WishDB::getInstance()->get_wisher_id_by_name($_POST["user"]);
    if ($wisherID) {
    $userNameIsUnique = false;
    }
    The WishDB object exists as long as the current page is being processed. It is destroyed after the processing is completed or interrupted. The code for opening a connection to the database is not necessary because this is done by the WishDB function. The code for closing the connection is not necessary because the connection is closed as soon as the WishDB object is destroyed.
  4. Replace the code that inserts new wishers into the database with code that calls the create_wisher function.

    For the MySQL database, the code you replace is:

    if (!$userIsEmpty && $userNameIsUnique && !$passwordIsEmpty && !$password2IsEmpty && $passwordIsValid) {
        $password = mysqli_real_escape_string($con, $_POST["password"]);
    mysqli_select_db($con, "wishlist");
    mysqli_query($con, "INSERT wishers (name, password) VALUES ('" . $user . "', '" . $password . "')");
    mysqli_free_result($wisher);
    mysqli_close($con);
    header('Location: editWishList.php');
    exit;
    }
    if (!$userIsEmpty && $userNameIsUnique && !$passwordIsEmpty && !$password2IsEmpty && $passwordIsValid) {
    WishDB::getInstance()->create_wisher($_POST["user"], $_POST["password"]);
    header('Location: editWishList.php' );
    exit;
    }

    For the Oracle database, the code you replace is:

    
    if (!$userIsEmpty && $userNameIsUnique && !$passwordIsEmpty && !$password2IsEmpty && $passwordIsValid) {
        $query = "INSERT INTO wishers (name, password) VALUES (:user_bv, :pwd_bv)";
        $stid = oci_parse($con, $query);
        $pwd = $_POST['password'];
        oci_bind_by_name($stid, ':user_bv', $user);
        oci_bind_by_name($stid, ':pwd_bv', $pwd);
        oci_execute($stid);
        oci_close($con);
        header('Location: editWishList.php');
        exit;
    }
    
    
    if (!$userIsEmpty && $userNameIsUnique && !$passwordIsEmpty && !$password2IsEmpty && $passwordIsValid) {
    WishDB::getInstance()->create_wisher($_POST["user"], $_POST["password"]);
    header('Location: editWishList.php' );
    exit;
    }

Application Source Code after the Current Lesson Is Completed

MySQL users: Click here to download the source code that reflects the project state after the lesson is completed.

Oracle Database users: Click here to download the source code that reflects the project state after the lesson is completed.

Next Steps

<< Previous lesson

Next lesson >>

Back to the Tutorial main page

Useful Links

Learn more about using classes in PHP:

Learn more about refactoring PHP code:



To send comments and suggestions, get support, and keep informed on the latest developments on the NetBeans IDE PHP development features, join the mailing list.

Back to the PHP Learning Trail

get support for the NetBeans

Support


By use of this website, you agree to the NetBeans Policies and Terms of Use. © 2013, Oracle Corporation and/or its affiliates. Sponsored by Oracle logo