How to protect password using htaccess

Although there are many uses of the .htaccess file, by far the most popular, and probably most useful, is being able to relaibly password protect directories on websites. Although JavaScript etc. can also be used to do this, only .htaccess has total security (as someone must know the password to get into the directory, there are no ‘back doors’)

The .htaccess File

Adding password protection to a directory using .htaccess takes two stages. The first part is to add the appropriate lines to your .htaccess file in the directory you would like to protect. Everything below this directory will be password protected:

AuthName “Section Name”
AuthType Basic
AuthUserFile /full/path/to/.htpasswd
Require valid-user

There are a few parts of this which you will need to change for your site. You should replace “Section Name” with the name of the part of the site you are protecting e.g. “Members Area”.

The /full/parth/to/.htpasswd should be changed to reflect the full server path to the .htpasswd file (more on this later). If you do not know what the full path to your webspace is, contact your system administrator for details.

The .htpasswd File

Password protecting a directory takes a little more work than any of the other .htaccess functions because you must also create a file to contain the usernames and passwords which are allowed to access the site. These should be placed in a file which (by default) should be called .htpasswd. Like the .htaccess file, this is a file with no name and an 8 letter extension. This can be placed anywhere within you website (as the passwords are encrypted) but it is advisable to store it outside the web root so that it is impossible to access it from the web.

Entering Usernames And Passwords

Once you have created your .htpasswd file (you can do this in a standard text editor) you must enter the usernames and passwords to access the site. They should be entered as follows:

username:password

where the password is the encrypted format of the password. To encrypt the password you will either need to use one of the premade scripts available on the web or write your own. There is a good username/password service at the KxS site which will allow you to enter the user name and password and will output it in the correct format.

For multiple users, just add extra lines to your .htpasswd file in the same format as the first. There are even scripts available for free which will manage the .htpasswd file and will allow automatic adding/removing of users etc.

Accessing The Site

When you try to access a site which has been protected by .htaccess your browser will pop up a standard username/password dialog box. If you don’t like this, there are certain scripts available which allow you to embed a username/password box in a website to do the authentication. You can also send the username and password (unencrypted) in the URL as follows:

http://username:password@www.website.com/directory/

.htaccess Commands

In the last part I introduced you to .htaccess and some of its useful features. In this part I will show you how to use the .htaccess file to implement some of these.

Stop A Directory Index From Being Shown

Sometimes, for one reason or another, you will have no index file in your directory. This will, of course, mean that if someone types the directory name into their browser, a full listing of all the files in that directory will be shown. This could be a security risk for your site.

To prevent against this (without creating lots of new ‘index’ files, you can enter a command into your .htaccess file to stop the directory list from being shown:

Options -Indexes

Deny/Allow Certian IP Addresses

In some situations, you may want to only allow people with specific IP addresses to access your site (for example, only allowing people using a particular ISP to get into a certian directory) or you may want to ban certian IP addresses (for example, keeping disruptive memembers out of your message boards). Of course, this will only work if you know the IP addresses you want to ban and, as most people on the internet now have a dynamic IP address, so this is not always the best way to limit usage.

You can block an IP address by using:

deny from 000.000.000.000

where 000.000.000.000 is the IP address. If you only specify 1 or 2 of the groups of numbers, you will block a whole range.

You can allow an IP address by using:

allow from 000.000.000.000

where 000.000.000.000 is the IP address. If you only specify 1 or 2 of the groups of numbers, you will allow a whole range.

If you want to deny everyone from accessing a directory, you can use:

deny from all

but this will still allow scripts to use the files in the directory.

Alternative Index Files

You may not always want to use index.htm or index.html as your index file for a directory, for example if you are using PHP files in your site, you may want index.php to be the index file for a directory. You are not limited to ‘index’ files though. Using .htaccess you can set foofoo.blah to be your index file if you want to!

Alternate index files are entered in a list. The server will work from left to right, checking to see if each file exists, if none of them exisit it will display a directory listing (unless, of course, you have turned this off).

DirectoryIndex index.php index.php3 messagebrd.pl index.html index.htm

Redirection

One of the most useful functions of the .htaccess file is to redirect requests to different files, either on the same server, or on a completely different web site. It can be extremely useful if you change the name of one of your files but allow users to still find it. Another use (which I find very useful) is to redirect to a longer URL, for example in my newsletters I can use a very short URL for my affiliate links. The following can be done to redirect a specific file:

Redirect /location/from/root/file.ext http://www.othersite.com/new/file/location.xyz

In this above example, a file in the root directory called oldfile.html would be entered as:

/oldfile.html

and a file in the old subdirectory would be entered as:

/old/oldfile.html

You can also redirect whole directoires of your site using the .htaccess file, for example if you had a directory called olddirectory on your site and you had set up the same files on a new site at: http://www.newsite.com/newdirectory/ you could redirect all the files in that directory without having to specify each one:

Redirect /olddirectory http://www.newsite.com/newdirectory

Then, any request to your site below /olddirectory will bee redirected to the new site, with the
extra information in the URL added on, for example if someone typed in:

http://www.youroldsite.com/olddirecotry/oldfiles/images/image.gif

They would be redirected to:

http://www.newsite.com/newdirectory/oldfiles/images/image.gif

This can prove to be extremely powerful if used correctly.

.htaccess Tutorial

In this tutorial you will find out about the .htaccess file and the power it has to improve your website. Although .htaccess is only a file, it can change settings on the servers and allow you to do many different things, the most popular being able to have your own custom 404 error pages. .htaccess isn’t difficult to use and is really just made up of a few simple instructions in a text file.

Will My Host Support It?

This is probably the hardest question to give a simple answer to. Many hosts support .htaccess but don’t actually publicise it and many other hosts have the capability but do not allow their users to have a .htaccess file. As a general rule, if your server runs Unix or Linux, or any version of the Apache web server it will support .htaccess, although your host may not allow you to use it.

A good sign of whether your host allows .htaccess files is if they support password protection of folders. To do this they will need to offer .htaccess (although in a few cases they will offer password protection but not let you use .htaccess). The best thing to do if you are unsure is to either upload your own .htaccess file and see if it works or e-mail your web host and ask them.

What Can I Do?

You may be wondering what .htaccess can do, or you may have read about some of its uses but don’t realise how many things you can actually do with it.

There is a huge range of things .htaccess can do including: password protecting folders, redirecting users automatically, custom error pages, changing your file extensions, banning users with certian IP addresses, only allowing users with certain IP addresses, stopping directory listings and using a different file as the index file.

Creating A .htaccess File

Creating a .htaccess file may cause you a few problems. Writing the file is easy, you just need enter the appropriate code into a text editor (like notepad). You may run into problems with saving the file. Because .htaccess is a strange file name (the file actually has no name but a 8 letter file extension) it may not be accepted on certain systems (e.g. Windows 3.1). With most operating systems, though, all you need to do is to save the file by entering the name as:

“.htaccess”

(including the quotes). If this doesn’t work, you will need to name it something else (e.g. htaccess.txt) and then upload it to the server. Once you have uploaded the file you can then rename it using an FTP program.

Warning

Before beginning using .htaccess, I should give you one warning. Although using .htaccess on your server is extremely unlikely to cause you any problems (if something is wrong it simply won’t work), you should be wary if you are using the Microsoft FrontPage Extensions. The FrontPage extensions use the .htaccess file so you should not really edit it to add your own information. If you do want to (this is not recommended, but possible) you should download the .htaccess file from your server first (if it exists) and then add your code to the beginning.

Custom Error Pages

The first use of the .htaccess file which I will cover is custom error pages. These will allow you to have your own, personal error pages (for example when a file is not found) instead of using your host’s error pages or having no page. This will make your site seem much more professional in the unlikely event of an error. It will also allow you to create scripts to notify you if there is an error (for example I use a PHP script on Free Webmaster Help to automatically e-mail me when a page is not found).

You can use custom error pages for any error as long as you know its number (like 404 for page not found) by adding the following to your .htaccess file:

ErrorDocument errornumber /file.html

For example if I had the file notfound.html in the root direct
ory of my site and I wanted to use it for a 404 error I would use:

ErrorDocument 404 /notfound.html

If the file is not in the root directory of your site, you just need to put the path to it:

ErrorDocument 500 /errorpages/500.html

These are some of the most common errors:

401 – Authorization Required
400 – Bad request
403 – Forbidden
500 – Internal Server Error
404 – Wrong page

Then, all you need to do is to create a file to display when the error happens and upload it and the .htaccess file.

PHP With Forms

In the last part, I showed you how to use PHP to send e-mail messages using a script. In this part I will continue this and also show you how to use PHP and forms together to make your PHP scripts useful.

Setting Up Your Form

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:

<input type=”text” name=”thebox” value=”Your Name”>

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.

<textarea name=”message”>
Please write your message here.
</textarea>

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.

<input type=”submit” value=”Submit”>

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 <form> tags. They are used as follows:

<form action=”process.php” method=”post”>
Form elements and formatting etc.
</form>

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:

<form action=”mail.php” method=”post”>
Your Name: <input type=”text” name=”name”><br>
E-mail: <input type=”text” name = “email”><br><br>
Comments<br>
<textarea name=”comments”></textarea><br><br>
<input type=”submit” value=”Submit”>
</form>

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:

<?
function checkOK($field)
{
if (eregi(“\r”,$field) || eregi(“\n”,$field)){
die(“Invalid Input!”);
}
}

$name=$_POST['name'];
checkOK($name);
$email=$_POST['email'];
checkOK($email);
$comments=$_POST['comments'];
checkOK($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.

The first part of that script may look a bit strange:

function checkOK($field)
{
if (eregi(“\r”,$field) || eregi(“\n”,$field)){
die(“Invalid Input!”);
}
}

You don’t really need to worry about what this is doing, but basically, it stops spammers from using your form to send thier spam messages by checking special characters are not present in the input which can be used to trick the computer into sending messages to other addresses. It is a fuction which checks for these characters, and if they are found, stops running the script.

The lines:

checkOK($name);

etc. run this check on each input to ensure it is valid.

E-mail With PHP

One of the major uses of a server side scripting language is to provide a way of sending e-mail from the server and, in particular, to take form input and output it to an e-mail address. In this part I will show you how to send e-mail messages using PHP.

The Mail Command

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.

Loops and Arrays

In the last parts of this tutorial I have showed you how to deal with text and variables in PHP and how you can use IF statements to compare them and to make decisions. In this part I am going to show you how to use another important part of PHP, loops.

The WHILE Loop

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<br>”;
}

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:

n
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]<br>”;
++$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.

IF Statements

Over the past two parts I have shown you the basics of text in PHP and how to store it as variables. In this part of the tutorial I will show you how to use IF statements to make decisions in your scripts.

The Basics Of IF

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”;
}

Display Data& Variables

In the last part of the tutorial I explained some of the advantages of PHP as a scripting language and showed you how to test your server for PHP. In this part I will show you the basics of showing information in the browser and how you can use variables to hold information.

Printing Text

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).

For this example I will change the text to the Arial font in red. The normal code for this would be:

<font face=”Arial” color=”#FF0000″>
</font>

As you can see this code contains 4 quotation marks so would confuse the script. Because of this you must add a backslash before each quotation mark to make the PHP script ignore it. The code would chang
e to:

<font face=\”Arial\” color=\”#FF0000\”>
</font>

You can now include this in your print statement:

print(“<font face=\”Arial\” color\”#FF0000\”>Hello and welcome to my website.</font>”);

which will make the browser display:

Hello and welcome to my website.

because it has only been sent the code:

<font face=”Arial” color=”#FF0000″>Hello and welcome to my website.</font>

This does make it quite difficult to output HTML code into the browser but later in this tutorial I will show you another way of doing this which can make it a bit easier.

Update and Delete

Introduction

So far you have learnt how to put information into your MySQL database, view the information in it and select which information you would like to view. In this part I will show you how to do the two final actions, updating your database and deleting records from it.

The Update Script

Last week I explained how to create a link for each record to point to your update script. By using the $id variable you output links which would pass the correct ID to the script so that it can update the database. Using this you can then create the update script, which will actually have two sections to it.

Displaying The Update Page

The first part of the update script uses the single record selection from last week but adds a little HTML to it to make it more useful. First of all, we connect to the database and select the appropriate record.

$id=$_GET['id'];
$username=”username”;
$password=”password”;
$database=”your_database”;
mysql_connect(localhost,$username,$password);

$query=” SELECT * FROM contacts WHERE id=’$id’”;
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();

$i=0;
while ($i < $num) {
$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”);

Space For Code

++$i;
}

Where ‘Space For Code’ is in this script is where the code for the update page will go. This is, in fact, just HTML formatting for the output:

<form action=”updated.php” method=”post”>
<input type=”hidden” name=”ud_id” value=”<? echo $id; ?>”>
First Name: <input type=”text” name=”ud_first” value=”<? echo $first; ?>”><br>
Last Name: <input type=”text” name=”ud_last” value=”<? echo $last; ?>”><br>
Phone Number: <input type=”text” name=”ud_phone” value=”<? echo $phone; ?>”><br>
Mobile Number: <input type=”text” name=”ud_mobile” value=”<? echo $mobile; ?>”><br>
Fax Number: <input type=”text” name=”ud_fax” value=”<? echo $fax; ?>”><br>
E-mail Address: <input type=”text” name=”ud_email” value=”<? echo $email; ?>”><br>
Web Address: <input type=”text” name=”ud_web” value=”<? echo $web; ?>”><br>
<input type=”Submit” value=”Update”>
</form>

As you can see, this code will output a standard form, but instead of having blank boxes like on the form for inserting a new record, this one already has the current information from the database inserted into it. This makes it much more effective for an update script.

Updating The Database

The next stage of this script is to actually update the database. This is a simple operation and just involves a new query for the database:

$query = “UPDATE contacts SET first = ‘$ud_first’, last = ‘$ud_last’, phone = ‘$ud_phone’, mobile = ‘$ud_mobile’, fax = ‘$ud_fax’, email = ‘$ud_email’, web = ‘$ud_web’ WHERE id = ‘$ud_id’”;

This query tells the database to update the contacts table where the ID is the same as the value stored in $ud_id (which as you can see from the form on the previous page was set as the id of the record we are updating) and to set the following fields to the specified values (which were set using the form on the previous page).

This query could then be integrated into a simple script:

$ud_id=$_POST['ud_id'];
$ud_first=$_POST['ud_first'];
$ud_last=$_POST['ud_last'];
$ud_phone=$_POST['ud_phone'];
$ud_mobile=$_POST['ud_mobile'];
$ud_fax=$_POST['ud_fax'];
$ud_email=$_POST['ud_email'];
$ud_web=$_POST['ud_web'];

$username=”username”;
$password=”password”;
$database=”your_database”;
mysql_connect(localhost,$username,$password);

$query=”UPDATE contacts SET first=’$ud_first’, last=’$ud_last’, phone=’$ud_phone’, mobile=’$ud_mobile’, fax=’$ud_fax’, email=’$ud_email’, web=’$ud_web’ WHERE id=’$ud_id’”;
mysql_query($query);
echo “Record Updated”;
mysql_close();

This code would update the database and give the user a confirmation.

Deleting Records

The final part of the contacts database which needs to be created is a page to delete records. As with the Update page, this should have a record ID sent to it in the URL e.g.:

delete.php?id=9

The code to do this is the same as to update the database, except with a slightly different MySQL query. Instead of the UPDATE query you should use:

DELETE FROM contacts WHERE id=’$id’

This would then be used with the connection and confirmation code as above.

Loops

At this time it seems appropriate to mention another use of loops with a database. As well as using a loop to get information from a database as we have before, you can also use loops to execute queries. For example, if you wanted to change all the records in the database with the last name Smith to have the website www.smith.com:

Standard Database Connection Code

$query=” SELECT * FROM contacts WHERE last=’Smith’”;
$result=mysql_query($query);
$num=mysql_numrows($result);

$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,”id”);
$query1=”UPDATE contacts SET web=’http://www.smith.com’ WHERE id=’$id’”;
mysql_query($query);
++$i;
}

mysql_close();

Of course, this could have been achived far easier and quicker using:

$query1=”UPDATE contacts SET web=’http://www.smith.com’ WHERE last=’Smith’”;

and no loop.

Single Records & Error Trapping

Introduction

In the past two parts of this tutorial I have shown you how to take data out of the database and display it on screen. In this part I will enter into the final aspect of this data displaying, selecting one piece of data and stopping errors from happening when you output data.

Error Trapping

By outputting all the information from the database, it is quite unlikely that there will be no data, but if you allow updating and deleting of records, it is certainly a possibility. Luckily, with PHP and MySQL, there is an easy way round this using:

$num=mysql_numrows($result);

where $result contains the result of a query on the database (like selecting all the records). As I expalined before, this will set the value of $num as the number of rows in the result (and it was used in a loop in part 4). Because of this you can make a simple error trap using an IF statement:

if ($num==0) {
echo “The database contains no contacts yet”;
} else {
Output Loop
}

You can expand on this more by making it more user friendly (for example by providing a link to the Add Data page if no contacts exist).

Ordering Data

Not only can you output data based on the contents of a field, but you can also order the output based on a field (for example placing users in alphabetical order). By default, the output from your queries will be in order of the id field, going from 1 upwards. You can sort it on any field, though.

For example, a useful sort would be to place all the users in alphabetical order based on their last name. For those not familiar with standard databases, this would be in Ascending order as it goes from A to Z. (Ascending order is also for 1-10 etc. and descending order provides Z to A and 10-1). To do this you would use the following query:

SELECT * FROM contacts ORDER BY last ASC

You could also use DESC to order the data in Descending order.

More Uses Of mysql_numrows and Sorting

The value you have assigned to $num is very imiportant as, apart from error trapping and loops, it has many other uses. An example of this would be to print out only the last 5 records added to a database. Firstly, they would need to be placed into order based on the id field (as the one with the latest ID would have been added last. This would require them to be in Descending order.

Now you have them in order of newest to oldest but this does not restrict the script to only showing the first 5. To do this, you would need to set your loop to run to 5 instead of $num (as this would only run the loop 5 times so only 5 records would be output).

Of course, before doing this, it would be important to check that $num was greater than 5, as if you ran the loop 5 times and there were only 3 rows you would get an error. This is easy to do though and the following code is an example of the sort of thing you would want to have:

if ($num>5) {
$to=5;
}else{
$to=$num;
}

$i=0;
while ($i < $to) {
REST OF CODE

This code would check if there were more than 5 rows in the database. If there were, the loop would be set to run 5 times. If there were less than 5 rows the loop would run the correct number of times to output the whole database.

The ID Field

If you remember back to creating the database for the contacts at the beginning of this tutorial, you will remember that we included a numerical field called id. This field was set as auto_increment as well as being the primary field. I have already explained how this field is unique for every single record in the database, but I will now take this a stage further by explaining how this can be used to select an individual record from a database.

Selecting A Single Record

At the end of the last part of this tutorial, I s
howed you how to select records from the database based on the contents of partiular fields using:

SELECT * FROM contacts WHERE field=’value’

Now, by using the unique ID field we can select any record from our database using:

SELECT * FROM contacts WHERE id=’$id’

Where $id is a variable holding a number of a record. This may seem to be a little worthless as it is, but you can use this very effectively in a number of different ways. For example, if you wanted to have a dynamically generated site run through a database and a single PHP script, you could write the script to include the database data into the design. Then, using the id field, you could select each individual page and put it into the output. You can even use the page’s URL to specify the record you want e.g.

http://www.yoursite.com/news/items.php?item=7393

And then have the PHP script look up the record with the id corresponding to $item, which in this case would be 7393

Links For Single Records

Using this method of choosing a record using the URL to select the record can be expanded further by generating the URL dynamically. This sounds a bit complicated so I will elaborate. In the contacts script we are writing, I will be showing you how to create an Update page where the user can update the contact details.

To do this, another column will be included in the output column, with an Update link in it. This update link will point to a page allowing the user to update the record. To select the record in this page, we will put:

?id=$id

By getting the id of the record along with the other information when we are outputting the information from the database, this code will create a link which has each record’s ID number in it. Then, on the update page, there can be code to just select this item.