PHP MySQL Web Application Example (First Project)

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 thought as to how your database users will use your database?

How about developing a simple web application using PHP and MySQL that hides all the SQL statements inside and provides a visual graphical user interface for the users to use when interacting with the database?

We can use PHP to create sample PHP application with MySQL.

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.

PHP web application architecture

Why PHP?

You have obviously heard 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

// Procedural connection with the MySQLi extension
$dh = mysqli_connect("servername", "username", "password", "databasename");

HERE

• “mysqli_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.
• “databasename” is the database to select.

⚠️ Warning: the older mysql_connect function was deprecated in PHP 5.5 and removed in PHP 7.0. Use mysqli or PDO instead.

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 PHP sample application database connection examples, not only is PHP simple to use but it’s also flexible. The PHP web application example diagram shown below illustrates how the client application, PHP powered web server and MySQL interact.

Interaction between PHP and MySQL

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 of PHP MySQL example below show how the video library application running on top of Myflixdb would look like.

Members listing window shown in the above PHP with MySQL project. Each row is a record read from the members table.

Movie details editing window shown above.

FAQs