jump to navigation

PHP – Mysql tutorial for beginners June 20, 2010

Posted by Tournas Dimitrios in Mysql, PHP.
trackback

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.

CREATE DATABASE if NOT EXISTS `test`;
USE test;
CREATE TABLE `users` (

`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) DEFAULT NULL,
`city` VARCHAR(100) DEFAULT NULL,
`web` VARCHAR(100) DEFAULT NULL,
`age` SMALLINT(6) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

INSERT INTO `users`(`id`,`name`,`city`,`web`,`age`) VALUES (1,'Mike','New York','www.mike.com',25);
INSERT INTO `users`(`id`,`name`,`city`,`web`,`age`) VALUES (2,'John','Dallas','www.john.com',37);
INSERT INTO `users`(`id`,`name`,`city`,`web`,`age`) VALUES (3,'Anna','London','www.anna.com',24);
INSERT INTO `users`(`id`,`name`,`city`,`web`,`age`) VALUES (4,'David','Oxford','www.david.com',19);
INSERT INTO `users`(`id`,`name`,`city`,`web`,`age`) VALUES (5,'Julia','New York','www.julia.com',20);

Step 2 – Connecting to MySQL:

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:


mysql_connect("localhost", "username", "password") or die(mysql_error());
echo "Connection to the server was successful!<br>";
mysql_select_db("test") or die(mysql_error());
echo "Database was selected!
";

?>

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:

 mysql_connect("localhost", "username", "password") or die(mysql_error());
      mysql_select_db("test") or die(mysql_error());
      $result = mysql_query("SELECT * FROM users");
        echo $result;

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.

$row = mysql_fetch_assoc($result);
       echo "ID: ".$row['id'].", Name:".$row['name']
        .", City:".$row['city'].", Age:".$row['age']."
";

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:

$result = mysql_query("SELECT * FROM users");
while($row = mysql_fetch_assoc($result)){
echo "ID: ".$row['id'].", Name:".$row['name']
.", City:".$row['city'].", Age:".$row['age']."<br>";
	}

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:

INSERT INTO users (name,city,web,age) VALUES ('Tom','Vegas','www.tom.com',44);.

Now store this SQL statements in a variable and pass this as parameter of mysql_query like this:

$sql = "INSERT INTO users (name,city,web,age) VALUES ('Tom','Vegas','www.tom.com',5)";
$result = mysql_query($sql);

Besides this we need to check the result and inform the user if an error was occurred. A complete insert code is the following:

Updating a record
The update is almost the same as the insert. You only need to change the sql statement and use it as before:

$sql = "UPDATE users SET age=45 WHERE name='Tom'";
$result = mysql_query($sql);

Deleting a record
As you may know it is again the same as before. Only the sql needs to be changed like this:

 $sql = "DELETE FROM users WHERE name='Tom'";
      $result = mysql_query($sql);

In this PHP MySQL tutorial I will show you how to use basic database manipulation features in PHP.

Step 5 – PHP MySQL database example:

Here you can find a complete code which demonstrates all the 4 basic SQL statements and how to use PHP to modify data in a MySQL database.

?php
       mysql_connect("localhost", "username", "password") or die(mysql_error());
      mysql_select_db("test") or die(mysql_error());
     echo "<br>Step 1. Insert data<hr>";
     $sql = "INSERT INTO users (name,city,web,age) VALUES ('Tom','Vegas','www.tom.com',5)";
     $result = mysql_query($sql);
       if ($result){
       echo "Data was inserted!<br>";
       } else {
       echo "An error occured during insert!
";
       }
       $result = mysql_query("SELECT * FROM users");
       while($row = mysql_fetch_assoc($result)){
      echo "ID: ".$row['id'].", Name:".$row['name']
      .", City:".$row['city'].", Age:".$row['age']."<br>";
       }
       echo "<br>Step 2. Update data<hr>";
      $sql = "UPDATE users SET age=45 WHERE name='Tom'";
      $result = mysql_query($sql);
       if ($result){
      echo "Data was updated!<br>";
       } else {
       echo "An error occured during update!
";
       }
       $result = mysql_query("SELECT * FROM users");
       while($row = mysql_fetch_assoc($result)){
       echo "ID: ".$row['id'].", Name:".$row['name']
       .", City:".$row['city'].", Age:".$row['age']."<br>";
        }
        echo "<br>Step 3. Remove data<hr>";
       $sql = "DELETE FROM users WHERE name='Tom'";
       $result = mysql_query($sql);
       if ($result){
       echo "Data was removed!<br>";
      } else {
       echo "An error occured during remove!
";
       }
        $result = mysql_query("SELECT * FROM users");
       while($row = mysql_fetch_assoc($result)){
       echo "ID: ".$row['id'].", Name:".$row['name']
       .", City:".$row['city'].", Age:".$row['age']."<br>";
        }
       ?>

This is just a tutorial for learning the basics .In a production enviroment other techniques are used to accomplish database functionality .In the feature I will publish the more advanced techniques.

Advertisements

Comments»

1. Using PDO Objects in PHP to access different Databases « Tournas Dimitrios - June 23, 2010

[…] June 23, 2010 Posted by tournasdimitrios1 in Uncategorized. trackback In a previous article I demonstrated the basic method to access Mysql through PHP .That technique has two disadvantages […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s