Eighty Years (or so) To The Big Zero

I was working on some coding for a statistics page for a website I’m running. I needed to get the number of titles in the library rated between a certain number and split by category (Fiction, Poetry, Non-Fiction).

Back in the day I would write out the query five times and assign variables for all the statistics but not today. Today I had a vision and it was code sweet!

After brushing up on arrays for a few minutes I harnessed the power of arrays and wrote out this code. (See if you can follow the logic flow. 8^)

function count_number_titles_in_rating_range_and_by_category() {
  global $database_connection;

  /*
  //– Written here for reference
  $titles_by_category = array(
                                    rated_6  => array(f=>”",nf=>”",p=>”",t=>”"),
                                    rated_7  => array(f=>”",nf=>”",p=>”",t=>”"),
                                    rated_8  => array(f=>”",nf=>”",p=>”",t=>”"),
                                    rated_9  => array(f=>”",nf=>”",p=>”",t=>”"),
                                    rated_10 => array(f=>”",nf=>”",p=>”",t=>”")
                                    ); // multi-dimensional array for title ratings by category and TOTAL(t)
  */ 

//– Loop as long as $i is less than 11
  for ($i=6;$i<11;$i++) {
    $query = “SELECT COUNT(tr.titleid),fl.category FROM title_rating as tr, files_in_library as fl WHERE fl.titleid=tr.titleid AND title_rating BETWEEN ‘$i’ AND ‘$i.99′ GROUP BY fl.category”;

    if ($i == “10″) {
       $query = “SELECT COUNT(pr.publishid),pf.categoryid FROM title_rating as tr, files_in_library as fl WHERE fl.publishid=tr.publishid AND tr.rating=’10′ GROUP BY fl.categoryid”;
    } // end check for the last value of 10

    $result = mysql_query($query);

    while($row = mysql_fetch_array($result)) {
      ($row['category'] == “F”) && ($f_count  = $row['COUNT(tr.titleid)']);
      ($row['category'] == “NF”) && ($nf_count  = $row['COUNT(tr.titleid)']);
      ($row['category'] == “P”) && ($p_count  = $row['COUNT(tr.titleid)']);

        $t_count = $f_count + $nf_count + $p_count; // get the TOTAL
        $rated_number = “rated_$i”;
        $rated_titles_by_category[$rated_number][f] = $f_count;
        $rated_titles_by_category[$rated_number][nf] = $nf_count;
        $rated_titles_by_category[$rated_number][p] = $p_count;
        $rated_titles_by_category[$rated_number][t] = $t_count;
    } // while loop

  } // end for loop

  return($titles_rated_by_category);
} // end count_number_titles_in_rating_range_and_by_category()

Notice that $i is a variable that continually rises to give me each of the MYSQL query selects needed to get the values in the database. I also use the ever increasing $i to reference the postion in the multi-dimensional array to set the correct values as they are being pulled from the data base. No if ($i == “6″) { do the titles rated 6 and above }, if ($i == “7″) { do the titles rated 7 and above}, etc etc, checking. It’s all automated. 

In 37 lines I wrote out an MYSQL query that pulls 15 different values and puts them into a multi-dimensional array that can be passed as a SINGLE object. That’s the beauty of this code.  Otherwise that last return would have looked something like this:

return(array($f_rated_6,$p_rated_6,$nf_rated_6,$t_rated_6, $f_rated_7,$p_rated_7,$nf_rated_7,$t_rated_7, $f_rated_8,$p_rated_8,$nf_rated_8,$t_rated_8, $f_rated_9,$p_rated_9,$nf_rated_9,$t_rated_9, $f_rated_10,$p_rated_10,$nf_rated_10,$t_rated_10));

And as an added bonus it only added 0.005 seconds to my overall code processing time. Statistics pages can be load heavy. So far I’m at 0.076 seconds for 62 unique statistical values pulled from the DB.

Ok. This is the geekest post ever.
PS( All tables, databases, variables and values have been modified from their original namings to protect the innocent - ie my server. Don’t be evil. Hacking is bad. M’kay.)

Thinking out loud