Fetching a Row

We can fetch a row three different ways. Essentially each way retrieves a single record, the one being pointed to, and returns it in a unique structure.

PHP

MySQL_fetch_row(Result);

This returns the data in a simple array. Each column's value is put in a separate element of the array. The array is zero-based. The range is 0 – Number_of_columns – 1.

PHP

MySQL_fetch_array(Result);

This returns an array. But this array is different from the array that we just fetched in the previous command. This array is an associative array. The key to access any value is the name of the column that the datum was stored in. In addition we can use the PHP array-accessing features (show, list, each) to move through this array, which, since a meaning is attached to each variable, can then be used easily.

PHP

MySQL_fetch_object(Result);

There is only one major difference between this command and the one preceding it. This one returns an object. Therefore, all the data is stored as a property with the name of the column being the name of the property. Also, unlike the associated array that was fetched before, this data cannot be accessed by referring to the offset of data. And there is no way to discover the column names if you do not already know them.

Although we did not create it and cannot directly access it, we can easily move the pointer to any row we choose. There are two functions we can use to do this. The MySQL_result command returns the value of any single field of the result. The Row is the number row of the result. The Column parameter is the zero-based column.

PHP

MySQL_result(Result, Row, Column);

This command moves the internal pointer within the result object to the row after Row. Any of the above commands that fetches a row affect the row immediately after the row that the retrieved field was in.

A second command to move the internal pointer expressly does solely that.

PHP

MySQL_data_seek(Result, NewRow);

This command moves the pointer to NewRow. Since all fetch commands retrieve rows, no column definition is neccessary.

Measuring Results

We can count how many rows were returned by MySQL_query.

PHP

MySQL_num_rows(Result);

This command returns the number of rows in the result—very useful information for setting up loop exit conditions.

Releasing Memory

Depending on how much information a result is storing and depending on how many results we have, we may start having memory problems. We can free up some of the results we are not using anymore for garbage collection. Garbage collection is when the program disassociates chunks of RAM with variable names so that RAM can be conserved.

PHP

MySQL_free_result(Result);

Elusive, erratic, and evil bugs may be the result of running out of memory. Fortunately, on today's modern machines, it is not as big a worry as used to be.

We have to be careful, because once a result is freed, it can never be accessed again. Once something is deleted it is gone, after all. To attempt to use the results will cause errors to occur.

The changes we make to the codebase in this chapter are based on the work of Chapter 10, where the following code appeared:

PHP

<?
class Question {
   var $idkey;
   var $Q;
   var $A= array();

Fakedata exists only until the MySQL database is working. It is, so it will disappear in our new code.

PHP

   var $fakedata= array (
             . . . lots and lots of hardcoded data . . .
   );

The "database" is queried during this object constructor, which is replaced later on in this chapter.

PHP

             function Question( $idkey ) {
                   if( !$this->fakedata[$idkey][Q])
                             return null;
                   $this->Q  = $this->fakedata[$idkey][Q];
                   while ( list($key, $choice)
                      = each($this->fakedata[$idkey][A] ) )
                             array_push( $this->A, $choice);
                   $this->idkey = $idkey;
                   return $this;
      }

This function creates all the necessary XML output. It remains unchanged.

PHP

function qzml(){
  if( !$beenhere++ ){
    echo  (   '<?xml version="1.0"?>'                   ."\ r\ n");
    echo  (      '<Question UIN = "'.$this->idkey.'"  >' ."\ r\ n");
    echo  (         '<Q>'.$this->Q. '</Q>'               ."\ r\ n");
    while ( list( $n, $answer ) = each($this->A) )
      echo(          '<A>'.$answer.'</A>'                 ."\ r\ n");
      echo  (     '</Question>'                           ."\ r\ n");
      }
   }
}
       srand(microtime()*1000000);     // seed random number generator
       $q= new Question( rand(0,5) );  // create a question
       $q->qzml();                     // display as xml
?>

Now we replace the object's constructor with one that queries our database. Most of it is straightforward code. A large conceptual problem, however, is choosing which question we want from the database.

There are several ways that we could use to decide which question we will withdraw from the Question table. One would be to plod through the questions sequentially, using a persistent variable to keep counting through all the QuIDs. We could even store this counter in its own MySQL table. If we wanted to be even fancier, we could keep a separate persistent variable for each of the users. But let's keep it simple and just select a random question regardless of past question history.