Database solutions for PHP

In this post I will explain the most popular and most used database engines capable of working with PHP.  They are many, and have different capabilities and architectures. Some of them are payable and expensive, some of them are open source. So depending on your needs you can use some of the great variety of database engines. No matter which one you use PHP has prebuilt drivers and PDO libraries for the all of them.

Here is a short list:
1. SQLite (open source)
2. MySQL (open source)
3. PostgreSQL (open source)
4. dbm-style databases (Oracle Berkeley DB)
5. Oracle Database (payable), Oracle XE (open source)
6. ODBC

1. SQLite

Although you might think that MySQL is most popular when working with PHP, you are quite wrong. The most popular db engine is SQLite. It is so popular because it is small, reliable and also embeded database engine. When you develop application that uses SQLite it is by default embeded inside the application that uses it, for example Firefox, Skype, mobile platforms like Symbian, iPhone etc. In the newest version of PHP SQLite is bundled with it so you don't have to install anything additional. It is fast and supports the SQL standard commands. It is simple because it is not structured like client-server and the code is open source so you can use it and modify it to your needs.

Some of the features of SQLite:

 - Dynamically typed: Given field can hold different types of data from one record to the next, much like a NoSQL database. This is very useful when working with PHP since it is loosely typed language. The drawback is that it is hard to maintain db integrity and it is not compatible with other db engines.

- Given database is stored in a single cross platform db file. So you can easily use the same db with other application that works with SQLite.

- It is easy to configure because there is no need of installation and no need to create users and add persmissions.

- You can call PHP commands from within an SQLite SQL query. So when working with some query you can call PHP function within the same query and it will surely work well.

MySQL is most known by PHP developers so I will jump right to 3.

3. PostgreSQL

PostgreSQL is also open source db engine much like MySQL. In fact the closest relative to MySQL is PostgreSQL. They both support the same capabilities and work at the same level of scalability. MySQL is known for its ease of use and great speed, while PostgreSQL is known as feature rich and reliable. But as the time passes both of them became powerful enough and surpassed their disabilities. You can access PostgreSQL with extension and also with PDO driver much like MySQL. So after this you can use both, as they are both good. It depends on your flavour which one to choose.

4. dbm-style databases (Oracle Berkeley DB)

 dbm-style database is an embeded type of db developed in 1970s. It is like SQLite but not nearly powerful. It stores files on the hard disk and it doesn't use SQL commands. But it can be very fast as records are taken with only a given key. This type of dbs aren't used very much today but the most popular of the existing is Oracle's Berkely DB.

5. Oracle Database and Oracle XE

Oracle is a company most well know by the database software systems that it develops. Oracle databases are used mainly in large organisations and companies that have a need of complex queries and store large amounts of data. Thus it is very expensive to use. So if you need small scale db engine for a low traffiic application than you should use the above given or Oracle's XE edition which is open source. For use with PHP you can use OCI8 extension or PDO driver.

6. ODBC

ODBC is not a database engine but it is an API interface for accessing and working with several db engines like MS Access, MS SQL server, and IBM's DB2 engine. MS Access and SQL Server are only available on Windows systems so this would be good solution if you work on Windows. As for DB2 there are AIX and, Unix, Linux and Mac as well as Windows.

Windows has preinstalled ODBC driver but there are open source drivers for other platforms like Linux, Unix and Mac OS X.

Ok so this would be all about PHP compliant db engines.

used literature from Beginning PHP 5.3 by Matt Doyle

Structure of the Sakila database

The sakila database contains 23 objects of which 7 are views and 16 are tables.

You can open MySQL workbench and choose the "Open Existing EER Model" for the sakila diagram option in the Data Modeling section. The EER diagram will show up. This database is for storing movies data.

So we have film table which contains film data and also

which language it is filmed in,
which list of actors have acted in it,
which category of film it is
and in which inventory it has samples.

Than we have customer table which stores data about the customer and also

from which country the customer is,
in which city and address he lives,
which file does he rented and when he needs to return the movie.

Also sakila stores data about payments and the online store.

Now there are 7 view which are:

1. sales by film category,
2. Sales by given store if we have several
3. Info about given actor,
4. customer list
5. films list
6. staff list
7. all films list, which is bigger and gives result slowly.

Now that is rough view of the sample sakila database. Hope that this is helpful.

You might wanna visit the sakila official page at Sakila

Basic PDO commands to access and manipulate MySQL database

Ok. Now that we have PDO and sakila sample database installed we can begin writing our first code of accesing and showing database data.

Open Netbeans and open the HelloWorldPHP project that we hava in early posts created. Open the index.php file in the editor. Now we need to create connection to MySQL if we want to use the sakila database.

first we write try, catch block and write out our connection strings:


$hostname = "localhost";
$usernameMySQL = "root";
$passwordMySQL = "root";


try{
     ////connection code
}catch(PDOException $exp){
     /////exception code
}

In my case username and pass to MySQL are root, root
Ok so the connection code would be this:

$pdo = new PDO("mysql:host=$hostname;dbname=sakila", $usernameMySQL, $passwordMySQL);
echo "I have succesfully connected to database";

We create $pdo object which gets three parameters: name of the host and type of database driver. In my case that is localhost and mysql. So it can be other host name if the database is hosted on remote server. For dbname we write sakila the name of our db.

The exception code would be this:

 echo $exp->getMessage();

Exception code writes the error message that was took when error occured while connecting.Try, catch block is useful in cases we want to redirect the user to an error page when some error occurs, in our case we would make error page and write some text in it to notify user that there is a problem. So now my index page looks lie this:
            
$hostname = "localhost";
$usernameMySQL = "root";
$passwordMySQL = "root";
         
try{
            $pdo = new PDO("mysql:host=$hostname;dbname=sakila", $usernameMySQL, $passwordMySQL);     
       echo "I have succesfully connected to database";
}catch(PDOException $exp){           
       echo $exp->getMessage();
}
         
Now that we have successfully connected to MySQL we need to prepare some SQL statements in order to use sakila db data. 

In the try block below the echo statement write this code:

$sql="select title from film";
$stmt=$pdo->prepare($sql);
$stmt->execute();
$filmTitles=$stmt->fetchAll();

We are creating sql statement to select only the title column of the film table.
After that we prepare the sql and return the result to the $stmt variable, short for statement.
Than we execute the sql on to the database.
In the last command we fetch  all table entries that the sql statement returns and put them in array $filmTitles

Below the code for the sql write this code for printing the fetched data:

echo "<table border='1'>";
echo "<tr><td>Film Title</td></tr>";
for ($i=0;$i<sizeof($filmTitles);$i++){
                echo "<tr><td>".($i+1).". ".$filmTitles[$i]['title']."</td></tr>";
}
echo "</table>";

No we parse trought the fetched data with for cycle. $filmTitles is an array of arrays with two elements i.e it is 1000x2 matrix. So we access each element with this code $filmTitles[$i]['title'] and we can see that the subarrays are associative. You can use print_r function to print the data in filmTitles and see whats behind the hood.


Try this in your php code after the for

print_r($filmTitles); 

 This is the basic setup for PDO data access to database. More tuts on this to come :)

Installing Sakila sample database

Ok, so in this post I will show you how to install Sakila sample database on our MySQL server by using Netbeans IDE. So make sure you install Netbeans and all other necessary software for this to work. You might wanna visit Installing Netbeans or LAMP Installation .

Ok so now open Netbeans 7 or other version if you have and got to tools->plugins .

Now new window opens. In the Available plugins tab find and select "sakila sample database". Cliks Install. Accept the terms of agreement and click next.

The sample database installs. Ok so now open the services tab in the left panel where Projects and files tabs reside. Click the databases tree. You will see sub tree called " MySQL Server as localhost:3306[root] ". Right click on the entry and click create database. In the dropdown list select Sample database sakila. It will ask you whether you want to install the sample database on MySQL. Click yes.

Wait a moment and the database is installed with its own data.

Now open MySQL workbench and double click on the mysql connection. New window opens. You should see the Sakila database in the left panel. Thats it. I will use this sample database to explain some advances topics of php and how to manipulate data on MySQL server.

You might wanna visit Sakila on Netbeans .

Quick description of the Sakila database:

It is successor of a previous sample database called World. So it is improved and enriched. It has several tables,views , triggers and stored procedures in order to begin learning advanced work with databases.