Tournas Dimitrios

PHP – Mysql tutorial for beginners June 20, 2010

In this PHP MySQL tutorial I will show you how to use basic database manipulation features in PHP.This tutorial assumes that your developing enviroment is ready to use (your local/remote server is installed , enviroment variables are set etc…)As you know by the help of PHP you can create dynamic website content. The dynamic content doesn’t necessary require a database, however in most cases you have/need one. All of the content management systems, blogs or only a simple form processor use database. In the PHP world the most commonly used database is MySQL. So in the next section I will focus on how to use PHP and MySQL to create dynamic content. In this tutorial I will focus only PHP MySQL usage basics, and I suppose you have already an installed and properly configured system supporting both PHP and MySQL.

Check your configuration:

To avoid any problem in the later steps you should check your actual configuration. To do this you need to create a simple Php file which calls the function phpinfo(). The file is really simple:

?php
phpinfo();
?>

If you open this site then you can check how your system is configured. The most important in this case the MySQL section of the output. It should look like something like this:

If you have no MySQL section then it means that MySQL access is not possible with the actual server settings. Ask your system administrator to configure it for you. (Later I plan to write a tutorial which guide you through the necessary steps.)

Step 1 – Creating a test database

In this tutorial I will use a MySQL database named test with a single table called users. Below you can find the sql code which creates the database, the table and inserts some default data.

To use any database in PHP – and in other programming languages as well – you first need to connect to the database server. Connecting to a MySQL database in PHP requires some information. You need to get the following data to establish the connection:

hostname

database username

password

database name

Connecting to a database first you need to connect to the MySQL database server and in the next step you need to select the required database. Of course if the connection to the server failed then it makes no sense to continue the script so we can exit. Let’s see how it looks like in PHP:

As you can see the mysql_connect() function establish the connection to the MySQL server and as next step we can select the database with mysql_select_db() function. If you execute the script the output should be the following:Connection to the server was successful! Database was selected!

Closing the connection

Before we go on I want to mention the opposite site as well. I mean closing a database connection. From PHP 4 you don’t really need to close a database connection opened the way above. This is done automatically by the PHP garbage collector. However it makes no problem if you add the closing statement to your code. You can close a MySQL database connection calling the function mysql_close(). This function closes the actual database connection.

Step 3 – Retrieving data from the database:

Now it’s time to make something more interesting work. For example let’s try to retrieve data from our table. To do this we first need to open a connection and select the relevant database as before. The next step is to send an SQL query – in this case a Select statement – to the database. Now try to get all records from MySQL. The SQL statement for this is:

SELECT * FROM users;

We need to send this command to the server and store the response. We can do this by using the mysql_query() function in this way:

Ok, this is nice but how to display data from the $result variable. If you try to print it with echo then you will get an output something like this:

Resource id #3

This is not what we want. To display the selected data correctly you need to do a bit more. There are more functions in PHP which you can use to retrieve data from a MySQL database result set. These are the followings:

mysql_fetch_assoc() – Fetch a result row as an associative array

mysql_fetch_row() – Fetch a result row as an enumerated array

mysql_fetch_array() – Fetch a result row as an associative array, a numeric array, or both

All of them converts one record of the result to an array and later you can use this array as you want. To get the array you need to call one of the above mentioned function. In this case I use the associative version.

However the function returns only with one record, but we should have 5 records in the result set. To list all of the records we need to create a loop. The fetch functions returns with an array if there is a record in the result set and returns false if no more records are available. So creating a while loop is very simple as here:

As you can see it is not a complicated task. In the next section we will insert data into the database.

Step 4 – Insert, update, delete records:

Inserting data into the database

In this topic we will create a code to insert new records into our MySQL database. This is very similar to the select version. Also in this case we need to build up a database connection and we will use the mysql_query() function again. However in this case we will use it with an insert sql statement. Now let’s try to insert a new user to our table. The sql statement is the following: