What I'm trying to do is create a simple array that takes the sum of all my advertising clicks and gives me back a count for a simple ad tracker I'm writing. In another post on Stackoverflow read the following solution, but its not elegant.
Basically we are currently running 6 ads outlined below and we just want the count of those nearly organized into an array so we can do a do loop to show the ads. Naturally it makes no sense to hard-code the count in SQL because we'll be adding more ads later. I thought about creating a do loop to go thru each count(*) and assigning it to a varible but that would cause us to hit the mysql server too many times than necessary.
Is there a simple way to transform this SQL statement into something we could easily generate in PHP.
i.e. $ads = array ('money1','money2','money3','st1','st2','st3')
how would you get the count back out? is this the best way?
mysql> SELECT ad,
SUM(ad = 'money1') AS ct,
SUM(ad = 'money2') AS ct,
SUM(ad = 'money3') AS ct,
SUM(ad = 'st1') AS ct,
SUM(ad = 'st2') AS ct,
SUM(ad = 'st3') AS ct
FROM bloggingengineOptins
GROUP BY ad;
this is the output
+--------+------+------+------+------+------+------+
| ad | ct | ct | ct | ct | ct | ct |
+--------+------+------+------+------+------+------+
| NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| | 0 | 0 | 0 | 0 | 0 | 0 |
| 7230 | 0 | 0 | 0 | 0 | 0 | 0 |
| money1 | 11 | 0 | 0 | 0 | 0 | 0 |
| money2 | 0 | 12 | 0 | 0 | 0 | 0 |
| money3 | 0 | 0 | 10 | 0 | 0 | 0 |
| st | 0 | 0 | 0 | 0 | 0 | 0 |
| st1 | 0 | 0 | 0 | 3 | 0 | 0 |
| st2 | 0 | 0 | 0 | 0 | 4 | 0 |
| st3 | 0 | 0 | 0 | 0 | 0 | 4 |
+--------+------+------+------+------+------+------+
10 rows in set
mysql>
After applying the 'fix' below the table now looks like this:
SELECT ad, count(1) ct FROM bloggingengineOptins GROUP BY ad;
+--------+-----+
| ad | ct |
+--------+-----+
| NULL | 13 |
| | 915 |
| 7230 | 1 |
| money1 | 11 |
| money2 | 13 |
| money3 | 10 |
| st | 1 |
| st1 | 6 |
| st2 | 11 |
| st3 | 6 |
+--------+-----+
10 rows in set
Perfect output
SELECT ad, count(1) ct FROM bloggingengineOptins GROUP BY ad;