Count() Variable

[D_tunisia]

Wondering if someone can help me out?

I've created a very simple search engine for my site that stores all of the search queries in a MySQL table. For example:

id Query

1 Dog
2 Cat
3 Dog
4 Mouse
5 Dick Cheney
6 Cat
7 Cat

I want to be able to run a query that gives me the results as follows:

Query Amount of times searched

Cat 3
Dog 2
Mouse 1
Dick Cheney 1

I can use the Count() function to look for and count specific results but how can I get Count() results for all possible variables in the table?

Thanks in advance for anyone who can help!

[jdc44]

One way... If I understand your question correctly...

$sql = "select * from query_table";
$results = $myConnectionStuff->query($sql);

$cat_count = 0;
$dog_count = 0;
$mouse_count = 0;

while( $sqlData = $result->fetchRow(DB_FETCHMODE_ASSOC) ){
  switch ( $sqlData['query'] ){
     case 'cat':
         $cat_count++;
         break;
     case 'dog':
        $dog_count++;
     case 'mouse':
        $mouse_count++;
  }
}

echo "Cat: $cat_count<br>
          Dog: $dog_count<br>
          Mouse: $mouse_count";

[jdc44]

Another thought that my prove easier for you...

sql = "select count(*), query from query_table group by query";

query is of course the column with the cat, dog, mouse... in it.

I tested this on a table I use here at work and it works.