phpHoo, Part I | 8 | WebReference

phpHoo, Part I | 8

phpHoo, Part I


Warning, what you are about to see here is "Powerful Stuff" *tm*. As Spider Man always said, with Great Power comes Great Responsibility. Put the coffee cup down, remove the cat from your lap, and concentrate. One false step here and it'll be the last time your database administrator lets you anywhere near the server, so pay attention.

When I first sat down to write phpHoo, I dropped these recursive methods in as a matter of course. It is by -far- the fastest and most efficient means of obtaining relational data and I use it routinely. Then I debated with myself whether to leave it in what is essentially a beginners guide to using PHP with MySQL. I finally decided that the benefits outweigh the potential for disaster when you sit down to write your own programs. Done properly, recursive routines will make your server admin or host smile in relief at how efficient and clean your code is.

Done improperly and you'll be lucky to escape with your skin intact. A faulty recursive query can and will consume all the resources available on the server. All the file descriptors, all the open ports, all the memory and all the processor time. Most admins have resource limits in place to prevent this from happening, but the potential is still there. Keep this in mind before you decide on implementing these types of methods in your own programs.

I ran into a snag in one of my own programs at work one day. When I described the problem I was having, my boss and resident MySQL guru quipped "use a recursive query" and went back to doing whatever it was he was doing. I did a patented Homer Simpson "Doh!, of course" and went back to work. Not 10 minutes later we were implementing stricter resource limits on our development server. Luckily, I hadn't crashed the thing, just robbed it of all it's resources for over 3 minutes. The development server is a dual PII 450 with a gigabyte of RAM. Six lines of code was all it took.

Be careful out there.

On line 117 we start the get_Parents() method. After checking to make sure the CatID isn't a NULL value, we define almost the same SQL query shown above:

SELECT CatID,CatParent,CatName from Categories where CatID = $CatID

We're going to need the raw output from this query, not the associative array returned by the select() method, so we then call mysql_query() directly using this SQL statement. It's the while() loop on the results where the fun begins.

129         while ( $row = mysql_fetch_array($results))
130         {
131             $trail = $this->TRAIL; 
132             $count = count($trail);
133             $trail[$count] = $row; 
134             $this->TRAIL = $trail;
135             $id = $row["CatParent"];
136             $this->get_Parents($id);   
137         }

The first thing we do when we get our result row is grab the current value of the global TRAIL array. Since we just cleared it using the get_ParentsInt() method, it should be empty. We find out how many elements are in the trail with the count() function on line 132. Since an array is zero based where array element number 1 is index number 0, the number returned by "count" will actually point to the next open slot in the array, or to zero if the array is completely empty. Perfect. Line 133 drops the row data into the trail array at the next open slot in the array. It then immediately re-assigns this value to the global TRAIL array on line 134. Line 135 looks at the current returned row, grabs the value for that rows CatParent column and assigns it to the $id variable. Line 136 then calls get_Parents($id).

Wait a second, we're IN the get_Parents() method already!

Yup. You're now using the power of Recursion. This is why we're using a global variable to track the results. Relational databases are perfect breeding grounds for recursive operations. The two methods here, using recursion, can be given any category ID number at any level in the hierarchy and faster than you can say "Bob's your Uncle" the recursive query will roll through the entire database and return an array telling you exactly what the "path" is back to the top!

The dangers: Infinite Loops. Unless you think things out completely at the start, an infinite loop is not only possible, it's the most likely result of a recursive operation. You are deliberately creating a loop that calls itself! ALWAYS provide yourself at LEAST two "outs" from a recursive query - preferably three. In get_Parents(), we have a total of 3 "outs" from the loop:

    119: if( (empty($CatID)) or ("$CatID" == "NULL")) { return false; }
124-127: if( (!$results) or (empty($results)) ... (return false)
    129: while ( $row = mysql_fetch_array($results))

None of these will save you if your logic is flawed however. Recursion is one of the most powerful tools I can show you, and it is by far the most dangerous. See (SIDEBAR)

The rest of the Category methods, get_CatIDFromName() and get_CatNames() are two quick methods we'll need later in our program. The two methods are used to obtain relational data. get_CatNames($CatID) is a multi-purpose method. Given no arguments, it will return an array of all the available Category Names. Given a specific Category ID, it'll return only that Categories CatName. If asked for CatID zero it'll return "Top". This completes the Category section of the MySQL class. Now we can concentrate on functions for use in the Links table.

The Links Table

The methods we use on the Links table are going to look very familiar. There are only two methods we need for the Links table. (Actually 3 if you count the one that adds links to the database, but we'll work on that later.)

Lines 174 through 186 comprise the get_Links() method. Given a specific category ID, it will return an array of all the Links in that category. If the category ID is not specified or is zero, it returns the Links found in the "Top" category. Very simple, very easy. The select() method we wrote earlier is proving it's worth as we write more methods that work on the database. Most of the work is already done for us. New methods just need to create an SQL query statement and send it off to the server.

Lines 188 through 198 comprise the get_CatFromLink() method. Given a specific LinkID, this method will tell us what category ID the Link belongs to. Again, a very simple method made even easier by the select() method we wrote earlier. This method will be used when we search the database. The search routine will return Link data, and we want to be able to tell the user what category that Link was found in.

There are two more methods in the MySQL class, suggest() and search(). Since these two methods won't make much sense to the reader without a better understanding of what's happening in phpHoo, we'll go over phpHoo's code before coming back to these two methods.

phpHoo - Getting the data to the web.

Congratulations! The majority of our work is done. The MySQL database, and the Class we wrote to handle it's data, is the lion's share of the work to be done to make phpHoo a reality.

The first thing to do in our PHP program is include the MySQL class and connect to the database. Lines 1 through 8 handle the inclusion of our MySQL class. Line 4 creates the MySQL "Object", which we assign to the variable '$db'. All future calls to methods or variables within the MySQL class will require the Object Identifier '$db'. Lines 5 through 8 shows an example of this:

    05 if(!$db->init()) {
    06  echo "Well this sucks<BR>\n";
    07  exit;
    08 }

Line 5 calls the init() method, which as you'll recall creates a connection to the MySQL server and selects the phpHoo database. The check here for success is really not needed, since if the connection fails the program will exit. It's still good practice to check for it anyway.

Remember at the beginning of the tutorial I described that PHP is -not- a linear top-down scripting language. All functions must be created before they can be used. So the very next thing we encounter is the function that creates our 'bread crumb' trail at the top of each page phpHoo generates. The breadcrumb() function on lines 10 through 30 tells our visitors where they are within the hierarchy of the phpHoo database.

Lines 12 and 13 are new. Remember variable scoping rules? A function cannot use variables outside it's own scope unless it is given those variables in it's arguments, or obtains them from an outside source. In this case, we tell the breadcrumb() function that $db and $PHP_SELF are global variables.

$PHP_SELF: One of those things that makes PHP programmers happy to work on the web. Unlike Perl and ASP, PHP was written specifically for use on the web. Remember this line from perlHoo?

my $baseurl = '/cgi-bin/';

And these lines from Yahasp?

Const website = ""
Const baseURL = "/yahasp/yahasp2.asp"

Both of these lines hard code the path and filename to their respective programs. This means that if you decide to change the name of the program or change it's location, you'll need to edit the source to make it work again. The PHP developers decided, in their infinite wisdom, that this was a fairly silly idea. PHP programs -always- know how to find themselves again by using the constant $PHP_SELF environment variable. This means that a PHP program, if written properly, can change it's name or location - even move to an entirely new server, and not have to change a single line of code.

Line 16 of breadcrumbs() calls our recursive query get_ParentsInt($CatID). The idea is that, given a specific category ID, the breadcrumbs() function will find the trail back to the top. The category ID submitted to this function is the "current" category the user is visiting. Since get_ParentsInt doesn't return any data, we have to grab the results from the $db->TRAIL variable. The '$path' we get back is an associative array. The while loop between lines 20 and 25 walk through the array and create the HTML needed for each segment of the breadcrumb trail. The "first" key and value we get is the "last" part of the trail, so on line 24 we assemble the trail by recursively adding segments to the beginning of the trail. We return the finished html on line 29. Part of this HTML is the "search" form, which we'll go into detail on later in the tutorial.

Line 32 through 54 is the "start_page()" function. This function is mostly just HTML output - starting the page using a dynamic $title, optional client $msg, and the current category ID number. Using the CatID we call breadcrumbs() from within this function and output the $trail as the last part of the start_page() function.

Lines 56 through 97 , "start_browse()", is the main function in phpHoo. Lines 58 and 59 again make $PHP_SELF and $db available to the function. start_browse() accepts a CatID and calls the two MySQL methods get_Cats($CatID) and get_Links($CatID). Remember that get_Cats actually gets the sub categories for the specified CatID. If CatID is empty or not specified, get_Cats() and get_Links() will both return the "Top" level Categories and Links. Lines 64 through 72 comprise some simple logic checking for "Top" level results.

Lines 74 through 93 look complex, but it's all very simple. Based on the results of the two arrays returned by get_Cats and get_Links, we walk through the returned arrays and output the HTML associated with the Categories and Links for this page. If the two arrays are empty, we won't print anything. Lines 94 through 96 finish off the HTML output by offering the "suggest" link.

Line 100 is where program execution actually begins. Before we can go into much detail here, we need to look at how PHP handles Form data.

Produced by Jonathan Eisenzopf and
Created: July 20, 1999
Revised: July 20, 1999