phpHoo, Part I | 7

The Categories Table

Now that we have our setup routines completed, we can actually start writing some code to work on the data. The two major SQL routines we'll be working with are SELECT and INSERT. The select statement in SQL is the means by which data is retrieved from the database. The insert statement in SQL is the means by which data is placed in the database. Since the lion's share of the work is done by select, we'll go over that first.

Lines 46 through 69 will handle almost all of the select statements we send to the database. Since this class is intended to abstract as much of the grunt work as possible, our "select" method is going to be completely self sufficient. It will send our SQL query to the database, retrieve the values (if any) and always return a multi-dimensional array containing the query results. By always knowing what kind of data we're going to get back, it makes writing our code much easier. Our "select" method is going to accept one piece of data, a pre-written SQL query string. Calls to this method will look something like this:

$sql = "SELECT * FROM Links";
$results = $this->select($sql);

The results of our query ("SELECT * FROM Links") will always return one of two values - FALSE upon failure or empty data set, or, a multi-dimensional array of values. The first two things we do in the "select" method is validate our data. If the SQL query string is empty, we return false immediately since there's nothing to ask the database. Like-wise if the SQL query statement isn't a "select" statement, we don't want to use this method at all so we check for those two things.

We then check to make sure we have a connection to the database on line 54. If we do, we use it on line 56 to actually send the query to the MySQL server and store the results in the '$results' variable. Line 56 does the majority of work in our program, it also happens to be a point of confusion for new PHP programmers, so let's spend some time with it.

56 $results = mysql_query($sql,$conn);

Seems simple enough, doesn't it? Send the SQL query to the server using the connection identifier, and store the results in the '$results' variable.

Oops. It doesn't do that. This is the single most common assumption made by new PHP programmers. The mysql_query() function does send the query to the server, but it does NOT return the results of that query!. More questions are posted to the PHP mailing lists and forums about this particular misconception than any other so it's very important to get this clearly understood. mysql_query() returns a result identifier as a positive integer value. To actually obtain the results of our query, we have to use some more functions that work with this result identifier.

Please be sure you fully understand that 'mysql_query' does NOT return the results of our query before continuing. It's very important. ALL mysql SELECT query operations return RESULT IDENTIFIERS and NOT the RESULTS. It may seem like I'm beating a dead horse here but believe me when I tell you this is the single most common error new PHP / MySQL programmers make.

OK - now that we have this firmly established, the $results of our query is just a result identifier. Since it's a SELECT statement, it must return a positive integer value. If the result identifier is not a positive integer value, then something was wrong with our SQL and the server returned an error. Since MySQL organizes data in Row->Column->Element format, we need to loop through all the returned rows and retrieve the returned column names and elements. The loop on lines 63 through 67 grabs each row of the results (mysql_fetch_array), stores them in a multi-dimensional associative array, clears the results on line 68, and returns the array on line 69. That's all there is to it. The two most common "fetch" routines are mysql_fetch_row() and mysql_fetch_array(). The major difference between the two is that mysql_fetch_row returns an enumerated array of column elements while mysql_fetch_array returns the row in an associative array where each Column name is a key, and the column data becomes that key's values. Since it's always easier to refer to columns by their names, we'll use mysql_fetch_array().

As you can see, retrieving data from the MySQL database is very easy. Placing data in the database is just as simple and uses almost the exact same function. The insert() method on lines 72 through 86 handle the insertion of data in the database. It should be noted here that all MySQL operations (INSERT, DELETE, UPDATE, and SELECT ) use the mysql_query() function. The difference is that where SELECT returns a result identifier, INSERT, DELETE, and UPDATE return TRUE or FALSE only. There is no result identifier for these operations. When we INSERT data into a table with an auto_incremented column, it is sometimes useful to know what the result of the auto_increment was. We can obtain this value using the mysql_insert_id() function, which returns the ID generated for an auto_incremented field after an INSERT operation. We'll use that as our return value. Since the insert ID is going to be a positive integer value, we can use it in a true/false test or use it's value in other queries.

With the completion of our insert method, we've completed all of the MySQL specific routines required to get phpHoo off the ground. The rest of the program will create SQL query statements, send them to "select" or "insert", and make decisions based on the results.

phpHoo is organized by Category, so the first function we'll need is one that retrieves all of the "Top" level categories. The get_Cats() function on lines 92 through 103 is what we need. We'll write this function so that it can handle the retrieval of Top Level categories, or sub-categories. Take a look at line 92.

function get_Cats ($CatParent= "")

This sets up a default value for the functions only argument, $CatParent. The default "empty" value and the logic on lines 94 through 99 allow us to grab all the top level categories by calling the get_Cats() method without any arguments, which results in the following SQL query:

SELECT CatID,CatName FROM Categories WHERE CatParent IS NULL

Remember that the Computers category is Category ID number 2? If we wanted to retrieve all of the sub-categories to "Computers" we would call get_Cats(2), which would result in the following SQL query:

SELECT CatID,CatName FROM Categories WHERE CatParent = 2

Notice how small this function is? By putting the lion's share of the MySQL logic within the Select and Insert methods, we've greatly reduced the amount of work required for other methods that require those operations. Line 100 of get_Cats formats the SQL query string, line 101 sends it to our select() method, and line 102 returns the results. Three lines of code to perform database operations from here on out.

This being the case, we're now going to pick up the pace. One of the things we'll need to do in phpHoo is find out the Parent category of any given sub category (if any). For instance, if we know the ID of the Perl category (Category ID #9) , we need to find out what the Parent CatID is. We can do that with the following SQL query:

    mysql> SELECT CatParent FROM Categories where CatID = 9;
    +-----------+
    | CatParent |
    +-----------+
    |         2 |
    +-----------+

Hmmmm, but what is the parent for CatID 2?

    mysql> SELECT CatParent FROM Categories WHERE CatID = 2;
    +-----------+
    | CatParent |
    +-----------+
    |      NULL |
    +-----------+
    1 row in set (0.00 sec)

OK, now we know that the hierarchy to the Perl sub category is:

    NULL      2       9
    TOP   Computers->Perl

So, given a specific CatID, we now know how we're going to find out where we are in the hierarchy, and what the "bread crumb" trail is back to the "Top". We also know that in order to obtain this information, we submitted the same SQL query more than once, using the results of the previous query to create the next one. That's the definition of a recursive loop. (One iteration of the loop creates the data needed for the next iteration of the same loop.)

Now is the time to show you a bit of "magic" programming that will make you the envy of all your friends. Every time phpHoo runs, it's going to need to find out "where" it is in the hierarchy, which means making multiple SQL queries to determine the trail back to the Top. We'll use two functions to do this. Lines 106 through 112 is the get_ParentsInt() function. This function clears the global variable "TRAIL", re-initializes it, and then calls another method, get_Parents(). The reason we have this little 4 line "magic method" is made clear in the get_Parents() method.

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

URL: https://www.webreference.com/perl/xhoo/php1/