Combining multiple queries into an output

With a feedback survey they can select bad, neutral or good. I want to compile the bad and good into a table. I am trying to create a tally “table” output from an ajax request to replace a div content. Ideally it will look like:

Experience	Negative	Positive
Cleanliness	12	18
Check-In/Out	4	30
Food	2	12
Staff	1	8

So my mysql has columns for cleanliness, food, etc. Inside is ratings of negative/neutral/positive.

I thought of using a single php script to populate the table, and came up with this so far:

I make two queries:

$pos = $pdo->query("SELECT COUNT(IF(Cleanliness = 'positively', 1, NULL)) AS Cleanliness, 
                        COUNT(IF(Check-In/Out = 'positively', 1, NULL)) AS Check-In/Out, 
                        COUNT(IF(Food = 'positively', 1, NULL)) AS Food, 
                        COUNT(IF(Staff = 'positively', 1, NULL)) AS Staff 
                    FROM responses");

$neg = $pdo->query("SELECT COUNT(IF(Cleanliness = 'negatively', 1, NULL)) AS Cleanliness, 
                        COUNT(IF(Check-In/Out = 'negatively', 1, NULL)) AS Check-In/Out, 
                        COUNT(IF(Food = 'negatively', 1, NULL)) AS Food, 
                        COUNT(IF(Staff = 'negatively', 1, NULL)) AS Staff 
                    FROM responses");

Then fetch them:

$positives = $pos->fetch(PDO::FETCH_ASSOC);
$negatives = $neg->fetch(PDO::FETCH_ASSOC);

Now, they are arrayed but how to assemble both in an output? I can’t Key => Value => Value right?

foreach (array_combine($positives, $negatives) as $pos => $neg) {
    echo "$neg - $pos <br>";
}

So I get my negative and positive but now no row “title”. If I just output the array, I can get the row title but only one of the counts.

Should I scrap trying to combine in a single script and create a “generic” script and POST the column and desired response for just a single count output? That will be a lot of ajax queries hitting the same php script.

Thanks!

SQL fiddle, not sure how to make the right side do what my php is doing with PDO. http://sqlfiddle.com/#!9/37c0c2e/7

Answer

You could obtain the table using a single UNION query

SELECT 'staff', COUNT(IF(`staff` = 'negative', 1, NULL)) negative, COUNT(IF(`staff` = 'positive', 1, NULL)) positive
FROM responses 
UNION 
SELECT 'cleanliness', COUNT(IF(`cleanliness` = 'negative', 1, NULL)) negative, COUNT(IF(`cleanliness` = 'positive', 1, NULL)) positive
FROM responses 
UNION 

SELECT 'food', COUNT(IF(`food` = 'negative', 1, NULL)) negative, COUNT(IF(`food` = 'positive', 1, NULL)) positive
FROM responses 
UNION
SELECT 'check-in/out', COUNT(IF(`check-in/out` = 'negative', 1, NULL)) negative, COUNT(IF(`check-in/out` = 'positive', 1, NULL)) positive
FROM responses

Advertisement

Answer