Database in an OOP Way - TOP10

Database in an OOP Way

 Besides regular improvements in the OOP, PHP5 also introduces many new libraries to seamlessly work with database in an OOP way. These libraries provide you with improved performance, sometimes improved security features, and of course a whole lot of methods to interact with new features provided by the database server. 

In this chapter we will discuss MySQL improved API, which is known as MySQLi. Take a look at basic PDO (well, not detailed because PDO is so huge that it is possible to write a whole book just on it), ADOdb, and PEAR::MDB2. In the mean time we will also take a look at Active Record pattern in PHP using ADOdb's active. One thing to note here is that we are not focusing on how to do general database manipulations. We will only focus on some specific topics which are interesting for PHP developers who are doing database programming in an OO way.

Introduction to MySQLi

MySQLi is an improved extension introduced in PHP5 to work with advanced MySQL features like prepared statements and stored procedures. From a performance point of view, MySQLi is much better than a MySQL extension. Also this extension offers completely object oriented interfaces to work with a MySQL database which was not available before PHP5. But keep in mind that if your MySQL version is at least 4.1.3 or above, you will get it working.  So what are the major improvements? Let's have a look first:  Improved performance over MySQL extensions Flexible OO and Non OO interface Advantage over new MySQL objects  Ability to create compressed connections  Ability to connect over SSL Support for Prepared Statements Support for Stored Procedure (SP) Support for better replication and transaction  
We will look into some of these features in the following examples. But of course we are not going for anything introductory to MySQL, because that is out of scope for this book. We will just show you how to use OO interface using MySQLi and how to use some of these advanced features.

Connecting to MySQL in an OO Way

Remember those old days when you had to use procedural function call to connect to MySQL, even from your objects. Those days are over. Now you can take advantage of complete OO interface of MySQLi to talk to MySQL (well, there are a few procedural methods, but overall it's completely OO). Take a look at the following example:  

<? $mysqli = new mysqli("localhost", "user", "password", "dbname"); if (mysqli_connect_errno()) {  echo("Failed to connect, the error message is : ".  mysqli_connect_error());  exit(); } ?>  
If the connection fails, you may get an error message like this: 
Failed to connect, the error message is : Access denied for user  'my_user'@'localhost' (using password: YES) 

Selecting Data in an OO Way

Let's see how to select data from a table in an OO way using MySQLi API.  <?php $mysqli = new mysqli("localhost", "un" "pwd", "db"); if (mysqli_connect_errno()) {  echo("Failed to connect, the error message is : ".  mysqli_connect_error());  exit();  } /* close connection */ $result = $mysqli->query("select * from users");  $mysqli->select * from users"); while ($data = $result->fetch_object()) { echo $data->name." : '".$data->pass."' \n"; } ?>  

The output is as following: 

robin : 'no password'  tipu : 'bolajabena'   

Please note that it is not good practice to store users' passwords in plain text in your database without encrypting them in some way. The best way is to store just the hash of their passwords using some hash routines like md5()   

Updating Data in an OO Way 
There is no special deal with it. You can update your data as you previously did with MySQL extension. But for the sake of OO style, we are showing an example of how you can do that with mysqli_query() function as shown in the above example. Instantiate an instance of MySQLi object and then run the query. 

Prepared Statements
Here we are in a really interesting section which has been introduced for the first time in PHP OO using MySQLi extension. The prepared statements are introduced in MySQL 5.0 versions (dynamic SQL) for better security and flexibility. It has a great performance boost over the regular one.  So what is actually a prepared statement? A prepared statement is nothing but a regular query that is pre-compiled by the MySQL sever that could be invoked later. Prepared statements reduce the chances of SQL injection and offers greater performance over the general non-prepared queries, as it need not perform different compilation steps at the run time.(It is already compiled, remember?) The following are advantages of using prepared statements: Better Performance Prevention of SQL injection Saving memory while handling blobs  But there are drawbacks too! There is no performance boost if you use prepared statements for a single call. There is no query cache for using prepared statements. Chance of memory leak if statements are not closed explicitly. Not all statements can be used as a prepared statement.  Prepared statements can accept parameters at run time in the same order you specify them whilst preparing the query. In this section we will learn about creating prepared statements, passing values to them, and fetching results. 

Basic Prepared Statements 

Let's prepare a statement using PHP's native MySQLi extension. In the following example we will make a prepared statement, execute it, and fetch the result from it: <? $mysqli = new mysqli("localhost", "un" "pwd", "db"); if (mysqli_connect_errno()) { echo("Failed to connect, the error message is : ". mysqli_connect_error()); exit(); 
} $stmt = $mysqli ->prepare("select name, pass from users order by name"); $stmt->execute(); //$name=null; $stmt->bind_result($name, $pass); while ($stmt->fetch()) { echo $name."<br/>"; } ?>  
So what did we actually do in the above example?  1. We prepared the statement using the following code:  $stmt = $mysqli->prepare("select name, pass from users order by name"); 2. Then we executed it:  $stmt->execute(); 3. Then we bound two variables with it, as there are two variables in our query:  $stmt->bind_result($name, $pass); 4. Finally we fetched the result using:  $stmt->fetch() Whenever we called fetch(), the bound variables are populated with values. So we can now use them.

Prepared Statements with Variables  

The advantage of prepared statements is that you can use variables with queries. First you can prepare the query by placing a ? sign at the appropriate place, and then you can pass the value after preparing it. Let's have a look at the following example: <? $mysqli = new mysqli("localhost", "un" "pwd", "db"); if (mysqli_connect_errno()) {  echo("Failed to connect, the error message is : ".  mysqli_connect_error());  exit(); } $stmt = $mysqli->prepare("select name, pass from users  where name=?"); $stmt->bind_param("s",$name); //binding name as string $name = "tipu"; $stmt->execute(); $name=null; $stmt->bind_result($name, $pass); while ($r = $stmt->fetch()) 
{  echo $pass."<br/>"; } ?>  
Here we prepare the query "select name, pass from users where name=?" where the name is definitely a string type value. As we bind parameters in the previous example for the result using bind_results(), here we have to bind parameters using bind_params() function. Besides that, we need to supply the data type of the parameters bound.  MySQL prepared statements support four types of parameters:  i, means the corresponding variable has type integer  d, means the corresponding variable has type double  s, means the corresponding variable has type string  b, means the corresponding variable is a blob and will be sent in packets As our parameter is a string, we used the following line to bind the parameter: 
$stmt->bind_param("s",$name); After binding the variable, now we set the value to $name and call the execute() function. After that we fetch the values as before. 

Using BLOB with Prepared Statements 

Prepared statements support handling BLOB or Binary Large Objects efficiently. If you manage BLOB with prepared statements, it will save you from greater memory consumption by sending the data as packets. Let's see how we can store BLOB (in this case, an image file).  Prepared statements support sending data in chunks using the send_long_data() function. In the following example we will store the image using this function, though you can send them as usual, unless your data exceeds the limit defined by the max_allowed_packet  MySQL configuration variable.   
<? $mysqli = new mysqli("localhost", "un" "pwd", "db"); if (mysqli_connect_errno()) {  echo("Failed to connect, the error message is : ".  mysqli_connect_error());  exit(); } $stmt = $mysqli->prepare("insert into images value(NULL,?)"); $stmt->bind_param("b",$image); $image = file_get_contents("signature.jpg");//fetching content of  //a file $stmt->send_long_data(0,$image); $stmt->execute(); 
Our table schema is as shown below: CREATE TABLE 'images' (  'id' int(11) NOT NULL auto_increment,  'image' mediumblob,  PRIMARYKEY('id') ) ENGINE=MyISAM; We choose medium BLOB as our data type because blob can store only 65KB of data, where as medium BLOB can store more than 16MB, and long blob can store more than 4GB data in it.  Now we will restore this BLOB data using the image again in prepared statement: <? $mysqli = new mysqli("localhost", "username", "password", "test"); if (mysqli_connect_errno()) {  echo("Failed to connect, the error message is : ".  mysqli_connect_error());  exit(); } $stmt = $mysqli->prepare("select image from images where id=?"); $stmt->bind_param("i",$id); $id = $_GET['id']; $stmt->execute(); $image=NULL; $stmt->bind_result($image); $stmt->fetch(); header("Content-type: image/jpeg"); echo $image; ?>  

Executing Stored Procedure with MySQLi and PHP

Stored procedure is another new addition to MySQL 5 which reduces the need for client-side queries to a great extent. Using MySQLi extension, you can execute stored procedures in MySQL. We are not going to discuss stored procedures as that is out of scope for this book. There are several articles available in the Internet that will help you in writing stored procedures in MySQL  Let's create a small stored procedure and run it using PHP. This stored procedure can take an input and insert that record in a table:  
DELIMITER $$; DROP PROCEDURE IF EXISTS 'test'.'sp_create_user'$$ CREATE PROCEDURE 'sp_create_user'(IN uname VARCHAR(50)) BEGIN INSERT INTO users(id,name) VALUES (null, uname);  END$$ DELIMITER ;$$ If you run this stored procedure in your database (using MySQL query builder or anything) the sp_create_user procedure will be created.   
You can manually execute any stored, procedure from MySQL client using "Execute" command. For example to execute the above stored procedure you have to use call sp_create_user('username').    
Now we will run this stored procedure using PHP code. Let's see. <? $mysqli = new mysqli("localhost", "username", "password", "test"); if (mysqli_connect_errno()) {  echo("Failed to connect, the error message is : ".  mysqli_connect_error());  exit(); } $mysqli->query("call sp_create_user('hasin')"); ?>    

Next Post »