Your First Application using MySQL and PHP

Now that Myflixdb, what's next?

Congratulations for your success completion of the SQL tutorial series.

We looked at how we can design a database from scratch, the relational database management system model among others, MySQL as a database management system and how it implements the SQL language, what tools are available for us to use in the database design phase, SQL development and MySQL server administration such as MySQL workbench and finally produced a working database for a fictional video library system "Myflix".

Excited as you might be with your newly acquired knowledge, have you given any thoughts' as to how your database uses will use your database?

How about developing an application that hides all the SQL statements inside and provide a visual graphical user interface for the users to use when interacting with the database?

We can use PHP for this...

What is PHP?

PHP is a general purpose server side scripting language that we can use to develop dynamic web sites and applications. PHP only needs to be installed on the web server that will host the web application and client applications can access the server resources via web browsers. The diagram shown below illustrates the basic architecture of a PHP web application.

Why PHP?

You have obviously head of a number of programming languages out there; you may be wondering why we would want to use PHP over other languages to develop our video library application? PHP just like MySQL is open source and free, you don't need to pay for you to get PHP; all you have to do is download it. Packages such as XAMPP come bundled already with a web server, MySQL and PHP among others. This is also unbelievably free. This makes PHP a cost effective choice compared to other scripting languages such as CFML or ASP.

Other benefit that you get with PHP is that it's a server side scripting language; this means you only need to install it on the server and client computers requesting for the resources from the server do not need to have PHP installed; only a web browser would be enough.

PHP also has in built support for working hand in hand with MySQL; this doesn't mean you can't use PHP with other database management systems.

PHP is cross platform which means you can deploy your application on a number of different operating systems such as windows, Linux, Mac OS etc.

PHP and MySQL

PHP and MySQL are like two different sides of the same coin. Just like MySQL has built in functions for data manipulations, so does PHP has built in functions for connecting to MySQL server and manipulating the data in the database. Let's now look at some of PHP functions that allow us to manipulate MySQL databases

MySQL connect

$dh = mysql_connect(servername,username,password);

HERE

• "mysql_connect" is the PHP built in function for connecting to MySQL database
• "servername" is the name of the server running MySQL server.
• "username" is the name of the user that we will use to authenticate ourselves when connecting to the server.
• "password" is the password that we will use to authenticate ourselves when connecting to the server.

PHP Data Object [PDO]

Alternatively we can also use the PHP Data Object PDO to connect to MySQL server. It has the following basic syntax.

$dbconn = new PDO(mysql:host=servername;dbname=databasename,username,password);

HERE

• "dbconn" is the database connection object that we can use to manipulate our database.
• "new PDO (...)" creates an instance of the PDO class.
• "mysql:host=servername;" is the name of the server running MySQL server.
• "dbname=databasename" is the name of the database to be manipulated from PHP.
• "username,password" are login credentials we will use to authenticate ourselves.

As you can see from the above database connection examples, not only is PHP simple to use but it's also flexible. The diagram shown below illustrates how the client application, PHP powered web server and MySQL interact.

Introducing Myflix Video Library Application

Assuming we have acquired knowledge of the PHP language combined with what we just learnt in the MySQL tutorial series, we can write the web based application in PHP that runs on top of the MySQL database to provide our Myflix database users with a Visual Graphical User Interface for interactions.

Myflix Video Library Screenshots

The screenshots below show how the video library application running on top of Myflixdb would look like.

Members listing window shown above

Movie details editing window shown above

Summary

• MySQL is a powerful open source database engine that can meet the requirements of both stand-alone as well as client server applications.
• PHP is a general purpose scripting language that can be used to develop client applications that run on top of MySQL databases.
• Equipping yourself with both MySQL and PHP knowledge is double blessing.