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.