Count number of time each string appears in foreach loop-CodePudding

I have a database table with some rows...

...where often there will be several repeats of items in the option_name column. As you can see, there are more than one instance of Fish, Cat, Bird and Snake. This is expected and fine.

So I am returning these rows on the front end like this...

$poll_id    = 211;
$poll_table = $wpdb->prefix . "the_votes";
$poll_items = $wpdb->get_results("
    SELECT * 
    FROM $poll_table
    WHERE poll_id = $poll_id
");
echo '<ul>';
foreach ( $poll_items as $poll_item ) {

    echo '<li>' . $poll_item->option_name . '</li>';

}
echo '</ul>';

...which will display like this:

Cat
Fish
Bird
Cat
Snake
Bird
Cat
Cat
Fish
Fish
Fish
Fish
Fish
Fish
Fish
Snake
Cat

But what I actually need, is to make it so that there are no duplicates returned, and a count next to each. So it should show like this instead:

Cat (5)
Fish (8)
Bird (2)
Snake (2)

Thanks in advance.

CodePudding user response：

You should use group by for it:

select option_name, count(id) as count from polls group by option_name;

Your code should look like this:

$poll_id    = 211
$poll_table = $wpdb->prefix . "the_votes";
$poll_items = $wpdb->get_results("
    SELECT count(id) as count, option_name 
    FROM $poll_table
    WHERE poll_id = $poll_id
    GROUP BY option_name
");
echo '<ul>';
foreach ( $poll_items as $poll_item ) {

    echo '<li>' . $poll_item->option_name . '(' . $poll_item->count . ')</li>';

}
echo '</ul>';

CodePudding user response：

SELECT * FROM $poll_table WHERE poll_id = $poll_id group by poll_id

this method is used to filter the duplicate values

CodePudding user response：

In SQL, SELECT column_name, count(column_name) FROM $poll_table WHERE poll_id = $poll_id group by poll_id. you can filter and get the id with their count. Instead of using *, you can use the column name.

Here in jquery, you can get the unique elements by using this $.unique(arr) and you can increment each array value and get the exact count of each value.

CodePudding user response：

Do it using MySQL query itself:

SELECT *, COUNT(option_name) AS option_count FROM $table WHERE poll_id = $poll_id GROUP BY option_name

This will return exactly as you wanted, and you can loop around the results to display it.