Select some data while counting entries from a different table?
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.
Thanks for reading and any tips you share.