Create HTML graph using PHP w/ MySQL

[DavidNels]

Hey guys.

I'd like to create bar graphs based off of duplicate data retrieved from a MySQL db. I have a general *idea* of how this can be done, but the code itself isn't standing out to me at the moment.

Here's an easy example of something similar to what I'll be doing with this:

Let's say we have a form on a site where users can type a day of the week into an input box. When a user types "Thursday" - for example - and presses Go, PHP creates a row in the mysql table with the timestamp and what the user typed which is, in this case, "Thursday".

Now lets say a few users enter a day of the week into the DB. Our mysql table might have values similar to this:

Monday
Thursday
Wednesday
Friday
Thursday
Thursday
Thursday
Tuesday
Tuesday

So - 1 monday, 4 thursdays, 1 friday and 2 tuesdays.

Now, I would like to SELECT from the DB and draw out a simple CSS bar graph stating the most popular (duplicate) values from the table. It might look something like

Thursday |||||||||||||||
Tuesday ||||||||
Monday ||
Friday ||

Because Thursday had the most occurances, Tuesday the second, and Monday/Friday both had 1 occurance.

This example is the easiest way I could come up with to demonstrate what I'm looking for. Unlike the simplified example I gave, the possible values will not be days of the week, and therefore will not be predictable. Obviously if I was just making a poll for people to choose a random day of the week I wouldn't go about it this way.

I *think* the general way to go about this will involve making some sort of loop and getting variables setup to count dupes and eventually multiply the values of those counters to get percentage values to pass into a CSS div or something to represent the bar graphs.

This sounds kind of tricky, but I think it is overall pretty simple, so let me know if you need any clarification.

Thanks!

David

[LingoOutsider]

You could use this command to identify the numbers of each value...

 $array=array_count_values($SQL[column]); // result ([Thursday] => 15 [Tuesday] => 8 [Monday] => 2 ) 

Then this to order them in size...

 $size=asort($array); //result ([Monday] => 2 [Tuesday] => 8 [Thursday] => 15) 

Then count how many there are...

 $no=count($size); //result 3 in my example 

Then do a while loop to produce the results...

 <?php
$x=0;
while($x<$no){
echo "$size";?><BR/><?php

$x++}?>

Then inside the while loop just place the html code for a table (width= colour= etc).
I've not tested this code but it makes sence to me. You might have to fiddle with the '$SQL[column]' I could be wrong post again if you need help in that area!

[LingoOutsider]

I've never got the hang of swaping keys with values, there are a number of was of doing this try

 array_flip($size) // turns [Monday] => 2 to [2] => Monday 

if your looking to print the key?

[SambaNeko]

Oooor you might be able to save yourself the lines of code by doing this on the SQL level... Like so:

SELECT weekday,COUNT(weekday) AS 'wCount' FROM myTable GROUP BY weekday ORDER BY wCount DESC

This will return a result set with your "weekday" column ("Monday," "Tuesday," etc.) grouped together with one row per weekday, and a column called 'wCount' which has the number of rows that existed for each one. And the ORDER BY clause takes care of sorting them in descending order.

But, whether or not this method works for you may depend on your data set.