A couple of years ago, I was doing some work for a client. They wanted to use a database on their site and bad news was I did not know how to accomplish this without the help from an outsider. I also did not make that money from the job

.

If you know some basic HTML coding and graphics, you are on your way to producing dynamic content. Once you start using a database with your pages, you will be hooked. PHP and MySQL make it really easy to reach your goal.

These are our goals for this tutorial:

1. Create a database in MySQL.
2. Create a table and fill it with data.
3. Dump lots of data in our table.
4. Retrieve the data we put in our table.
5. Learn how to delete and modify data in our table.
6. Write a basic PHP script that will connect to the MySQL server
and will display our table data.


Now, I'm not going over all aspects of MySQL, since there is a lot to cover and that is your homework at the end of this tutorial. I'm not going over installation of MySQL or PHP, that is covered in other tutorials on this site.



Create a database in MySQL

There are many ways to connect to the MySQL server, I use a Linux server that I telnet to by using SSH2, a secure way to connect. Many hosts don't allow telnetting but I got lucky.

If you can not connect this way there are many other ways like:

1. Any of the web-based GUI clients available
2. Running them locally on your own computer

I write up all of my commands ahead of time then I can copy and paste them into the MySQL monitor program. It's easier and you don't waste time figuring out what you want to achieve.

The below example has us selecting and connecting to the MySQL server, inputting our username, password, and then creating a database. I will be using all capital letters for the SQL commands so that you can distinquish them from the other commands.

NOTE: The "mysql>" represents the prompt of the MySQL server monitor, similar to the DOS prompt in Windows. This is where you type in your commands.

host_server:~$ mysql -u username -p
Enter password: 

Welcome to the MySQL monitor.


mysql> mysql -u username -p password

mysql> CREATE DATABASE database_name;

mysql> use database_name;

Now we have a database, time to create a table to put in our database.

NOTE: Some hosts don't allow you to create your database, so you have to use the same database that corresponds to your domain name or however they have it set up for your server.



Create a table and fill it with data

Let's create a table. Using the simple structure, here's the full command to create our table named stuff

CREATE TABLE stuff
(
id int not null auto_increment primary key,
name varchar(25) not null,
description text
);


mysql> CREATE TABLE stuff
    -> (
    -> id int not null auto_increment primary key,
    -> name varchar(25) not null,
    -> description text
    -> );
Query OK, 0 rows affected (0.02 sec)

In our table we have an id number that auto increments that column, meaning that if we insert one item, it will be called "1". If we add more items then it will increment, or have its own unique number.

Name is what we call our "stuff" and description is a short amount of
text to describe our "stuff".



Using the INSERT command I will dump some data into our table.

INSERT INTO stuff
    (id, name, description)
VALUES
    (NULL, 'Candy Bar', 'This is my favorite candy');

We inserted values into our table. NULL means the it has no data. NULL has no value until it gets into the table, if you specified a default value - MySQL will you that. Our stuff is called "Candy Bar" and the description is "This is my favorite candy".

Let's view what we have just inserted into our table.

Running the SELECT command, we retrieve the data stored in our table. The asterisk "*" means all fields.

mysql> SELECT * FROM stuff;
+----+-----------+---------------------------+
| id | name      | description               |
+----+-----------+---------------------------+
|  1 | Candy Bar | This is my favorite candy |
+----+-----------+---------------------------+
1 row in set (0.00 sec)

Optionaly, we could also select only one part of the data like this:

mysql> SELECT name FROM stuff;
+------------+
| name       |
+------------+
| Candy Bar  |
+------------+
1 row in set (0.00 sec)

Dump lots of data in our table

Let's dump lots of data into our table. An easy way is to do this
ahead of time with a text editor. Here's our dump, just copy and paste
it into the MySQL monitor program.

INSERT INTO stuff
    (id,name,description)
VALUES
    (NULL,'Computer','Let the machine do all of the work.');


INSERT INTO stuff
    (id,name,description)
VALUES
    NULL,'Dog','Man's best friend.');


INSERT INTO stuff
    (id,name,description)
VALUES
    (NULL,'Girlfriend','The woman of my dreams.');


INSERT INTO stuff
    (id,name,description)
VALUES
    (NULL,'Wife','The other goddess in my life.');


INSERT INTO stuff
    (id,name,description)
VALUES
    (NULL,'Car','My mode of transportation.');


INSERT INTO stuff
    (id,name,description)
VALUES
    (NULL,'Stereo','That thing that plays my cds.');


INSERT INTO stuff
    (id,name,description)
VALUES
    (NULL,'Bed','Where I lay my head at night.');


INSERT INTO stuff
    (id,name,description)
VALUES
    (NULL,'Television','That device with all of the commercials.');


INSERT INTO stuff
    (id,name,description)
VALUES
    (NULL,'House','Yard work should be against the law.');


INSERT INTO stuff
    (id,name,description)
VALUES
    (NULL,'Neighbor','Keep your dog in your own yard.');


INSERT INTO stuff
    (id,name,description)
VALUES
    (NULL,'Occupation','The skillz that pay the billz.');

Retrieve the data we put in our table

Okay, so now we have something to work with, and running the SELECT command again will show us how our table will look.

mysql> SELECT * FROM stuff;
+----+------------+------------------------------------------+
| id | name       | description                              |
+----+------------+------------------------------------------+
|  1 | Candy Bar  | This is my favorite candy                |
|  2 | Computer   | Let the machine do all of the work.      |
|  3 | Dog        | Man's best friend.                       |
|  4 | Girlfriend | The woman of my dreams.                  |
|  5 | Wife       | The other goddess in my life.            |
|  6 | Car        | My mode of transportation.               |
|  7 | Stereo     | That thing that plays my cds.            |
|  8 | Bed        | Where I lay my head at night.            |
|  9 | Television | That device with all of the commercials. |
| 10 | House      | Yard work should be against the law.     |
| 11 | Neighbor   | Keep your dog in your own yard.          |
| 12 | Occupation | The skillz that pay the billz.           |
+----+------------+------------------------------------------+
12 rows in set (0.00 sec)

Learn how to delete and modify data in our table

What if we wanted to change some data? An easy task, we have to know what data we are changing in order to alter its information.

For instance, we wanted to change the line about our Television.

We first select the correct column and then the field. Here's our query:

mysql> UPDATE stuff set description="That device with all of the BAD commercials"
where id = 9;

mysql> select * from stuff;
+----+------------+---------------------------------------------+
| id | name       | description                                 |
+----+------------+---------------------------------------------+
|  1 | Candy Bar  | This is my favorite candy                   |
|  2 | Computer   | Let the machine do all of the work.         |
|  3 | Dog        | Man's best friend.                          |
|  4 | Girlfriend | The woman of my dreams.                     |
|  5 | Wife       | The other goddess in my life.               |
|  6 | Car        | My mode of transportation.                  |
|  7 | Stereo     | That thing that plays my cds.               |
|  8 | Bed        | Where I lay my head at night.               |
|  9 | Television | That device with all of the BAD commercials |
| 10 | House      | Yard work should be against the law.        |
| 11 | Neighbor   | Keep your dog in your own yard.             |
| 12 | Occupation | The skillz that pay the billz.              |
+----+------------+---------------------------------------------+
12 rows in set (0.00 sec)

The UPDATE command modifies whatever field that you select. In this case, it's the one with the id with a value of 9. That's how the unique values come into place, there is no redundant information and it makes it easier for the server to select the data.

Okay, let's delete an item. Using the DELETE command, we will select the item of choice by its id number. Let's get rid of the Candy Bar. Here's the command:

mysql> DELETE FROM stuff where id = 1;

mysql> SELECT * FROM stuff;
+----+------------+---------------------------------------------+
| id | name       | description                                 |
+----+------------+---------------------------------------------+
|  2 | Computer   | Let the machine do all of the work.         |
|  3 | Dog        | Man's best friend.                          |
|  4 | Girlfriend | The woman of my dreams.                     |
|  5 | Wife       | The other goddess in my life.               |
|  6 | Car        | My mode of transportation.                  |
|  7 | Stereo     | That thing that plays my cds.               |
|  8 | Bed        | Where I lay my head at night.               |
|  9 | Television | That device with all of the BAD commercials |
| 10 | House      | Yard work should be against the law.        |
| 11 | Neighbor   | Keep your dog in your own yard.             |
| 12 | Occupation | The skillz that pay the billz.              |
+----+------------+---------------------------------------------+
11 rows in set (0.00 sec)

Finaly, a basic PHP script

Now, let's write a PHP script to connect to our server, and list everything in our table.

Start up your text editor, and copy and paste this code onto your page. Save it as stuff.php and upload it to your server. Call the page from your browser.

Here's the code:

NOTE: Fill in the values of your username, password, table and database name.

<?php


    echo "<B>This is a list of items I have in my table named $table</B> <hr>";

    $host = "localhost";
    $user = "username";
    $pass = "password";
    $database = "database_name";
    $table = "stuff";

        // Connecting to the Database
    $connect = @mysql_connect($host, $user, $pass) or die("could not connect to server");

        // Selecting the Database for use
    $db_select = @mysql_select_db($database) or die("could not select the database");

        // Compose SQL command
    $SQL = "SELECT * FROM $table";

        // Query the Database
    $result = @mysql_query($SQL) or die("could not complete your query");

        // Loop the results
        // Note, results are returned as an
        // Array - we cannot just print it out.
    while($row = @mysql_fetch_array($result)) {
            $id = $row["id"];
            $name = $row["name"];
            $description = $row["description"];

            echo "$name - $description <P>";
    }

    echo "<B>and thats all folks ...</B>";

?>

We set up variables for the names of our server, username, password, database and table in the top part of the script. We connect to the server with the mysql_connect() function[[, using the "AT" sign ("@") to suppress any warning messages that may occur. This logs into our server.

The mysql_select_db() function selects our database, called database_name, and the $SQL variable contains the query to select all of the data from our table, named stuff.

The $result variable equals the mysql_query() function. We use a while statement to loop through the rows of the table. We have selected everything in the table and now assign each variable a value from the current row.

In order to view the data, we use an echo or print language construct to print out our data. The curly bracket closes the loop and ends the script.


I hope that this tutorial will help you to better understand how to use a database to store your data and PHP to process it for you.

Good Luck, and don't fall asleep till you get it working - it is not that hard after all, yet it's a must.

Joel