/    Sign up×
Community /Pin to ProfileBookmark

Trying to write a mysql code to show the number of entries in each of the categories.

Hi, I have a database table with name – products -, now I am trying to write a mysql code to show the number of entries in each of the categories in the products table.

Example:

CategoryONE (25)
CategoryTWO (38)
CategoryTHREE (72)

Below is the code I have, so far it not working, I need help.

<?
$table = mysql_query("SELECT count(categories) FROM tb_products GROUP BY id DESC limit 0,1000"); 
while ($registro = mysql_fetch_array($table)) { 
echo "<p>". $registro["categories"] ."</p>
";} 
?>
to post a comment
PHP

2 Comments(s)

Davinci•003 auto-replyCertainly! To achieve the result you're looking for, you can use the COUNT() function in MySQL along with GROUP BY to count the number of entries in each category in your products table. Assuming your table has a column named category that stores the category names for each product, the SQL query would look something like this:

``sql
SELECT category, COUNT(*) AS NumberOfEntries
FROM products
GROUP BY category
ORDER BY NumberOfEntries DESC; -- Optional: Orders the result by the number of entries in descending order
`

This query does the following:
-
SELECT category, COUNT(*) AS NumberOfEntries: Selects the category column and counts the number of rows for each category, labeling this count as NumberOfEntries.
-
FROM products: Specifies the table from which to retrieve the data.
-
GROUP BY category: Groups the result set by the category column so that the COUNT() function applies to each group, rather than the entire table.
-
ORDER BY NumberOfEntries DESC: This line is optional. It orders the results by the number of entries in each category in descending order, so you see the category with the most entries first. If you prefer ascending order or no specific order, you can modify or remove this line.

If your category column has a different name, replace
category` in the query with the actual column name.

Was this helpful?

Copy linkTweet thisAlerts:
@akluch2authorAug 07.2024(updated) — I have resolved it. Thanks. Below is the code:

$sql = mysql_query("SELECT category, COUNT(*) AS NumberOfEntries
FROM tb_products
GROUP BY category
ORDER BY NumberOfEntries DESC");
while ($registro = mysql_fetch_array($sql)) {
echo "". $registro["category"] ." (". $registro["NumberOfEntries"] .")
";}
×

Success!

Help @akluch2 spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 BETA 11.14,
social: @webDeveloperHQ,
});

legal: ({
terms: of use,
privacy: policy
analytics: Fullres
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: Anonymous,
tipped: article
amount: 1000 SATS,

tipper: @aldoushuxley,
tipped: article
amount: 1000 SATS,

tipper: Anonymous,
tipped: article
amount: 1000 SATS,
)...