Wednesday, August 31, 2011

JQuery basics

In this post I will explain the basics of JQuery, what it is and what it is used for.

JQuery is cross browser javascript library that is easy to learn and easy to use. It has two versions one is for production which is compressed (about 31KB) and other which is not compressed (about 230KB) and is for development. So when developing or learning you should use the development version and when you are publishing some website use the production version.
JQuery is written by several IT proffesionals whose main mission is to make JQuery the best javascript library for web.

You might wanna visit JQuery .
There are several other good javascript libraries like prototype, scriptaculous, Google Web Toolkint, and many others list of js libs. What makes JQuery so popular is that it is cross-browser, CSS3 compliant, easy to learn and to use. It has a wide variety of tools built on it like widgets, interactions, utilities, special effects.
For example implementing simple accordion with text data is no more than writing div tags and importing js files in your html.

I will be writing some tutorials on using JQuery with AJAX, implementing widgets like accordion, slider, progressbar etc. so make sure you have Firefox with firebug installed or maybe Chrome. About IE I haven't used it much so maybe I will make some tut on it in future.

Tuesday, August 30, 2011

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

Sunday, August 7, 2011

Installing PDO on Ubuntu

Requirements for this post:

Installed php 5.1+ engine, apache2 web server
installed Netbeans 7 or 6.9
Installed MySQL and MySQL WB.

If you havent already installed this visit my older posts and install whats missing.

So in this post I will explain what is PDO and how can be installed on Ubuntu/Windows.

PDO stands for PHP Data Objects. It is PECL extension for PHP 5.0 and ships with PHP 5.1 and newer versions of PHP. It is dependent on OO features of PHP so it will not work in older PHP versions.

It is data-access abstraction layer  which means that you can use same function for access and manipulation of db data no mather what type of db is behind. So depending on the type of the db behind, you use special PDO driver for that database.  Here is the official PDO docs PDO .

PDO is not dependent of other libraries and extensions so if you have PHP 5.0+ you are ready to go and install.

PDO is a PECL extension so you will need this package in order to install pdo php5-dev.

Open terminal and run this command:

$sudo apt-get install php5-dev

 No we will install PDO.

The easiest way to install on Ubuntu is to use these commands:

$ sudo apt-get install libmysqlclient15-dev
$ sudo pecl install pdo
$ sudo pecl install pdo_mysql
 
 I have PHP 5.3 so no need to edit the php.ini file. If you have other version 
you should edit the php.ini file and add these lines 
 
extension=pdo.so
extension=pdo_mysql.so
 
On PHP 5.3 php.ini is place in the /etc/php5/apache2 folder. 
On other PHP versions it should be in /etc/php5/cgi/php.ini or /etc/php/cli/php.ini.
 
So no open terminal and run this command 
 
$ sudo gedit /path/to/php.ini 
 
mine was 
 
$sudo gedit /etc/php5/apache2/php.ini
 
It will open text file. Find the Dynamic extensions section and add the lines
 
extension=pdo.so
extension=pdo_mysql.so 
 
Now save the file and close it. In terminal run 
 
$ sudo /etc/init.d/apache2 restart 
 
to restart the web server.   Now I will create a test project in Netbeans to test 
the installtion of PDO. 
In previous post I created new PHP project called HelloWorldPHP. If you haven't 
I will explain it again here. 
Open Netbeans and click file->new project. Choose new PHP application 
and click next. Add the name of the project HelloWorldPHP and place it in the 
sources folder /var/www/HelloWorldPHP. Click finish. This will open new 
project in the projects panel. 
 
Now, click right click on the  HelloWorldPHP node and click 
set as main project. Open the sources folder and double click the index.php file
It will open in the editor window. Now you will see a php segment 
 
<?php ?>
 
Add this line of code phpinfo(); 
 
Your page will look like 
 
<?php phpinfo(); ?>
 
 
Now open a browser and type this url: http://localhost/HelloWorldPHP/ 
or click run->run main project in Netbeans. You  should get the php info 
page with all settings. Now far below on the page you have to find PDO 
section and pdo_mysql section. If this is the case than you installed it succesfully. 
Otherwise check what's wrong and post a question. 
 
In the next post i will add some on installing PDO on Windows.