Greetings!
I'm building a PHP table from data I'm pulling from a MySQL database. I'm querying data from two tables where the common field between them is a customer ID. Table A contains customer ID and a customer score. Table B contains customer ID and a mailing code. The easy part is querying the data in such a way as to show me how many customers with a certain mailing code have a particular score. So, here's my query:
select score,count(score) as qty from custscore where customer_id in (select customer_id from all_buyers where keycode = '12345678') group by score
The above query gives me the following information:
- # of Customers with a score that equals 0 is 4
- # of Customers with a score that equals 2 is 2
- # of Customers with a score that equals 3 is 3
- # of Customers with a score that equals 4 is 8
- # of Customers with a score that equals 5 is 18
Notice that I get no results for scores of 1 and 6-10 (10 is the highest). Here's where I'm getting stuck. I want to display that information in a simple table that shows the mailing code down the X axis and the scores across the Y axis of the table. So for the example above please note the following image
The field headings across the top of the table represent the scores from 0 to 10. What I need to do is match up the scores found in my query with the appropriate score cell in the table and place the associated quantity in the matching cell. For instance, since I know that 4 customers had a score of 0, I want to place that number in the column marked "0". But where it gets fuzzy is how do I compensate for times when I have no matching score and fill in those empty cells? I know I could simply loop through the data results (and have successfully done that) and it would put the quantities in consecutive cells but I need them to populate the cell where the score and the column heading match. Maybe I'm just having one of those days but I'm feeling quite stumped. Any help is great appreciated! Let me know if you need further clarification or info.
Thanks...
