I’ve been trying to learn how to use Mysql more effectively in hopes that it will continue to reduce the amount of PHP I have to write to get data from the database. It’s been quite rewarding so far and now I have a new challenge I’m trying to over come.
I have a table called “categories” and a table called “items”. The categories are differentiated (or w/e word I’m supposed to be using) via unique, auto-incremented category_id of int11. The items are differentiated via item_id of int11 as well and the items table has a column called category_id which is a foreign_key? of categories.
So what I’d like to do is select all categories and count the amount of items in that category by doing something like “select * from categories and “item_count” as (select count(*) from items where category_id = xxxx)” in which case the result will yield an array in PHP that gives me access to “item_count” as $array[‘item_count’] as well as all of the columns of the table “categories”.
I’ve tried reading some tutorials from the web and I’m confident I could get this working but that doesn’t mean I’ll understand what it is I’m doing and I find that to be rather pointless and I’ve had such good results with what I learn here on sitepoint.
SELECT category_id, name, COUNT(item_id) AS 'item_count' FROM categories AS c
LEFT JOIN items AS i ON c.category_id = i.category_id
GROUP BY category_id, name
A join will create a record for each combination, so let’s say you have 2 categories and 3 items in each category, so you would get 6 results.
Example:
With group by, you can only select the columns you group on. But you can add new columns so long as they are a result of COUNT(), SUM(), AVG(), MIN(), MAX(), etc.
I used COUNT(item_id), so that it would count the items in the category group, thus providing you with the category id, name and the number of items in that category.
Thank you for the very elaborate explanation. I think I know what you mean by it all, I’ve just go to learn to think in those terms instead of the way I originally figured the query would be written. Would you say this is the only way to do such a query, or just the best?