I have what might be a simple question but I can't seem to figure out how to do it. I am trying to write a query that counts the number of unique results per query and stores both the values and the number of times it was a query result to a different database.
Here's an example of what I am basically trying to do...
Say I have a table 'color'
+------------+------------+------------+
| id | color | letter |
+------------+------------+------------+
| 1 | blue | a |
| 2 | blue | b |
| 3 | red | a |
| 4 | red | b |
| 5 | green | a |
+------------+------------+------------+
I have a query that will run multiple times using this table to find the corresponding letter and color:
$query = ("SELECT * FROM colors WHERE letter = '$someletter'");
$query1 = mysql_query($query);
while($row = mysql_fetch_array($query1)) {
$id = $row['id'];
$color = $row['color'];
};
For each query I am writing the $id and $color variables to a seperate log file.
For example, using the above query, $someletter = "a". (call this query #1) The results would be:
+------------+------------+------------+
| id | color | letter |
+------------+------------+------------+
| 1 | blue | a |
| 3 | red | a |
| 5 | green | a |
+------------+------------+------------+
Blue, red, and green would have one result each.
If the query was run with $someletter = "b" (call this query #2) The result set would be:
+------------+------------+------------+
| id | color | letter |
+------------+------------+------------+
| 2 | blue | b |
| 4 | red | b |
+------------+------------+------------+
Blue and red would each get one result.
So the total number of results for both queries #1 and #2:
+------------+------------+------------+
| id | color | totalresult|
+------------+------------+------------+
| 1 | blue | 2 |
| 3 | red | 2 |
| 5 | green | 1 |
+------------+------------+------------+
Basically, I want to figure out a way to get a count of all the unique results for X number of queries. Because this query will be run multiple times with different variables, I would like to store the results in a database that could have a column for color and a column for total results as I have shown above. Also, I am just using the table 'color' as an example and the actual number of unique entries would be in the hundreds, so the query would have to work for that.
I was thinking of some form of COUNT or GROUP but I cant seem to connect the dots.
Any help would be much appreciated.
