HOME / Docs & Support

Lesson 4: Optimizing the Code

Contributed by Barbara Mityashina
June 27, 2008

Application Source Code from the Previous Lesson
Lesson Scope
Creating the db.php File
Creating the WishDB Class
Functions in the WishDB Class
Refactoring Your Application Code
- Refactoring the wishlist.php File
- Refactoring the createNewWisher.php File
Learn More - Useful Links
Application Source Code after the Current Lesson Is Completed

Application Source Code from the Previous Lesson

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

Lessong Scope

In this lesson you will optimize the code to facilitate maintaining it in the future. This will affect the files createNewWisher.php and wishlist.php. Additionally, a new file db.php will be 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 WishDB, and place it in a separate file db.php. Afterwards you can include the db.php file in any PHP file and use any function from WishDB without code duplication. Such 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.

Creating the db.php File

Place the db.php file in a separate folder Includes. Later you can add more Include files there.

Click the right mouse button on the Source files node and choose New > Folder from the context menu.
On the New Folder panel, in the Folder Name edit box enter Includes and click Finish.

To create a db.php file:

Click the right mouse button on the Includes node and choose New > PHP File from the context menu.
On the New PHP File panel, enter db in the File Name edit box and 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.

Open the file db.php and type the following code in it:

<?php
  class WishDB {

	   var $user = "phpuser";
    var $pass = "!phpuser";
    var $dbName = "wishlist";
    var $dbHost = "localhost";
    var $con;

    function WishDB() {
		        $this->con = mysql_connect($this->dbHost, $this->user, $this->pass)
		        or die ("Could not connect to db: " . mysql_error());
		        mysql_select_db($this->dbName, $this->con)
		        or die ("Could not select db: " . mysql_error());
	          }
?>

The variables are intended for storing the name and password of the database owner (user), the name of the database, and the database host.

The function WishDB is the constructor of the class WishDB. It is called every time any function of the class is called. The function establishes connection to the wishlist database.

Functions in the WishDB Class

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

get_wisher_id_by_name for retrieving the id of a wisher based on the wisher's name
get_wishes_by_wisher_id for retrieving a list of wishes of the wisher with a specific id
create_wisher for adding a new wisher record to the table wishers

Function get_wisher_id_by_name

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

Enter the following code block:

function get_wisher_id_by_name ($name) {
        $result = mysql_query("SELECT id FROM wishers WHERE name = '" 
                              . $name . "'");
        if (mysql_num_rows($result) > 0)
           return mysql_result($result, 0);  
        else
           return null;
}

The code block executes the query "SELECT ID FROM wishers WHERE Name = '" . $name . "'", where $name is the name of the wisher. The query result is an array of id's 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.

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:

function get_wishes_by_wisher_id($id) {
        return mysql_query("SELECT * FROM wishes WHERE wisher_id=" . $id);
}

The code block executes the query "SELECT * FROM wishes WHERE wisher_id=" . $id and returns a resultset which is an array of records that meet the query. The selection is performed by the wisher_id, which is the foreign key for the table wishes.

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:

function create_wisher ($name, $password){
       mysql_query("INSERT INTO wishers (name, password) VALUES ('" . $name . "', '" . $password . "')");
}

The code block executes the query "INSERT wishers (Name, Password) VALUES ('".$name."', '".$password."')", where $name is the name of the new wisher and $password is the wisher's password. The query adds a new record to the table wishers 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.

At the top of the <?php ?> block, enter the following line to enable using the db.php file:
```
require_once("Includes/db.php");
```
Create a $db object of the WishDB class:
```
$db = new WishDB;
```
The object exists as long as the current page is being processed is destroyed after the processing is completed or interrupted.
Replace the following code block:
```
$con = mysql_connect("localhost", "phpuser", "!phpuser");
        if (!$con) {
        die('Could not connect: ' . mysql_error());
        }
        mysql_select_db("wishlist1", $con);
        $wisher = mysql_query("SELECT ID FROM wishers WHERE Name='".$_GET["user"]."'");
        $wisherID = mysql_result($wisher, 0);
```
with a call of the function get_wisher_id_by_name function:
```
$wisherID = $db->get_wisher_id_by_name($_GET["user"]);
```
The code for opening a connection to the database with the name and password of its owner is no longer necessary here. The connection is opened by the WishDB function, which the constructor of the WishDB class. If the name and/or password changes, you will need to update only the relevant variables of the WishDB class.
Remove the following line:
```
 mysql_close($con);
```
The code is not necessary because the connection to the database is automatically closed as soon as the $db object is destroyed.

Replace the following code:

$result = mysql_query("SELECT * FROM wishes WHERE Wisherid=". $wisherID);

with a call of the function get_wishes_by_wisher_id:

$result = $db->get_wishes_by_wisher_id($wisherID);

Refactoring the createNewWisher.php File

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

At the top of the <?php?> block, enter the following code to enable using the db.php file:
```
require_once("Includes/db.php");
```
Create a $db object of the WishDB class:
```
$db = new WishDB;
```
The object exists as long as the current page is being processed is destroyed after the processing is completed or interrupted.

Replace the following code block:

$con = mysql_connect("localhost", "phpuser", "!phpuser");
    if (!$con) {
        die('Could not connect: ' . mysql_error());
    }

    mysql_select_db("wishlist1", $con);
    $wisher = mysql_query("SELECT ID FROM wishers WHERE Name='".$_POST["user"]."'");
    $wisherID = mysql_result($wisher, 0);
    if ($wisherID) {
        $userNameIsUnique = false;
    }
    mysql_close($con);

with a call of the get_wisher_id_by_name function:

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

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 $db object is destroyed.

Replace the following code block:

if (!$userIsEmpty && $userNameIsUnique && !$passwordIsEmpty && !$password2IsEmpty && $passwordIsValid) {
        $con = mysql_connect("localhost", "phpuser", "!phpuser");
        if (!$con) {
            die('Could not connect: ' . mysql_error());
        }

        mysql_select_db("wishlist1", $con);
        mysql_query("INSERT wishers (Name, Password) VALUES ('".$_POST["user"]."', '".$_POST["password"]."')");

with:

if (!$userIsEmpty && $userNameIsUnique && !$passwordIsEmpty && !$password2IsEmpty && $passwordIsValid) {
        $db->create_wisher($_POST["user"], $_POST["password"]);