I'm investigating how to easily figure out the number of duplicates in php and mysql.

id name price
1 Apple 300 yen
2 Grape 500 yen
3 Apple 300 yen
4 Cucumber 100 yen
5 Apple 300 yen
6 Cucumber 100 yen
7 Pear 400 yen

If i have such a database, how do you count the number of apples, grapes, cucumbers and pears?
If the number is small

$count = 'SELECT COUNT (*) FROM db_name WHERE name = "apple"';
$count = $pdo->prepare ($count);
$count->execute ();
$apple_count = $count->fetchColumn ();

I thought it would be good to ask in this way, but I don't know how to solve it when there are nearly 20 products.

  • Answer # 1

    SELECT count (id) as cnt, name FROM table GROUP BY name
    You can get the number of each by executing


  • Answer # 2

    The grapes and pears do not overlap, but is it better to display them with a quantity of 1?

    SELECT name, count (*) as cnt FROM table GROUP BY name HAVING count (*)>1