WebPage / Digital / Multimedia Development

CLIENT SIDE

THE WHAT / TOPICS

THE HOW / LESSONS

Mark Up Language

1. html
2. xhtml
3. resources
1. Free Webmaster Help.cpm Tutorials
2. W3Schools
3. ALA

CSS

1. inline
2. embedded
3. linked/imported
4. style concepts
1. layout
2. positioning
3. dimensioning
4. typographical
5. style elements
1. special css projects
6. resources
1. ALA
2. Simple Bits
3. W3schools
4. Web Monkey
5. BlueRobot
6. W3C
7. Eric Meyer

Graphics

1. vector
2. raster
1. file formats
2. resolution
3. layers
4. slicing
3. image management with css
1. backgrounds
2. z-index
3. dimensioning
4. positioning
4. web-optimizing images
   1. command-line and shell script image conversions and management (ImageMagick)
5. resources
1. ALA

DOM

1. ecma scripting
2. javascripting
3. scripts with css - projects
1. hierarchical menus
2. sorting tabular data
4. resources
1. ALA

SERVER SIDE

WHAT / TOPICS

HOW / LESSONS

PHP

1. automation/interactivity
2. database access and management
3. PHP-based applications
4. using php frontends to trigger server events; cgi (perl, bash)
5. resources
1. PHP.net
2. Free Webmaster Help.cpm Tutorials

To output text in your PHP script is actually very simple. As with most other things in PHP, you can do it in a variety of different ways. The main one you will be using, though, is print. Print will allow you to output text, variables or a combination of the two so that they display on the screen.

The print statement is used in the following way:

print("Hello world!");

I will explain the above line:

print is the command and tells the script what to do. This is followed by the information to be printed, which is contained in the brackets. Because you are outputting text, the text is also enclosed instide quotation marks. Finally, as with nearly every line in a PHP script, it must end in a semicolon. You would, of course, have to enclose this in your standard PHP tags, making the following code:

print("Hello world!");
?>

Which will display:

Hello world!

on the screen.

Variables

As with other programming languages, PHP allows you to define variables. In PHP there are several variable types, but the most common is called a String. It can hold text and numbers. All strings begin with a $ sign. To assign some text to a string you would use the following code:

$welcome_text = "Hello and welcome to my website.";

This is quite a simple line to understand, everything inside the quotation marks will be assigned to the string. You must remember a few rules about strings though:

Strings are case sensetive so $Welcome_Text is not the same as $welcome_text
String names can contain letters, numbers and underscores but cannot begin with a number or underscore
When assigning numbers to strings you do not need to include the quotes so:

$user_id = 987

would be allowed.

Outputting Variables

To display a variable on the screen uses exactly the same code as to display text but in a slightly different form. The following code would display your welcome text:

$welcome_text = "Hello and welcome to my website.";
print($welcome_text);
?>

As you can see, the only major difference is that you do not need the quotation marks if you are printing a variable.

Formatting Your Text

Unfortunately, the output from your PHP programs is quite boring. Everything is just output in the browser's default font. It is very easy, though, to format your text using HTML. This is because, as PHP is a server side language, the code is executed before the page is sent to the browser. This means that only the resulting information from the script is sent, so in the example above the browser would just be sent the text:

Hello and welcome to my website.

This means, though, that you can include standard HTML markup in your scripts and strings. The only problem with this is that many HTML tags require the " sign. You may notice that this will clash with the quotation marks used to print your text. This means that you must tell the script which quotes should be used (the ones at the beginning and end of the output) and which ones should be ignored (the ones in the HTML code).

If statements are used to compare two values and carry out different actions based on the results of the test. If statements take the form IF, THEN, ELSE. Basically the IF part checks for a condition. If it is true, the then statement is executed. If not, the else statement is executed.

IF Strucure

The structure of an IF statement is as follows:

IF (something == something else)
{
THEN Statement
} else {
ELSE Statement
}

Variables

The most common use of an IF statement is to compare a variable to another piece of text, a number, or another variable. For example:

if ($username == "webmaster")

which would compare the contents of the variable to the text string. The THEN section of code will only be executed if the variable is exactly the same as the contents of the quotation marks so if the variable contained 'Webmaster' or 'WEBMASTER' it will be false.

Constructing The THEN Statment

To add to your script, you can now add a THEN statement:

if ($username == "webmaster") {
echo "Please enter your password below";
}

This will only display this text if the username is webmaster. If not, nothing will be displayed. You can actually leave an IF statement like this, as there is no actual requirement to have an ELSE part. This is especially useful if you are using multiple IF statements.

Constructing The ELSE Statement

Adding The ELSE statement is as easy as the THEN statement. Just add some extra code:

if ($username == "webmaster") {
echo "Please enter your password below";
} else {
echo "We are sorry but you are not a recognised user";
}

Of course, you are not limited to just one line of code. You can add any PHP commands in between the curly brackets. You can even include other IF statments (nested statements).

Other Comparisons

There are other ways you can use your IF statement to compare values. Firstly, you can compare two different variables to see if their values match e.g.

if ($enteredpass == $password)

You can also use the standard comparision symbols to check to see if one variable is greater than or less than another:

if ($age < "13")

Or :

if ($date > $finished)

You can also check for multiple tests in one IF statement. For instance, if you have a form and you want to check if any of the fields were left blank you could use:

if ($name == "" || $email == "" || $password == "") {
echo "Please fill in all the fields";
}

The WHILE loop is one of the most useful commands in PHP. It is also quite easy to set up and use. A WHILE loop will, as the name suggests, execute a piece of code until a certain condition is met.

Repeating A Set Number Of Times

If you have a piece of code which you want to repeat several times without retyping it, you can use a while loop. For instance if you wanted to print out the words "Hello World" 5 times you could use the following code:

$times = 5;
$x = 0;
while ($x < $times) {
echo "Hello World";
++$x;
}

I will now explain this code. The first two lines are just setting the variables. The $times variable holds the number of times you want to repeat the code. The $x variable is the one which will count the number of times the code has been executed. After these is the WHILE line. This tells the computer to repeat the code while $i is less than $times (or to repeat it until $i is equal to $times). This is followed by the code to be executed which is enclosed in { }.

After the echo line which prints out the text, there is another very important line:

++$x;

What this does is exactly the same as writing:

$x = $x + 1;

It adds one to the value of $x. This code is then repeated (as $x now equals 1). It continues being repeated until $x equals 5 (the value of times) when the computer will then move on to the next part of the code.

Using $x

The variable counting the number of repeats ($x in the above example) can be used for much more than just counting. For example if you wanted to create a web page with all the numbers from 1 to 1000 on it, you could either type out every single one or you could use the following code:

$number = 1000;
$current = 0;
while ($current < $number) {
++$current;
echo "$current
";
}

There are a few things to notice about this code. Firstly, you will notice that I have placed the ++$current; before the echo statement. This is because, if I didn't do this it would start printing numbers from 0, which is not what we want. The ++$current; line can be placed anywhere in your WHILE loop, it does not matter. It can, of course, add, subtract, multiply, divide or do anthing else to the number as well.

The other reason for this is that, if the ++$current; line was after the echo line, the loop would also stop when the number showed 999 because it would check $current which would equal 1000 (set in the last loop) and would stop, even though 1000 had not yet been printed.

Arrays

Arrays are common to many programing languages. They are special variables which can hold more than one value, each stored in its own numbered 'space' in the array. Arrays are extremely useful, especially when using WHILE loops.

Setting Up An Array

Setting up an array is slightly different to setting up a normal variable. In this example I will set up an array with 5 names in it:

$names[0] = 'John';
$names[1] = 'Paul';
$names[2] = 'Steven';
$names[3] = 'George';
$names[4] = 'David';

As you can see, the parts of an array are all numbered, starting from 0. To add a value to an array you must specify the location in the array by putting a number in [ ].

Reading From An Array

Reading from an array is just the same as putting information in. All you have to do is to refer to the array and the number of the piece of data in the array. So if I wanted to print out the third name I could use the code:

echo "The third name is $names[2]";

Which would output:

The third name is Steven

Using Arrays And Loops

One of the best uses of a loop is to output the information in an array. For instance if I wanted to print out the following list of names:

Name 1 is John
Name 2 is Paul
Name 3 is Steven
Name 4 is George
Name 5 is David

I could use the following code:

$number = 5;
$x = 0;
while ($x < $number) {
$namenumber = $x + 1;
echo "Name $namenumber is $names[$x]
";
++$x;
}

As you can see, I can use the variable $x from my loop to print out the names in the array. You may have noticed I am also using the variable $namenumber which is always 1 greater than $x. This is because the array numbering starts from 0, so to number the names correctly in the output I must add one to the actual value.

Mail is extremely easy to send from PHP, unlike using scripting languages which require special setup (like CGI). There is actually just one command, mail() for sending mail. It is used as follows:

mail($to,$subject,$body,$headers);

In this example I have used variables as they have descriptive names but you could also just place text in the mail command. Firstly, $to. This variable (or section of the command) contains the e-mail address to which the mail will be sent. $subject is the section for the subject of the e-mail and $body is the actual text of the e-mail.

The section $headers is used for any additional e-mail headers you may want to add. The most common use of this is for the From field of an e-mai but you can also include other headers like cc and bcc.

Sending An E-mail

Before sending your mail, if you are using variables, you must, of course, set up the variable content beforehand. Here is some simple code for sending a message:

$to = "php@gowansnet.com";
$subject = "PHP Is Great";
$body = "PHP is one of the best scripting languages around";
$headers = "From: webmaster@gowansnet.com\n";
mail($to,$subject,$body,$headers);
echo "Mail sent to $to";

This code will acutally do two things. Firstly it will send a message to php@gowansnet.com with the subject 'PHP Is Great' and the text:

PHP is one of the best scripting languages around

and the e-mail will be from webmaster@gowansnet.com. It will also output the text:

Mail sent to php@gowansnet.com

to the browser.

Formatting E-mail

Something you may have noticed from the example is that the From line ended with \n. This is acutally a very important character when sending e-mail. It is the new line character and tells PHP to take a new line in an e-mail. It is very important that this is put in after each header you add so that your e-mail will follow the international standards and will be delivered.

The \n code can also be used in the body section of the e-mail to put line breaks in but should not be used in the subject or the To field.

Mail Without Variables

The e-mail above could have been sent using different variable names (it is the position of the variables in relation to the commas, not the name of them which decides on their use). It could also have been done on one line using text like this:

mail("php@gowansnet.com","PHP Is Great","PHP is one of the best scripting languages around","From: webmaster@gowansnet.com\n");

But that would make your code slightly harder to read.

Error Control

As anyone who has been scripting for a while will know, it is extremely easy to make mistakes in your code and it is also very easy to input an invalid e-mail address (especially if you are using your script for form to mail). Because of this, you can add in a small piece of code which will check if the e-mail is sent:

if(mail($to,$subject,$body,$headers)) {
echo "An e-mail was sent to $to with the subject: $subject";
} else {
echo "There was a problem sending the mail. Check your code and make sure that the e-mail address $to is valid";
}

This code is quite self explanitory. If the mail is sent successfully it will output a message to the browser telling the user, if not, it will display an error message with some suggestions for correcting the problem.

Setting up a form for use with a PHP script is exactly the same as normal in HTML. As this is a PHP tutorial I will not go into depth in how to write your form but I will show you three of the main pieces of code you must know:

Will display a text input box with Your Name written in it as default. The value section of this code is optional. The information defined by name will be the name of this text box and should be unique.

<br /> Please write your message here.<br />

Will display a large scrolling text box with the text 'Please write your message here.' as default. Again, the name is defined and should be unique.

This will create a submit button for your form. You can change what it says on the button by changing the button's value.

All the elements for your form must be enclosed in the

The form's action tells it what script to send its data to (in this case its process.php). This can also be a full URL (e.g. http://www.mysite.com/scripts/private/processors/process.php). The method tells the form how to submit its data. POST will send the data in a data stream to the script when it is requested. GET is the other option. GET will send the form data in the form of the url so it would appear after a question mark e.g. http://www.mysite.com/process.php?name=david

It really makes no difference which system you use but it is normally better to use POST if you are using passwords or sensitive information as they should not be shown in the browser's address bar.

Getting The Form Information

The next step is to get the data the form has submitted into your script so that you can do something with it. This is. There are basically two different methods of getting the data into PHP, which depend on how they were submitted. There are two submission methods, GET and POST, which can both be used by forms. The difference between the two is that using GET, the variables and data will be shown in the page address, but using POST it is invisible. The benefit of GET, though is that you can submit information to the script without a form, by simply editing the URL.

This works the same as submitting a form using GET. The advantage of this is that you can create links to your scripts which do different things depending on the link clicked. For example you could create a script which will show different pages depending on the link clicked:

yourpage.php?user=david
could show David's page and:
yourpage.php?user=tom
could show Tom's page, using the same script.

It is also possible to pass more than one piece of information to the script using this system by separating them with the & symbol:

yourpage.php?user=david&referrer=gowansnet&area=6

These could all be accessed separately using the GET variables user, referrer and area.

To get a variable which has been sent to a script using the POST method you use the following code:
$variablename=$_POST['variable'];
which basically takes the variable from the POST (the name of a form field) and assigns it to the variable $variablename.

Similarly, if you are using the GET method you should use the form:
$variablename=$_GET['variable'];

This should be done for each variable you wish to use from your form (or URL).

Creating The Form To Mail Script

To finish off this section, I will show you how to use what you have learnt in this part and the last to create a system which will e-mail a user's comments to you.

Firstly, create this form for your HTML page:

This will make a simple form where the user can enter their e-mail address, their name and their comments. You can, of course, add extra parts to this form but remember to update the script too. Now create the PHP script:

$name=$_POST['name'];
$email=$_POST['email'];
$comments=$_POST['comments'];
$to="php@gowansnet.com";
$message="$name just filled in your comments form. They said:\n$comments\n\nTheir e-mail address was: $email";
if(mail($to,"Comments From Your Site",$message,"From: $email\n")) {
echo "Thanks for your comments.";
} else {
echo "There was a problem sending the mail. Please check that you filled in the form correctly.";
}
?>

Remember to replace php@gowansnet.com with your own e-mail address. This script should be saved as mail.php and both should be uploaded. Now, all you need to do is to fill in your comments form.

As with any programming language, it is quite important to comment in your script. If you are working on a script with someone else you must let them know what you code does and if you are distributing your script you will need to show people how to edit it. Even if you are the only one who will use your script it is useful to comment so that you can edit it at a later date.

In PHP there are two ways you can comment. One way is used for single line comments and the other is used mainly for comments that go over one line. A single line comment is written as follows:

// Your comment can go in here

Everything after the // will be ingnored when the script is executed. You can even place these on the end of another line e.g.

print "Hello $name"; // Welcome to the user

Another way of commenting is by using multi-line comments:

/* The following piece of code will take the input
the user gave and will check that it is valid before
adding it to the database */

Anything between the /* and the */ will be ignored. It is important that you always close this type of comment as not doing so could make your script not work.

Print, Echo and HTML

As you may have noticed during this tutorial I have actually used 4 different ways of outputting information to the browser:

echo("Text here");
echo "Text here";
print("Text here";
print "Text here";

To clarify, all of these do the same thing and you can use any or all of them in a script. There is no reason to even use the same type all through a script. The only problem you may find is that, as I explained in part 2, all the " in the HTML code must be replaced with \" which, if you have a lot of code, could take a very long time. This brings me to a very useful part of PHP. If, for example, you created the header of a page dynamically in PHP, then had the static page and finally a dynamic footer you can do the following:

Top PHP code in here
?>
HTML Code
Bottom PHP code in here
?>

This gets even better as the PHP code will just continue from where it was left off so you could do the following:

IF Statement {
?>
HTML For IF Being Correct
} else {
?>
HTML For IF Being Wrong
}
?>

You must always remember to close IF statements and loops, though, as it is very easy to forget.

One Line Prints

Being able to place HTML code into your PHP is very useful, but what happens if you want to put the value of a variable into the code. Unlike when using an echo or print statement, you can't just put in the variable name as this section is not actually part of the PHP code. Instead you must just put in a little PHP.

For example if you wanted to print someone's name from a script with HTML formatting you would do the following:

In the above code you have just added in the following PHP:

Which is exactly the same as the following PHP code:

echo($variablename);
?>

But all put onto one line.

Conclusion

This tutorial has given you some of the basics of PHP and should allow you to do most things you will want to. For a much more in depth look you should visit PHP.net, the official homepage of PHP. One major omission of this tutorial, you may have noticed, is using PHP with a database. As this is one of the major reasons that people use PHP and because there are many options I will put this in a separate PHP/MySQL tutorial.

SERVER SIDE

WHAT / TOPICS

HOW / LESSONS

Relational Databases

1. creating altering
2. dropping tables
3. using Mysql client to formulate and test queries
4. loading data
5. mysqldump for backups
6. Normalization of data
7. resources
1. MySQL.com

For many people, the main reson for learning a scripting language like PHP is because of the interaction with databases it can offer. In this tutorial I will show you how to use PHP and the MySQL database to store information on the web and include it into your website. Before you read this tutorial you should have at least a basic knowledge of how to use PHP. If you do not yet know PHP, I suggest that you read our PHP tutorial before continuing.

Why Would I Want A Database?

It is actually surprising how useful a database can be when used with a website. There are a huge variety of things you can do when you interact the two, from displaying simple lists to running a complete website from a database. Some examples of PHP and MySQL being used together are:

* Banner Rotation. On this site, where each banner is, a PHP script is called. This opens a database and picks a random banner from it to show the visitor. It also counts the number of times the banner has been viewed and could, with a few changes, track clicks too. To add, change or edit the banners all I have to do is change the database and the script will pick the correct banners for all the pages on the site.
* Forums. Hundreds of forums (message boards) on the internet are run using PHP and MySQL. These are much more efficent than other systems that create a page for each message and offer a wide variety of options. All the pages in the forum can be updated by changing one script.
* Databases. One quite obvious example is sites which get all there information from a database. For example Script Avenue is run by a few scripts, which gain all their information from a large database. All the different script categories can be accessed in one script by just changing the URL to access a different part of the database.
* Websites. If you have a large website and you want to change the design it can take a very long time to update and upload all the pages. With PHP and MySQL your whole website could be just one or two PHP scripts. These would access a MySQL database to get the information for the pages. To update the website's design you would just have to change one page.

What Do I Need?

You only really need three things to run PHP scripts which access MySQL databases. Firstly, you will, of course, need a webserver. This can either be on a computer of your own or on a web host. Any web server software should work with PHP and MySQL but the best to use is Apache, which is free.

PHP also needs to be installed on the server. If it is not already installed you can install it (or ask your web host to install it). It can be downloaded from PHP.net and is also free. If you are not sure if you have PHP installed I will show you a way to check it later.

Finally, you will also require MySQL. This is the actual database software. You can also use most other types of database (SQL, Oracle etc.) but as this is a PHP/MySQL tutorial I will deal just now with the MySQL database (although the commands used here will also work with SQL databases). As with the other software you need, MySQL is free and can be downloaded from the MySQL homepage. If you are not sure if you have MySQL installed, I will show you how to check later.

If you cannot install (or your web host won't allow) PHP and MySQL you can still use another web host. Freedom2Surf are a free (banner supported) web host and support PHP and have MySQL installed. HostRocket are an excellent web host and can offer you 300MB of space with PHP, MySQL and loads of other extras for under $10 a month.

Testing For PHP and MySQL

There is a simple test for both PHP and MySQL. Open a text editor and type in the following:

phpinfo();
?>

and save it as phpinfo.php

Now upload this to your webspace and go to it in your browser. If you have PHP installed you will see a huge page with all the details of your PHP installation on it. Next, scroll down through all this information. If you find a section about MySQL then you will know that MySQL is installed.

Managing Databases

Although all the database administrative options can be done through PHP scripts, I strongly suggest installing a copy of PHPMyAdmin on your server. It is an excellent free set of scripts that will provide you with an administrative interface for your MySQL database(s). You can add, remove, edit, backup and view your databases using this and it is especially useful when troubleshooting your databases.

This Tutorial

Throughout this tutorial I will be showing you some of the basics of using PHP and MySQL together. To do this I will be using an example all the way through. As you use this tutorial, you will learn how to create a web based contact management program. It will allow you to store names with their addresses, e-mail and phone numbers. You will be able to update records and search the database. There will even be an option which allows you to send an e-mail out to all the people in the database (please note: this system should not be used for spam or unsolicited e-mail).

After creating this system you should have enough knowledge to go on and create nearly any type of database enabled site you want to.

MySQL databases have a standard setup. They are made up of a database, in which is contained tables. Each of these tables is quite separate and can have different fields etc. even though it is part of one database. Each table contains records which are made up of fields.

Databases And Logins

The process of setting up a MySQL database varies from host to host, you will however end up with a database name, a user name and a password. This information will be required to log in to the database.

If you have PHPMyAdmin (or a similar program) installed you can just go to it to log in with your user name and password. If not you must do all your database administration using PHP scripts.

Creating A Table

Before you can do anything with your database, you must create a table. A table is a section of the database for storing related information. In a table you will set up the different fields which will be used in that table. Because of this construction, nearly all of a site's database needs can be satisfied using just one database.

Creating a table in PHPMyAdmin is simple, just type the name, select the number of fields and click the button. You will then be taken to a setup screen where you must create the fields for the database. If you are using a PHP script to create your database, the whole creation and setup will be done in one command.

Fields

There are a wide variety of fields and attributes available in MySQL and I will cover a few of these here:
Field Type Description
TINYINT Small Integer Number
SMALLINT Small Integer Number
MEDIUMINT Integer Number
INT Integer Number
VARCHAR Text (maximum 256 characters)
TEXT Text

These are just a few of the fields which are available. A search on the internet will provide lists of all the field types allowed.

Creating A Table With PHP

To create a table in PHP is slightly more difficult than with MySQL. It takes the following format:

CREATE TABLE tablename {

Fields

}

The fields are defined as follows:

fieldname type(length) extra info,

The final field entered should not have a comma after it.

I will give full an example of using these later in the section.

The Contacts Database

The contacts database will contain all the conact information for the people you enter and the information will be able to be edited and viewed on the internet. The following fields will be used in the database:

Name

Type

Length

Description
id INT 6 A unique identifier for each record
first VARCHAR 15 The person's first name
last VARCHAR 15 The person's last name
phone VARCHAR 20 The person's phone number
mobile VARCHAR 20 The person's mobile number
fax VARCHAR 20 The person's fax number
email VARCHAR 30 The person's e-mail address
web VARCHAR 30 The person's web address

You may be wondering why I have used VARCHAR fields for the phone/fax numbers even though they are made up of digits. You could use INT fields but I prefer to use VARCHAR as it will allow dashes and spaces in the number, as well as textual numbers (like 1800-COMPANY) and as we will not be initiating phone calls from the web it is not a problem.

There is one other thing you should be aware of in this database. The id field will also be set as PRIMARY, INDEX, UNIQUE and will be set to auto_increment (found under Extra in PHPMyAdmin). The reason for this is that this will be the field identifier (primary and index) and so must be unique. The auto increment setting means that whenever you add a record, as long as you don't specify an id, it will be given the next number.

If you are using PHPMyAdmin or a management program you can now create this in a table called contacts.

Creating The Table In PHP

The following code should be used to create this table in PHP. Some of the code has not been covered yet but I will explain it fully in the next part.

$user="username";
$password="password";
$database="database";
mysql_connect(localhost,$user,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="CREATE TABLE contacts (id int(6) NOT NULL auto_increment,first varchar(15) NOT NULL,last varchar(15) NOT NULL,phone varchar(20) NOT NULL,mobile varchar(20) NOT NULL,fax varchar(20) NOT NULL,email varchar(30) NOT NULL,web varchar(30) NOT NULL,PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id))";
mysql_query($query);
mysql_close();
?>

Enter your database, MySQL username and MySQL password in the appropriate positions on the first three lines above.

The first thing you must do before you can do any work at all is to connect to the MySQL database. This is an extremely important step as, if you are not connected, your commands to the database will fail.

Good practice for using databases is to specify the username, password and database name first so that if you change any of them at a later date you will only have to change one line:

$username="username";
$password="password";
$database="your_database";

At this point you may be wondering if it is a security risk, keeping your password in the file. You don't need to worry, though, because the PHP scource code is processed aby the server before being sent to the browser so it is impossible for the user to see the script's source.

Next, you will need to issue the command to start a database connection:

mysql_connect(localhost,$username,$password);

This line tells PHP to connect to the MySQL database server at 'localhost' (localhost means the server that the site is running one. Unless you web host tells you otherwise you should use localhost. If you are given a server address (such as sql.myserver.com you should replace localhost with "sql.myserver.com" (including the quotes)) using the username stored in $username and the password in $password.

Before I show you how to work with the database, I will show you one more command:

mysql_close();

This is a very important command as it closes the connection to the database server. Your script will still run if you do not include this command but too many open MySQL connections can cause problems for a web host. It is good practice to always include this line once you have issued all your commands to the database, to keep the server running well.

Selecting The Database

After you have connected to the database server you must then select the database you wish to use. This must be a database to which your username has access. The following command:

@mysql_select_db($database) or die( "Unable to select database");

is used to do this. This tells PHP to select the database stored in the variable $database (which you set earlier). If it cannot connect it will stop executing the script and output the text:

Unable to select database

This extra 'or die' part is good to leave in as it provides a little error control but it is not essential.

Executing Commands

Now you have connected to the server and selected the database you want to work with you can begin executing commands on the server.

There are two ways of executing a command. One is to just enter the command in PHP. This way is used if there will be no results from the operation.

The other way is to define the command as a variable. This will set the variable with the results of the operation.

In this part of the tutorial we will use the first way as we are not expecting a response from the database. The command will look like this:

mysql_query($query);

The useful thing about using this form of the command is that you can just repeat the same command over and over again without learning new ones. All you need to do is to change the variable.

Inserting Data

For this part of the tutorial I will return to the contacts database which we created in the last part. We will now add our first information to the database:

First: John
Last: Smith
Phone: 01234 567890
Mobile: 00112 334455
Fax: 01234 567891
E-mail: johnsmith@gowansnet.com
Web: http://www.gowansnet.com

This will all be put in with one command:

$query = "INSERT INTO contacts VALUES ('','John','Smith','01234 567890','00112 334455','01234 567891','johnsmith@gowansnet.com','http://www.gowansnet.com')";

This may look a little confusing at first so I will explain what it all means.

Firstly $query= is there because we are assigning this to the variable $query (see the section above). The next part:

INSERT INTO contacts VALUES

is quite easy to understand. It tells the PHP to insert into the table called contacts the values in the brackets which follow.

The part in the brackets contains all the information to add. It uses all the fields in order and inserts the information from between the quotes. For example:

John

will be inserted into the 2nd field which, in this table, is the 'first' field.

You may have noticed that you are not inserting any value into the first field in the database (id). This is because this field is going to act as an index field. No two records in the database will have the same ID. Because of this, when we set up the database we set ID to 'Auto Increment'. This means that if you assign it no value it will take the next number in the series. This means that this first record will have the ID 1.

Inputing the data using HTML pages is almost identical to inserting it using a PHP script. The benefit, though, is that you do not need to change the script for each piece of data you want to input and you can also allow your users to input their own data.

The following code will show an HTML page with textboxes to enter the appropriate details:

This page could, of course, be formatted and have other changes made to it. It is just a basic form to get you started. Next you will need to edit the script from last week. Instead of using information to input into the database, you will instead use variables:
$username="username";
$password="password";
$database="your_database";

$first=$_POST['first'];
$last=$_POST['last'];
$phone=$_POST['phone'];
$mobile=$_POST['mobile'];
$fax=$_POST['fax'];
$email=$_POST['email'];
$web=$_POST['web'];

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "INSERT INTO contacts VALUES ('','$first','$last','$phone','$mobile','$fax','$email','$web')";
mysql_query($query);

mysql_close();
?>

This script should then be saved as insert.php so that it can be called by the HTML form. It works because, instead of the data being entered locally, it is being entered into the form and stored in variables which are then passed to the PHP.

You could also add to this script a message confirming the data input. This is basic PHP, though, and you should read the PHP tutorial if you do not know how to do this.

Outputting Data

Now you have at least one record, if not many more, in your database you will be wanting to know how you can output this data using PHP. Before beginning, though you should be familiar with loops in PHP (you can find out about them in the tutorial on Free Webmaster Help) as they are used for this way of outputting data.

The first command you will need to use is a MySQL query made up like this:

SELECT * FROM contacts

This is a basic MySQL command which will tell the script to select all the records in the contacts table. Because there will be output from this command it must be executed with the results being assigned to a variable:

$query="SELECT * FROM contacts";
$result=mysql_query($query);

In this case the whole contents of the database is now contained in a special array with the name $result. Before you can output this data you must change each piece into a separate variable. There are two stages to this.

Counting Rows

Before you can go through the data in your result variable, you must know how many database rows there are. You could, of course, just type this into your code but it is not a very good solution as the whole script would need to be changed every time a new row was added. Instead you can use the command:

$num=mysql_numrows($result);

This will set the value of $num to be the number of rows stored in $result (the output you got from the database). This can then be used in a loop to get all the data and output it on the screen.

Setting Up The Loop

You must now set up a loop to take each row of the result and print out the data held there. By using $num, which you created above, you can loop through all the rows quite easily. In the code below, $i is the number of times the loop has run and is used to make sure the loop stops at the end of the results so there are no errors.

$i=0;
while ($i < $num) {

CODE

$i++;
}

This is a basic PHP loop and will execute the code the correct number of times. Each time $i will be one greater than the time before. This is useful, as $i can be used to tell the script which line of the results should be read. As the first line in MySQL output is 0, this will work correctly.

Assigning The Data To Variables

The final part of this output script is to assign each piece of data to its own variable. The following code is used to do this:

$variable=mysql_result($result,$i,"fieldname");

So to take each individual piece of data in our database we would use the following:

$first=mysql_result($result,$i,"first");
$last=mysql_result($result,$i,"last");
$phone=mysql_result($result,$i,"phone");
$mobile=mysql_result($result,$i,"mobile");
$fax=mysql_result($result,$i,"fax");
$email=mysql_result($result,$i,"email");
$web=mysql_result($result,$i,"web");

We do not need to get the ID field (although we could have done) because we have no use for it in the current output page.

Combining The Script

We can now write a full script to output the data. In this script the data is not formatted when it is output:

$username="username";