Busy Weekdays and Weekend

Nothing much to post except for the fact I'm very busy coping with PHP and MySQL
might as well post it here for my reference in this way I will not have a hard way of searching my documents etc..

Connecting To The Database

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.


HTML Input

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:

First Name:

Last Name:

Phone:

Mobile:

Fax:

E-mail:

Web:

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


nYou 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";
$password="password";
$database="your_database";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM contacts";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "Database Output

";

$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");

echo "$first $last
Phone: $phone
Mobile: $mobile
Fax: $fax
E-mail: $email
Web: $web

---

";

$i++;
}

Formatting Output

In the last part of the tutorial we output a list of all the people stored in the database. This just gave us a very basic output, though and is not particularly useful for a working website. Instead, it would be better if we could format it into a table and display it like this.

Doing this formatting is not particularly complicated. All you need to do is use PHP to output HTML and include your variables in the correct spaces. The easiest way to do this is by closing your PHP tag and entering the HTML normally. When you reach a variable position, include it as follows:



in the correct position in your code.

You can also use the PHP loop to repeat the appropriate code and include it as part of a larger table. For example, using a section of the code from part 4 which looped to output the database you can format it to display it in one large table:










?>

$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");
?>










$i++;
}
echo "

Name	Phone	Mobile	Fax	E-mail	Website
				E-mail < /a>	Website

";

?>

This code will print out table headers, then add an extra row for each record in the database, formatting the data as it is output.

As long as you are familiar with PHP and HTML the code is probably pretty self explanatory but I will just point out the last two lines in the table, for example:

E-mail

This shows one of the useful features of using PHP to include MySQL data as you can use it to output parts of your code and make pages fully dynamic.

Selecting Pieces of Data

As well as showing the whole database, PHP can be used to select individual records, or records which match certian criteria. To do this you must use a variation of the SELECT query. To display the whole table we used the query:

SELECT * FROM contacts

If we just wanted to select ones who had the first name 'John' you would use the following query:

SELECT * FROM contacts WHERE first='john'

As with other MySQL queries, it is almost like plain english. In the same way you could select records based on any field in the database. You can also select ones with more than one field by adding more:

field='value'

sections onto the query.

Although I won't go int
o great depth about it in this section, you can also use variables to give the database criteria. For example, if you had a search form you could get the last name people wanted to search for and store it in a variable called $searchlast. Then you could execute the following piece of code:

$query="SELECT * FROM contacts WHERE last='$searchlast'";
$result=mysql_query($query);

Please note that at the end of the first line there is a ' followed by a " before the semicolon.

Security

At this point it should be noted that you must be very careful in using the technique given above. Without correct secuirty measures, it would be very easy for someone to access data on your server, or even make changes to the database. This can occur if the user sets the variable to a value which edits the SQL string being generated in such a way that it can be used for their own purposes. I won't go into full details here, but there are many websites which give full details (search for 'sql injection attack').

This security hole is easy to plug with a bit of work. Always check input data for invalid chanracters and use PHP's built in functions to remove control characters and HTML code etc. Again, there are many websites which go into this in depth.

* * *

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.

The 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:

First Name:


Last Name:


Phone Number:


Mobile Number:


Fax Number:


E-mail Address:


Web Address:

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.

* * *

Saving Time

When creating complex scripts using databases you will find that the most common thing you are doing is connecting to a database. Because of this, you can actually save time by creating either a username/password file or a connection file. For example for a username/password file you would create a file called:


dbinfo.inc.php

and put the following in it:

$username="databaseusername";
$password="databasepassword";
$database="databasename";
?>

Replacing the appropriate sections. Then in your php files use the following code:

include("dbinfo.inc.php");

or

include("/full/path/to/file/dbinfo.inc.php");


at the beginning. Then, you can use the variables $username, $password and $database throughout your scripts without having to define them every time. Also, if you ever change this information, for example if you move to another web host, there is only one file to change.

You can use the same principal to connect to the database, by putting the connection code in the file, but you must always be sure to close the connection in each file or you may have problems with your MySQL server.

Searching

A limited form of searching can also be performed on your database using a built in MySQL function. This is by using the LIKE function as follows:

SELECT * FROM tablename WHERE fieldname LIKE '%$string%'

To explain furhter, LIKE tells the database to perform its 'searching' feature. The % signs mean that any other data could appear in their place and $string would hold your search string. In this place could be a word or number as well e.g.:

LIKE '%piano%'

which would output any rows with piano in the specified field.

Similarly, you can leave out one of the % signs so that you can specify the position of the string e.g.:

LIKE 'piano%'

Will only output rows where the specified field begins with piano, so:

The piano is next to the table.

Would not show up.

The Finished Script

Throughout this tutorial I have given you pieces of code to make a contacts database script. You can download the full script as a zip file so that you can examine the code (see Related Links).

Conclusion

From this tutorial you should now know the basics of using PHP and MySQL together to create database-enabled websites and programs. Using databases with the web opens up a huge new selection of things you can do and can make a simple website much more powerful, saving time updating the site, allowing user interaction and feedback and much more.

Aice Made Poems:
Day Shift adjustment
Day Shift adjustment Transformation =========> 50%
Day Shift adjustment Transformation =========> 100%
So Much to say but never took the time
Ang Malungkot na Tula (The Lonely Poem)
Don't give up
My Favorite Piece
The main causes of liver damage are:

Cute Post:
Cute Dog Tricks
What is true happiness?
How to Interpret Dreams?

~~~