Beginners guide to PHP and MySQL

Ok, I'm convinced ! I want to use PHP and MySQL, but how do I install and run the stuff ?
What are the basic things I have to know, use and do to get a simple PHP-MySQL application working?

This tutorial is down to basics. No fancy coding, exemplary scripts etc. just the basics.

One more thing, there are a lot of tutorials out there dealing with UNIX based machines, this tutorial will focus on Windows based platforms. Nevertheless, except of the installation part, which is more or less Windows specific, the other parts are identical for all platforms.

In this tutorial we will build step by step a small web site using PHP and MySQL with the following features:

1. Viewing the database;
2. Adding an entry to the database;
3. Modifying and entry on the database;
4. Deleting an entry from the database.

On the way we will work both with the MySQL terminal and PHP scripts to get the feel of both.

Section 1 - Collecting and setting up the necessary items.

Ok, having said that, let�s get down to business. In order to start the PHP- MySQL wonder we need some basic stuff:

1. Web server running;
2. PHP extension for you web server;
3. MySQL server running;

First step, Getting and installing a Web Server:

Let's assume that you don�t have a web server running on your machine. There are few nice web server applications available for Windows. Just one thing, in order to run php/mysql the web server you choose has to support extensions.

One of the easiest servers to install on Windows and to install PHP on is OmniHTTPd http://omnicron.ca/httpd/download.html), which is a very nice free web server.

Once you got the installation file from the site, double click it and install the web server. If you chose to install the web server with all the default values then you will have it installed at c:\httpd. Once installed successfully, the installation program will launch your web browser with a lame welcome message and you'll get a new icon on the windows tray.

Ok, done with that.

Next, Downloading and installing the PHP distribution:

Getting the PHP Win32 distribution is easy: just go to http://www.php.net/downloads.php and choose the best location for you.

The PHP installation on OmniHTTPd is very simple.

First Unzip the installation file to your disk at, lets say, e:\php.

Copy the file, 'php3-dist.ini' to your '%WINDOWS%' directory and rename it to 'php3.ini' (c:\windows on Win95/95 and c:\winnt on WinNT/Win2k).

Edit your 'php3.ini' file as follows:

Change the 'extension_dir' setting to point to your php-install-dir (c:\php), or where you have placed your 'php3_*.dll' files.

Choose which modules you would like to load when PHP starts. You can uncomment the: 'extension=php3_*.dll' lines to load these modules (make sure you uncommented extention=php3_mysql.dll). Some modules require you to have additional libraries installed on your system for the module to work correctly.

The PHP FAQ (http://www.php.net/FAQ.php3) has more information on where to get supporting libraries. You can also load a module dynamically in your script using: dl("php_*.dll");

The DLLs for PHP extensions are prefixed with 'php3_'. This prevents confusion between PHP extensions and their supporting libraries.

Now follow few simple steps to configure OmniHTTPd:

Right click on the blue OmniHTTPd icon in the system tray and select Properties;

Click on Web Server Global Settings;

Click On the 'External' tab. Now, enter: ".php3" at the "Virtual" field, "c:\php\php.exe" on the "actual" field and click on "Add";

Choose the Mime tab and enter: "wwwserver/stdcgi" on the "Virtual" field, ".php3" on the "Actual" field and click "Add".

Click OK.

Choose �Default Virtual Settings� on that same window;

Now, click on the �Server� tab;

Under the �Default Index� field enter �index.php3� (separate by a comma). This will tell the web server to look for index.php3 file also.

Click OK.

Ok, that should be enough. Lets test if PHP is working:

Create a small text file named index.php3 with the following text:

Now, launch your browser and point it to the localhost. You should get "I made it!" on the screen.

Now, getting and installing MySQL:

Go to http://www.mysql.com/downloads/index.html, get the MySQL-Win32 distribution, unzip the zip file to a temp directory and run the installation file (setup.exe).

If you chose the default typical installation all of MySQL files will be installed at c:\mysql.

Now, if you followed all the steps, every thing should work properly.

Let's make some tests:

First, you have to run the MySQL demon by mysqld-shareware.exe located at c:\mysql\bin. You'll see a dos window opens and after few seconds closes. If no error messages appeared MySQLd is running.

If you want to check MySQL, just run c:\mysql\bin\mysql. You�ll see a terminal window with "mysql>" prompt. That's good; we are talking with the MySQL server.

Section 2 - Creating and manipulating a MySQL database:

First we have to create the database and the table we want to use. We will call our database "example" and create a table called "tbl" with few columns: id number, first name, last name and info. Creating the database and defining the
table is done through the mysql terminal - just double click or run c:\mysql\bin\mysql.exe.

To see which tables are defined on MySQL use (Note that the �Mysql>� is the terminal prompt:

Mysql> show databases; <Enter>

This command should display something like this:

+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.01 sec)

In order to define a new database (example) type in:

Mysql> create database example; <Enter>

You should see a reply like:
Query OK, 1 row affected (0.17 sec)

That's good, we have a new database. Now we can create a table inside the database, but first we have to choose the new database:

Mysql> use example; <Enter>

The responds should be:
Database changed

Now we can create the table with the following columns:

Index number - which will be a integer;
User name - which will be a maximum 30 char text field;
Last name - which will be a maximum 50 char text field;
free text - which will be a maximum 100 char text field;
To create this table, type the following command at the MySQL prompt:

MySQL> create table tbl (idx integer(3), UserName varchar(30), LastName varchar(50), FreeText varchar(100));<enter>

The responds should be something like:
Query OK, 0 rows affected (0.01 sec)

Ok, Let�s see how the table looks from the MySQL prompt, by typing the command:

MySQL> show columns from tbl; <enter>

We should get the following result:

+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| idx      | int(3)       | YES  |     | NULL    |       |
| UserName | varchar(30)  | YES  |     | NULL    |       |
| LastName | varchar(50)  | YES  |     | NULL    |       |
| FreeText | varchar(100) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Here we can see the content of table "tbl" which we just created.

Now we can see what is in the table. Let's type the following command:

MySQL> select * from tbl;<enter>

This command asks to display all the data in the "tbl" table. The output should be:
Empty set (0.07 sec)

The reason we got this responds is that we don't have any data inserted in the table. Let's insert some data into the table by typing:

MySQL> insert into tbl values (1,'Rafi','Ton','Just a test');<enter>
Query OK, 1 row affected (0.04 sec)

As we can see, we inserted the values into the table inthe order that we defined the table earlier because this is the default cell order. We could set the order of data by using the following syntax:

MySQL> insert into tbl (idx,UserName,LastName,FreeText) values (1,'Rafi','Ton','Just a test');<enter>

Ok, now we can try and see the content of the table:

MySQL> select * from tbl;<enter>

This time the result is:

+------+----------+----------+-------------+
| idx  | UserName | LastName | FreeText    |
+------+----------+----------+-------------+
| 1    | Rafi     | Ton      | Just a test |
+------+----------+----------+-------------+
1 row in set (0.00 sec)

Now we can see the structure of the table and the content of each cell.

Let's say we want to delete this set of data. To achieve this we should type:

MySQL> delete from tbl where idx=1 limit 1;<enter>
Query OK, 1 row affected (0.00 sec)

Ok, some explanation is in order. We are telling MySQL to delete from "tbl" the row that has idx value of 1 and limiting the deleted rows to 1. If we didn't limit the rows deleted by 1, then all the rows holding idx value of 1 would have been deleted (in this case we had only one row, but nevertheless I added it just to make the point clear).

Unfortunately, we have again an empty table, so let's fill it again:

MySQL> insert into tbl values (1,'Rafi','Ton','Just a test');<enter>
Query OK, 1 row affected (0.04 sec)

Another thing we can do is to modify the content of a specific cell using the "update" command:

MySQL>update tbl set UserName='Berber' where UserName='Rafi';<enter>
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

This command will seek all the UserName cells with value of "Rafi" and will change it to "Berber". Note that the "set" section and the "where" section are not necessarily have to be the same. We can change one column by searching another. Furthermore, we can perform a search by two or more conditions:

MySQL>update tbl set UserName='Rafi' where UserName='Berber' and LastName='Ton';<enter>
Query OK, 1 row affected (0.04 sec)

This query searched both columns for their content and changed the content of UserName.

Section 3 - Combining PHP and MySQL

In this section we will build a simple PHP based web site to control the aforementioned MySQL table.

We will build the following site structure (I'll resume you all know some basic html):

1. index.php3 for the front end and viewing the table;
2. add.php3 to add entries to the table;
3. Modify.php3 to edit the table content;
4. del.php3 to delete entries from the table;

First, we want to view the database:

Consider the following script.

Index.php3:

Ok, some explaining:

We begin with normal html tags to create the html tructure. When we want to escape from html and move to PHP we open a PHP section with <? This tells the webserver to treat the following text as PHP syntax and not plain html. To close the PHP section we used ?> tag.

The mysql_connect() command tells PHP to establish a connection to the MySQL server. If the connection is established successfully the script will continue, if not it will print the die message "Problem connecting to DataBase" (More information about mysql_connect and other PHP functions could be found on http://www.php.net under documentation).

Now, if the MySQL was installed like we discussed above this should be enough, but if you are using a pre-installed MySQL (like ISP) you should use the following command:

mysql_connect (localhost, username, password);

We placed the query we want to perform on the MySQL in $query, and then the script executes it by using mysql_db_query command:

$result = mysql_db_query("example", $query);

while the "example" varible is the name of the database and $query is the needed MySQL query.

We used the MySQL command select (as described above) to get all the data from the table:

$query = "select * from tbl";

Few words about $result the function, the function returns a positive MySQL result identifier to the query result, or false on error. This is not the result itself but just an identifier that can be later translated to the information we need.

Now we want to check whether we have any data in the database, and is so to print it formatted in a html table structure.

To check whether we have data we used the if command with the following syntax:

while "do something" is your set of commands to be executed when the argument=true, and "do something different" is the set of commands to be executed if the argument=false.

Notice that we printed some html tags to build the html table structure using the echo command. Only printed text from PHP command will be inserted as html - not the PHP commands themselves.

Another command we used is the while instruction, which uses the following syntax:

The while loop will repeat itself as long as the argument=true and will execute the set of instructions inside the { } set.

Here we combined the while loop with the PHP function $r=mysql_fetch_array ($result). This function gets one row that corresponds to the result identifier and places the result in an associative array $r, with the column names as the array identifiers. In our script we will get an array: $r['idx'], $r['UserName'], $r['LastName'] and $['FreeText'].

We could also use the function mysql_fetch_row which places the results in a regular array and then we would get an array: $r[0], $r[1], $r[2] and $r[3] with corresponding values.

For further information about those function please visit http://www.php.net.

Now, that we have all the information, we can print it inside the html table with:

Now we can free the MySQL connection and free some resources by using the mysql_free_result($result); function.

Another useful feature of PHP is the ability to include text files inside the script. Let's assume we have a repeating set of code (e.x. set of links to other pages), we can use the include function and save us some coding and time. Furthermore, if we want to change the code all we need to do is change the included file and it will affect all the pages we included the file.

Here we created a text file called Links.x that holds the entire link menu we want to use on every page:

The include syntax is:

Include ('included_text_file');

Now we can close the PHP section with ?> and the html page with </body></html>.

Adding data to the database using forms

Let's review the following code:

Assuming you are familiar with forms, this is rather a simple script. We designed a form based html page, which calls for add2tbl.php3 script upon submission. Now, the form consists with 4 fields corresponding our MySQL table:
index number, FirstName, LastName and FreeText. Notice that we called the form fields like the MySQL table names, but this is not necessary just convenient.

Again we added the link set with the include command using <? include ('links.x');?> (like we explained before).

Let's take a look at add2tbl.php3 script:

Explanation:

This page consists from two major parts. The first one takes the data from the previous form and inserts it into the database and the second part prints the new table from the database. The second part is identical to what I've
demonstrated in the viewing the database section.

The first part:

First we connect to the database as usual using mysql_connect().

Then we use the following query:

$query = "insert into tbl values ('$idx','$','$LastName','$FreeText')";

This query takes $idx, $UserName, $LastName and $FreeText which were transferred from the previous form and insert them into the table tbl.

Notice that I've used some remarks on the script. To state a remark just use "//" and the server will ignore the rest of the line.

Simple, isn't it ?

Editing an entry from the database:

Let's assume we want to modify an existing entry from the database. We saw earlier that there is a SQL command called set which sets values in existing database cells. We will use this command to modify entries on the database.

Consider the following script:

edit.php3:

As you can see we have some familiar code here. The first part just prints the Database inside a table. Notice there is one different line:

<a href=\"editing.php3?idx=$idx&user=$user&last=$last&text=$text\">$idx</a>

This line creates a link to editing.php3 and transfers some variables to the new script. Very similar to the form, but with a link. We transfer the information in pairs: the variable and its value. Notice that in order to print
the " sign we have to use \" or else the server will interpret it as a part of the PHP script and as printed information.

We want to transfer the complete content of a database table row so we will have the exact data from the table and we will be able to modify it easily.

Editing.php3:

Well, this script is rather simple. The only interesting thing is when the form is printed it holds the current cell data which was transferred from the previous page using the value=��� command within the <input type=�> command.

Now if we don�t change the information of the cell it will transfer the current value, which is the default one, but if we change the value of the field it will change the value to the new value. Now we can transfer the new values to another script which will change the information on the MySQL table.

editdb.php3:

Basically, the only interesting thing about this is the following line:

Notice that this is the same syntax that was explained earlier at the MySQL section. Another thing, note that this script changes the rows in the table where idx=$idx, if we have more than one row with the same idx then all the rows with the corresponding idx will be changes. If we want to be more specific we can change the where statement to the following:

This syntax will check all the cells and not only idx.

Deleting a record from the database:

Well, deleting is easy. Again, we have two scripts: one to select the row to be deleted (basically the same as selecting the row to be edited as explained above) and the second to actually delete the row and to print the new table.

del.php3:

This is a very familiar script we used it the edit section, so see the explanation there.

dele.php3:

This script is also very familiar, the only new thing is the delete query syntax:

This query deletes a row then all the cells match the information transferred from the previous script. Easy.

Well, that's all for now folks.

Questions and remarks will be accepted gladly.

Rafi Ton, rafi@weberdev.com