The final step, now that we’re storing our data in the database is to display data from the database instead of from the file.
One of the nice things about databases is that they usually will sort the information for you. We’ll be able to throw out the “array_count_values” and the “arsort” lines. These three lines can go away:
$votes = file($filename);
$votecounts = array_count_values($votes);
arsort($votecounts);
Replace them with:
//get all of our votes
$select = "SELECT choice, COUNT(choice) AS count FROM imaginaries GROUP BY choice ORDER BY count DESC";
$votecounts = doQuery($select);
The tough part there is the select query we’re creating. The “GROUP BY choice” part of the query tells MySQL that we only want one row per choice. By asking for the “COUNT(choice)” we’re getting the number of entries for each choice, just as we did originally with “array_count_values”.
We need to modify our doQuery function so that it returns the results of any query we send it. Replace:
IF (!mysql_query($query)):
mysql_problem("Unable to perform query $query");
ENDIF;
with:
IF ($query_result = mysql_query($query)):
WHILE ($row = mysql_fetch_array($query_result)):
$rows[] = $row;
ENDWHILE;
ELSE:
mysql_problem("Unable to perform query $query");
ENDIF;
At the end of the function, add:
return $rows;
The new mysql function in this version of the doQuery function is mysql_fetch_array. This grabs the next row. So we use “WHILE” to loop through the query result for every row. We’re storing it in an array of our own called “rows”. When PHP sees “$variable[]=”, the empty square brackets tell it to append to that array. This means that at the end of that WHILE loop, we have every row of the query in the array called $rows.
Which means we’re pretty much there. Our display loop expects to see a $choice and $count variable, so replace:
FOREACH ($votecounts as $choice => $count):
with:
FOREACH ($votecounts as $votecount):
$choice = $votecount['choice'];
$count = $votecount['count'];
The keys “choice” and “count” are the names of the columns from our query. The array that mysql_fetch_array created used the column names as the keys for that array.