Count number of time each string appears in foreach loop

Question

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.

gguney · Accepted Answer · 2022-02-28 06:08:13Z

1

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>';

edited Feb 28, 2022 at 6:08

answered Feb 28, 2022 at 5:44

gguney

2,6711 gold badge17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

User_FTW Over a year ago

Thanks. This solves half the problem. How do I get the counts next to each item?

gguney Over a year ago

I updated my answer, If it solved your problem do not forget to mark it as an answer and upvote it.

Muthu Karthikeyan · Accepted Answer · 2022-02-28 05:41:52Z

0

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

this method is used to filter the duplicate values

answered Feb 28, 2022 at 5:41

Muthu Karthikeyan

11 bronze badge

1 Comment

gguney Over a year ago

The poll_id field is the same, in this case it is 211 and select * part probably throw an error because sql can not aggregate all columns.

Elanchezhiyan P · Accepted Answer · 2022-02-28 06:00:54Z

0

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.

answered Feb 28, 2022 at 6:00

Elanchezhiyan P

869 bronze badges

Comments

NightOwl · Accepted Answer · 2022-02-28 06:04:09Z

0

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.

answered Feb 28, 2022 at 6:04

NightOwl

3594 silver badges20 bronze badges

Collectives™ on Stack Overflow

Count number of time each string appears in foreach loop

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related