PHP Tutorial – Lesson 13 Looping, looping, looping, looping, looping, looping, looping, looping…
February 17th, 2006There are 4 main concepts that you have to understand inside and out to be a good programmer.
- Variables
- Arrays
- Choices (if statements)
- Looping
We have talked about variables (those named containers where we can store information) and we have talked about choices. We have also touched on arrays (remember $_GET and $_POST?). Remember arrays are variables that have multiple slots and use the [] to designate wich slot to set or get.
Now, all of this is very nice, but it is the looping where computers really show their stuff. The ability for a computer to do something repetetavly as fast as it can is where computers flex their muscles. We call these chucks of repetative code loops. Loops can come in many different variations, but they all ultimatly repeat the same steps over and over again until they are signaled to stop. You will see that loops and arrays can work very closely together to process large ammounts of data very quickly.
In this example, we aren’t just going to show you the standard looping demonstrations. We are going to jump right in and show you a real world example! The idea here is to get you up and running fast so that you can do something useful. As you become more familliar with the looping structures, you can branch off and try things on your own. Soon you will look at code and you will read it like a book.
For our real world example, we will do a database query and show you the results of that query. We will use the SELECT statements from the previous article to display a list of users.
One thing to note before we continue though is that this code does not have any authentication built in. If you take this demo and point it to a real table with real information, you could be exposing that information to people who shouldn’t see it! That is why we use a demo table that has no real information in it.
To get information from the database and display it on the screen, we will have to do several steps.
- Connect to the database
- Create your SQL statement
- Send your SQL statement to the database
- Start a loop that will read each row returned from the database
- – In the loop, print the information for this row to the screen so the user can see it
- Close the database connection
- Done! The page runs out of code and the user sees the page
For this example, create a php file called user_list.php and start filling it with the following…
Step 1 – Connect to the database
One of the cool things about using a real database server is that it has all sorts of security built in. You will need to use your database user name and password. This may be the same as the user name and password you use to login to your account. Best practices say that you should create a new account specifically for the web application that can only do what it needs to (unlike your account which can do most anything to the current database). In most ISP setups, you can not create a new account directly, you will need to use the provided control panel to do so. Instructions will vary depending on the control panel software you use, so I will leave this as an exercise for you. For our example, if your current username and password work, then we can just use that.
<?php
mysql_connect("localhost", "user", "password");
?>
The mysql_connect function requres three parameters. The first is the server to connect to. This is a host name or an IP address. Since the PHP script actually runs on the web server itself (and not on the browser viewing the page) the address should be relative to the web server. In many ISP configurations, the database software is running on the same box, so localhost will work for the address as localhost always means “me” or the current box. It is a special loop back address present on any machine with the TCP/IP protocol installed.
The second and third parameters are the user name and password that is required to connect to the database. This is important to keep unwanted users from connecting and looking at or changing your data.
Finally, there are some optional parameters available. We won’t talk about them here, but you might want to look them up in the PHP documentation (http://www.php.net).
Creating an SQL statement
Once you have a connection to the database, you can send it requests. These requests are formulated using the SQL langauge. We have already covered the core four statements (INSERT, UPDATE, SELECT, DELETE). If you are this far in the tutorial series, you should have even practiced with sending these statements to the database via a GUI interface (PHPMyAdmin or SQLYog). What we want to do now is have our program (PHP script) create an SQL statement and send it to the database.
If you remember back to our lesson on variables, we always put “s around values that are not numeric in PHP. PHP itself doesn’t understand SQL and therefore it considers SQL statements data. Anything that is data (and isn’t a number) needs to be surrounded by “s. In PHP, you could surround data with single ‘s, but that has a slighly different meaning, so generally we always use double “s. For this reason, we generally use single ‘s in the SQL statement itself. Lets illustrate the point so that you see why we do things this way.
DO NOT PUT THIS SNIPPET INTO YOUR PHP SCRIPT
<?php $variable = 'Don't do that!'; ?>
In the above snippet, that is PHP code. Notice that I used the single quote to surround the value. Do you think that this will work? Try it out and see. You will get an error.
PHP sees the first quote and say to itself “this is data, I can pretty much ignore it”. Once it sees the ending quote, it says “ok, the data is done, now the next stuff should be valid PHP code”. The problem is that the single quote is also what we call an apostrophe. The computer is dumb and won’t try to figure out if the next quote is a valid punctuation in the data where it resides, it will simply say, “ahh, the quote is closed, now run PHP code!”. What you get is a PHP engine try ing to understand what it should do with (t do that!). That isn’t a PHP command and therefore messes up the script and causes the error. If we change this example just slightly, it will run just fine.
DO NOT PUT THIS SNIPPET INTO YOUR PHP SCRIPT
<?php $variable = "Don't do that!"; ?>
By using the double quote, PHP won’t end the data until it encounters another double quote. Whichever quote it starts with, it will expect the same type of quote to end with. This allows you to have all the single quotes inside double quotes that you want!
The point of this is that SQL statements themselves need quotes also for their values. Thus, we use double quotes for PHP data and single quotes for SQL data, and everyone is happy!
Here is an example that will request all the users from the database. Put this in your php script under the mysql_connect statement (between the open and close PHP tags).
$sql = "SELECT * FROM mytable";
It is mostly that simple.
Send your SQL statement to the database
Now that we have created our SQL statement, it should be ready to send off to the database server. Generally when you run a SELECT statement, you want a list of matches back. This comes in the form a record set. It is just a bunch of rows. Here is the code to send the statement to the server and get back a recordset. Add to your PHP script under the last statement ($sql = …).
$rs = mysql_query($sql);
The $rs varaible will hold the recordset when the database gets done. The mysql_query function requires one parameter which is the SQL statement. Our $sql varaible holds that statement (look at the previous line). It is nice that we don’t have to worry about sending packets on the network or parsing responses. The mysql libraries wrap all this up in a few easy to use function calls. Note that we have left out error checking in the intrest of keeping things simple. As the tutorials progress, we will start to add that back in so that you can see what proper error checking should look like.
Start a loop that will read each row returned from the database
Now that we have our recordset, we can start getting the records out of it. There could be one record, or there could be thousands. You don’t want to write the same code over and over again to exrtract the records. Instead, we use a loop. The loop will grab a record and display it. Once it runs out of records, it will quit. Looping is great and is one of the things that computers do best! Put this code under the last statement.
while ($record = mysql_fetch_array($rs))
{
}
This is a while loop. It continues to loop until the expression is false. The expression is the part between the initial ( and ). The expression in this case evaluates the $record variable. When you tell mysql to fetch a row from the recordset (mysql_fetch_array($rs)) you are returning an array of columns for the current row. This is great as an array of columns is easy to work with. When the recordset runs out of rows, the $record variable will be equal to false. Since the expression for the while loop is based on evaluating the $record variable, the false value will cause the while loop to quit looping (it only loops while the expression is true!). Boolean is weird (that is true/false) in that zero is false and anything else is true. We take advantage of this by allowing our loop to continue as long as there is a valid array of columns, but when we run out of rows, instead of a valid array of columns, we get a false value.
In the loop, print the information for this row to the screen so the user can see it
Running this script now will show you nothing. We didn’t bother to print anything! We can display information back to the user by printing during each iteration in the loop. Replace the previous snippet of the loop with this one.
while ($record = mysql_fetch_array($rs))
{
print "hi";
}
This loop will print “hi” as many times as you have records in your table. Go ahead, give it a try. Notice how it just prints “hi” one right after the other.
Now, lets try it again with information from our recordset. Again, replace the previous snippet with this one.
while ($record = mysql_fetch_array($rs))
{
print $record["user_name"];
}
This will print a list of users. We didn’t use any HTML to make it look nice so again, just like with the previous snippet, it all prints on the same line. Now lets make it look a little nicer. This is where we start adding HTML tags to our print statements. Since HTML is not PHP, we need to put it all in quotes just like everything else. Replace your last loop with this one.
while ($record = mysql_fetch_array($rs))
{
print "<li>" . $record["user_name"] . "</li>";
}
This will use the HTML LI tags to display each item as a list item. This creates that nice dot and puts each item on a seperate line. This makes it a bit more readable, but it can’t do much. One of the cool things about web pages is that you can create a hyperlink. This means that when some one clicks that link, the browser will go to the specified page. To create a link, all we have to do is place the appropriate tags and the target pages. Here is another revision of the loop with a link that will take us to a different page. This page will display the details of the specified user. We will create the target page in the next tutorial.
while ($record = mysql_fetch_array($rs))
{
print "<li><a href='user_details.php?id=" . $record["id"] . "'>" . $record["user_name"] . "</a></li>";
}
This final version of the loop renders the list of users and turns them into hyperlinks. After the next tutorial, you will be able to click the user and have it display their details. For now, clicking the link will give you a page not found error (we haven’t created the target page yet!).
Close the database connection
Once you are done, it is a good idea to close the database connection that you have opened. Here is a complete copy of the script including the final close statement.
<?php
mysql_connect("localhost", "user", "password");
$sql = "SELECT * FROM mytable";
$rs = mysql_query($sql);
while ($record = mysql_fetch_array($rs))
{
print "<li><a href='user_details.php?id=" . $record["id"] . "'>" . $record["user_name"] . "</a></li>";
}
mysql_close();
?>
Old MySQL API vs the new MySQL API
MySQL recently released version 5 of their database server software. This version included many new features including views and stored procedures. The makup of a stored procedure is basically a function call to the database. As such, many of the pieces of information that you used to put into an SQL string can be passed as a parameter now. The major difference is in how you call these stored procedures from your code. The new MySQLI interface supports all the new features while the old MySQL API only supports the old way. In our exercises, we are using the old API. Many ISP’s don’t have support for MySQL 5 yet and you may have to write your code using the old API, but it is good to know what is coming in the near future and be prepared for it.
Give this script a try. We may come back and modify it later. In our next tutorial, we will write the user_details.php script to finish illustrating the point on how to transition from the listing page to the details page.
Ray Pulsipher
Owner
Computer Magic And Software Design
