Using PHP

PHP and MySQL make up  the best combination for data-driven web sites. Moreover, both PHP and MySQL are free to use. 

MySQL is a database server, which is ideal for both small and large applications alike. In addition to supporting the ANSI-SQL standard, MySQL also compiles on a number of different platforms and has multithreading abilities, which makes for great performance. For non-Unix people, MySQL can be run as a service on Windows NT and as a normal process on Windows 95/98 machines. 

PHP is a server-side scripting language. This means that the server processes the script and sends plain HTML back to the browser. You can create some fairly complex scripts and routines with PHP. PHP can also connect to big-vendor databases such as Informix, Oracle, Solid and PostgreSQL. 

In this tutorial, we're going to teach you how to connect to a MySQL database using PHP. We will also describe how to enter, edit and delete entries from a MySQL database using PHP and HTML forms. 

For this tutorial, you will need some basic knowledge of PHP. You will also need the following:

• PHP and MySQL installed on a server. (If you don't have a web server, you should install Apache (http://www.apache.org/).

• PHPMyAdmin(http://www.phpwizard.net/projects/phpMyAdmin/). Great for creating MySQL tables. We'll be using PHPMySQLAdmin because its very easy to use while you're still getting your head around the SQL language syntax.

So, without further ado, let us get right into it and create our first database.

Creating your first database

Before we can extract data from a database, we actually need the data to be in-place. The easiest way that I have found to create a table is by using PHPMyAdmin. This nifty script allows you to create, edit and remove MySQL databases, tables, rows, fields, etc. Plus, it's a lot easier to use PHPMyAdmin compared to telnet (this facility is not available in our server) or the MySQL command prompt. 

First of all, make sure that you have installed PHPMyAdmin correctly, and call it up using your server. Please contact your server administrator for details. Using the input form, select your database as 'mydb' (which say, is your database). 

Now we need to create a table inside of our database. You can do it manually, but for now, just copy the following text into the text area field:

This will create a table named 'address'. The 'address' table will also contain three records of test data. In this example we are just going to create a simple address book to get us started. Now that we have our database, table and some sample data, we will display the data into a more user-friendly form. Using PHP, we will format the data as plain HTML.

Bringing the data back

Ok, now that we have a database setup, let's do something with it. Copy and paste the following text into your favourite HTML editor and save it as a PHP file on your web server. Name it 'open.php':

We are starting by creating a basic PHP script, which will only display one record. 

Let us just explain what happens here. The mysql_connect( ) function opens a link to our MySQL server on the specified host (localhost) along with a username (user). If you need to specify a password, you would add it after the username. The data to keep the connection open is stored in the variable named $db. 

mysql_select_db( ) then tells our web server that any queries we make should be directed to the database named 'mydb'. The mysql_select_db() function can also be used to create multiple connections to different databases. 

Next we actually need to grab some data from the database. This is where the mysql_query( ) function comes into play. Using the database connection details stored in $db, MySQL processes the query string passed as its first parameter. The results of this query are stored in the $result variable. 

Finally, the mysql_result( ) function is used to hold the values of the fields returned from our query. Using $result, we move to the first row. The first row is numbered as row 0. 

Once all of the querying and processing is out of the way, we actually need to output the data retrieved from the query. The print function handles the outputting of the data. The '%s' is a symbolic representation for a string variable, and is replaced with the result from the second parameter passed to the function, which is mysql_result($result,0,"name"). This query will only show one row of our MySQL address table. If we want to see all rows, then change all the 0's into 1's (for each mysql_result() call), and see what happens! 

We have just created and executed a simple script to retrieve information from our database. Next, we will describe how to display multiple records and how to send data to and from the database.

Going loopy

We are going to show you some more useful features of PHP and the MySQL language. Open 'open.php' and overwrite the entire file with the new code, which is shown below:

Save 'open.php' and load it up in your web browser. You should see all of the entries that we made when the table was first created. If you take a careful look at the script, you will notice a few new things. 

Apart from the table, the while( ) statement is new. The while statement basically says that as long as there is at least one row available, then print out the data in all of the remaining rows. The mysql_fetch_row( ) function is used to achieve this result. 

The great thing about using a while( ) loop is that if your query returns no records, you won't get an error message. But if it returns no data, then we have no way of letting the user know. We should create an alternate program route that will let the user know if the MySQL function can't find any records matching its query:

As you can see, there have been some fairly substantial changes to our original 'open.php' script. We have used the mysql_fetch_array( ) function instead of the mysql_fetch_row( ) function, which will allow us to refer to fields by their names (such as $myrow["name"]) instead of using their id's. This is much easier than having to use numbers, and should save you some headaches when dealing with more complex queries where several dozen fields are involved. 

We have also added a do/while loop and an if-else statement. The if-else statement says that if we can assign a row to $myrow, then continue; otherwise, skip to the else section and execute the code contained within its block. 

The do/while loop is a variation of the while() loop we used on the last page. We need the do/while loop here for a very good reason: with the initial if statement, we assigned the first row returned by the query to the variable $myrow. If, at this point, we executed a normal while statement (such as while ($myrow = mysql_fetch_row($result)), then we would be overriding the first record with the second record. The do/while loop lets us test the condition after the code has been run once, so there's no chance of us accidentally skipping a row. 

Finally, if there are no records returned at all, the statements contained in the else{} portion of the code will be executed. To see this take action, change the SQL statement to SELECT * FROM address WHERE id=6 or something similar that will return no records.

Linking

We are going to take the looping power we just learned and use it in a more practical example. Before we proceed here though, you should know how to work with HTML forms, the PHP query string, and the GET and POST methods. 

As you should know, there are three ways to get information into the query string variable. The first is to use the GET method in a form. The second is to type the information directly into the URL in your web browser. Lastly, you can embed a query string in a standard link. Just make the anchor tag look something like this: <a href="http://my_machine/mypage.php3?id=1">. We are going to use this technique right now. 

First , let us query our database again, listing the name field. Take a look at the following script, which should look familiar by now:

Once again, overwrite 'open.php', load the page in your web browser and see what happens. The first thing you should notice is that only the name field shows up, and each name field is a link. The output appears this way because of the way the loops are placed in our code. 

This time, you will notice that we have used the printf() function. Firstly, notice that each quotation mark is preceded by a backslash. The backslash tells PHP to escape the character following it, rather than treat it as part of the code. Also, note the use of the variable $PHP_SELF. This variable (which stores the current script's name and location) is passed along with every PHP page. It's useful here because we just want this file to call itself. Using $PHP_SELF, we can be sure this will happen everytime: even if the file is moved to another directory... or even another machine. 

PHP does a nifty thing whenever it sees a valid name/value pair contained within the query string. It automatically creates a variable which is called the name of the name/value pair. This variable contains the value of the name/value pair. This feature allows us to test whether it's the users first or second time through this page. All we have to do is ask PHP if the variable $id exists. 

If it does exist the script will display the individual account, according to what $ID was selected.

Adding a record to our address book

We have managed to get data from our database without much difficulty. But what about sending data the other way? It is not a problem with PHP. First, let us create a page which contains a simple form:

Note the use of $PHP_SELF again. You will also notice that each form element matches the field name in the database. This is not compulsory; it is just a good idea so you can get your head around the code for now. Also notice that I have given the submit button a name attribute. I have done this so I can test for the existence of a $submit variable. That way, when the page is called again, I will know whether someone used this form. I should mention that you don't have to have a page that loops back on itself. You can span two, three, or more pages, if you like. However, by using just one page, everything stays together and in one file. 

Ok, let us add some code that will check for the form input. Just to prove that the form input does make it through, I'll dump all of the variables to the screen via the $HTTP_POST_VARS array. This is a useful debugging feature. If you ever need to see all the variables on a page, you should use the $GLOBALS array instead:

Now that we can collect information from the user, let's take the form information and post it to the database:

This script will allow us to add new entries to our address table. Let us now talk about updating and deleting records from our table.

This looks complex, but it really isn't. The script is broken up into three parts. The first if() statement checks to see whether the submit button has been pressed. If it has, the script checks to see whether the variable $id exists. If it doesn't, then we are adding a record. Otherwise, we are editing a record. 

Next we check to see whether the variable $delete exists. If it does, we are deleting a record. Note that with the first if() statement, we checked for a variable that came through as the POST method, and in this part of the script, the variable would be part of the GET method set of variables. 

Finally, we take the default action that displays the list of employees and the form. Again, we check for the existence of the $id variable. If it exists, we query the database to display the relevant record. Otherwise, we just display a blank form.