Counting the top checkbox responses when responses are stored as comma separated string in mysql row (Google Form -> mysql)

user_14544263 0
Viewed 37

A form's checkboxes are stored as comma separated values in a single row as a string in mysql. I cannot control this.

When trying to get the top responses, both finding what the responses are, and how many times they appear, I run this:

$query = "SELECT `answer`, COUNT(*) AS CNT FROM `responses` WHERE `answer` <> '' GROUP BY `answer` ORDER BY CNT DESC ";

The individual comma separated values are not counted, rather the combination of them.

Example, 4 people submit checkboxes A and B. 2 only select A, 1 selects A and B, 1 selects B The result of query above looks like:

A (2)
A,B (1)
B (1)

What I want to achieve is it separating the comma seperated values and yielding:

A (3)
B (2)

I have seen some php explode examples and tried as follows:

        if ($result->num_rows > 0) {
        while($rows = mysqli_fetch_assoc($result)) {
            $ans=explode(',', $rows['answer']);
            foreach($ans as $separated) {
                echo "<div>".$separated."</div>";
            }
        }

The result looks like:

a 
a
a 
b 
b

Now how do I store them to count, and then order from most selected to least?

Is my best route to use explode? Or is there another way that is simpler and more logical?

This link explains the issue, and they have a google side fix, but mysql doesn't have the commands: https://support.google.com/docs/thread/54705332?hl=en

Thanks!

###########################################

As answered below, the new code is:

    $query = "SELECT `ans_4` FROM `responses` WHERE `ans_4` <> ''";
    $result = $conn->query($query);
if ($result->num_rows > 0) {
        $ans = [];
        while($rows = mysqli_fetch_assoc($result)) {
            $ans = array_merge($ans, explode(', ', $rows['answer']));
        }
        $count = array_count_values($ans);
        arsort($count);
        foreach ($count as $key => $value) {
            echo "<div><span>$key</span><span>$value</span></div>";
        }
mysql php
2 Answers

For PDO:

$stmt = $pdo->query("SELECT ans_4 FROM responses WHERE ans_4 <> ''");
$ans = [];
while ($rows = $stmt->fetch(PDO::FETCH_ASSOC))
{
    $ans = array_merge( $ans, explode( ', ', $rows[ 'ans_4' ] ) );
}
$count = array_count_values( $ans );
arsort( $count );
echo( "Largest Concerns" );
foreach ( $count as $key => $value ) {
    echo "<div><span>$key</span><span>$value</span></div>";
}
user_145442630

Obviously this is not the best way to store them, and I'm not sure if you can do it in the query. But you probably want to just select that column:

SELECT `answer` FROM `responses` WHERE `answer` <> ''

Then explode and add to a flat array and count the values:

$ans = [];
while($rows = mysqli_fetch_assoc($result)) {
    $ans = array_merge($ans, explode(',', $rows['answer']));
}
$count = array_count_values($ans);

The $count array should then yield something like:

Array
(
    [A] => 3
    [B] => 2
)

Then if needed to sort descending:

arsort($count);
user_28443190
Related