Use PHP with MySQL

The main reason for many web developers to learn a scripting language like PHP is because of the ease with which it can interact with databases. In this post I will show you how to use PHP and the MySQL database to store information on the web and include it into your website. Before you read this tutorial you should have at least a basic knowledge of how to use PHP.


You might have read that using HTML you can create only static pages but using PHP, you can create dynamic pages. So where does PHP get this dynamic data. Mostly its from databases. You create a general template and fill it with data from the database. Now a days most of the sites you might encounter in the internet are heavily dependent on database.

Before we start, you need three things to run PHP scripts which access MySQL databases. Firstly, you will, of course, need a webserver. This can either be on your computer or on a web host. Any web server software should work with PHP and MySQL but the best option is Apache, which is free.
PHP also needs to be installed on the server. If it is not already installed you can install it (or ask your web host to install it). It can be downloaded from PHP.net and is also free.
Finally, you will also require MySQL. This is the actual database software. You can also use most other types of database (SQL, Oracle etc.) but as this is a PHP/MySQL tutorial I will deal just now with the MySQL database (although the commands used here will also work with SQL databases). As with the other software you need, MySQL is free and can be downloaded from the MySQL homepage.

To verify the installation of PHP and MySQL, there is a simple test. Open a text editor and type in the following:

<?
phpinfo();
?>

and save it as phpinfo.php
Now upload this to your server and go to it in your browser. If you have PHP installed you will see a page with all the details of your PHP installation on it. Next, scroll down through all this information. If you find a section about MySQL then you will know that MySQL is installed.

Now you have to create a database and table. For this, lets use the interface provided by PHPMyadmin. Most of the web host provides this facility. Its is highly interactive and easy to use. Create a database and give it some name. Now create a table in the database. You have to specify the name of each field and type of data that would go into it. Also you have to define a primary key for your table. So always make a practice to create the first field as ID, and make it the primary key. Also select auto increment for this. Once you have the table ready, lets move to the PHP section.

First of all, we need to create a connection to the database. For that, we require three values. The hostname, username and password. If you are using your computer as the server, the hostname will be localhost. Otherwise your webhost will give you the hostname. So lets move to the code :

$host = "localhost";
$username = "user";
$password = "pass";

$connection = mysql_connect($host, $username, $password);

The above code will create a connection to mysql. Now, we have to select the required database.

$database = "mydatabase";

mysql_select_db($database, $connection);

So now you have created a connection and also selected the database.
Now you can start writing queries.
First lets see an insert query. Suppose your table has two field name and phone, then lets see how to insert values to it.

$name = "John";
$phone = 123456;

$query = "INSERT INTO tablename (name, phone) VALUES ($name, $phone)";

mysql_query($query);

This will insert a new row into the table.

You can replace the $query statement with any other query supported by MySQL.
Lets also see how to select data from a table and display it. Lets use the same table structure above in this example also.

<table border="1">
<tr><th>Name</th><th>Phone</th></tr>
<?php
$query = "SELECT * FROM tablename";
$res = mysql_query($query);
$results = mysql_fetch_array($res);
foreach($results as $result) {
?>
<tr>
<td><?php echo $result['name']; ?></td>
<td><?php echo $result['phone']; ?></td>
</tr>
<?php
}
?>
</table>

The above code will create a table and fill it with all the records obtained from the database. Similarly you can write code for update and delete.