// 
//     Licensed to the Apache Software Foundation (ASF) under one
//     or more contributor license agreements.  See the NOTICE file
//     distributed with this work for additional information
//     regarding copyright ownership.  The ASF licenses this file
//     to you under the Apache License, Version 2.0 (the
//     "License"); you may not use this file except in compliance
//     with the License.  You may obtain a copy of the License at
// 
//       http://www.apache.org/licenses/LICENSE-2.0
// 
//     Unless required by applicable law or agreed to in writing,
//     software distributed under the License is distributed on an
//     "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
//     KIND, either express or implied.  See the License for the
//     specific language governing permissions and limitations
//     under the License.
//

:jbake-type: tutorial
:jbake-tags: tutorials 
:jbake-status: published
:syntax: true
:icons: font
:source-highlighter: pygments
:toc: left
:toc-title:
:description: Creating a Database Driven Application With PHP - Apache NetBeans
:keywords: Apache NetBeans, Tutorials, Creating a Database Driven Application With PHP

= Lesson 4: Optimizing the Code with Classes and Objects
:jbake-type: tutorial
:jbake-tags: tutorials 
:jbake-status: published
:syntax: true
:icons: font
:source-highlighter: pygments
:toc: left
:toc-title:
:description: Lesson 4: Optimizing the Code with Classes and Objects - Apache NetBeans
:keywords: Apache NetBeans, Tutorials, Lesson 4: Optimizing the Code with Classes and Objects

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 <<includedFunctions,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 existence at the same time, and in other cases you may prefer a "singleton" class, where you only have one instance in existence 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 link:http://us3.php.net/zend-engine-2.php[+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.

[[previousLessonSourceCode]]
== Application Source Code from the Previous Lesson

MySQL users: Click link:https://netbeans.org/files/documents/4/1929/lesson3.zip[+here+] to download the source code that reflects the project state after the previous lesson is completed.

Oracle Database users: Click link:https://netbeans.org/projects/www/downloads/download/php%252Foracle-lesson3.zip[+here+] to download the source code that reflects the project state after the previous lesson is completed.

[[createDbPhpFile]]
== 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.


[[wishDBClass]]
== 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 link:http://us3.php.net/manual/en/language.oop5.visibility.php[+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:*

[source,php]
----

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:*

[source,php]
----

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;

}

----

[[instantiate-wishdb]]
== 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 link:http://www.phpclasses.org/browse/package/1151.html[+singleton class+], meaning that only one instance of the class is in existence 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:

[source,php]
----

// 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 link:http://us3.php.net/manual/en/language.oop5.paamayim-nekudotayim.php[+php.net on the Scope Resolution Operator+].


[[wishdb-constructor]]
== 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:*


[source,php]
----

// 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:*


[source,php]
----

// 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.

[[includedFunctions]]
== Functions in the WishDB Class

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

* <<getIDByName,get_wisher_id_by_name>> for retrieving the id of a wisher based on the wisher's name
* <<getWishesByID,get_wishes_by_wisher_id>> for retrieving a list of wishes of the wisher with a specific id
* <<createWisher,create_wisher>> for adding a new wisher record to the table wishers

[[getIDByName]]
=== 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:*


[source,php]
----

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:*


[source,php]
----

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 link:http://en.wikipedia.org/wiki/SQL_injection[+Wikipedia on SQL injections+] and the link:http://us3.php.net/mysql_real_escape_string[+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.


[[getWishesByID]]
=== 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:*

[source,php]
----

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:*

[source,php]
----

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.

[[createWisher]]
=== 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:*

[source,php]
----

public function create_wisher ($name, $password) {

  $name = $this->real_escape_string($name);
  $password = $this->real_escape_string($password);

  return $this->query("INSERT INTO wishers (name, password) VALUES ('" . $name . "', '" . $password . "')");
}

----

*For the Oracle database:*

[source,php]
----

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);

  return $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]]
== 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.


[[refactoringWishlistFile]]
=== 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:

[source,php]
----

require_once("Includes/db.php");

----

[start=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:

[source,php]
----

// to remove

 $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);

// to replace

$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:

[source,php]
----

// to remove

$con = oci_connect("phpuser", "phpuserpw", "localhost/XE");
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']; 

// to replace

$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.


[start=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:

[source,php]
----

// to remove

$result = mysqli_query($con, "SELECT description, due_date FROM wishes WHERE wisher_id=" . $wisherID);

// to replace      
 
$result = WishDB::getInstance()->get_wishes_by_wisher_id($wisherID);

----

For the *Oracle database*, the code you replace is:

[source,php]
----

// to remove

$query = "SELECT description, due_date FROM wishes WHERE wisher_id = :id_bv";
$stid = oci_parse($con, $query);
oci_bind_by_name($stid, ":id_bv", $wisherID);
oci_execute($stid);

// to replace

$stid = WishDB::getInstance()->get_wishes_by_wisher_id($wisherID);

----


[start=4]
. Remove the line that closes the database connection.

[source,php]
----

// For MYSQL database
mysqli_close($con);

// For Oracle database
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.


[[refactoringCreateNewWisher]]
=== 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:

[source,php]
----

require_once("Includes/db.php");

----


[start=2]
. Delete the database connection credentials ( `$dbHost,`  etc). These are now in  `db.php` .

[start=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:

[source,php]
----

// to remove

$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;
}

// to replace

$wisherID = WishDB::getInstance()->get_wisher_id_by_name($_POST["user"]);

if ($wisherID) {
  $userNameIsUnique = false;
}

----

For the *Oracle database*, the code you replace is:

[source,php]
----

// to remove

$con = oci_connect("phpuser", "phpuserpw", "localhost/XE", "AL32UTF8");
if (!$con) {
  $m = oci_error();
  exit('Connect Error ' . $m['message']);
}
$query = "SELECT id FROM wishers WHERE name = :user_bv";
$stid = oci_parse($con, $query);
$user = $_POST['user'];

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) {
  $userNameIsUnique = false;
}

// to replace

$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.

[start=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:

[source,php]
----

// to remove

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;
}

// to replace

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:

[source,php]
----

// to remove

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_free_statement($stid);
  oci_close($con);
  header('Location: editWishList.php');
  exit;
}

// to replace

if (!$userIsEmpty && $userNameIsUnique && !$passwordIsEmpty && !$password2IsEmpty && $passwordIsValid) {

  WishDB::getInstance()->create_wisher($_POST["user"], $_POST["password"]);

  header('Location: editWishList.php' );
  exit;
}

----

[[lessonResultSourceCode]]
== Application Source Code after the Current Lesson Is Completed

MySQL users: Click link:https://netbeans.org/projects/www/downloads/download/php%252Flesson4.zip[+here+] to download the source code that reflects the project state after the lesson is completed.

Oracle Database users: Click link:https://netbeans.org/projects/www/downloads/download/php%252Foracle-lesson4.zip[+here+] to download the source code that reflects the project state after the lesson is completed.


== Next Steps

link:wish-list-lesson3.html[+<< Previous lesson+]

link:wish-list-lesson5.html[+Next lesson >>+]

link:wish-list-tutorial-main-page.html[+Back to the Tutorial main page+]