Can someone explain the COUNT function to me?

[mickinell]

I have Googled just about everything and I am still very confused by this.

PROBLEM:  I want to display a statistics page like this:

item_id 1 [20]
item_id 2 [15]
item_id 3 [27]

BACKGROUND:

I have two tables, lesson_plan and plan_items.

Lesson_plan is:
| plan_id | ...various others | item_id

Where item_id is an imploded array (like 1,2,14,16).

Plan_items is
| item_id | item _count | ...various others.

I *know* you can count the number of instances of each item_id when they're stored that way.  I just cannot for the life of me figure out the correct syntax for what I'm trying to do.  I've tried to "reverse engineer" it from a site I use and know has this capability, but I don't fully understand what I'm looking at.

Can someone look at my steps below and help explain?

1.  Set item_count to 0 before you start. - I know this works

Koodi [Valitse] Laajenna

$updatequery = "UPDATE plan_items SET item_count = '0'";
$updateresult = @mysql_query($updatequery, $connection);

2.  Select the item_ids.

Koodi [Valitse] Laajenna

$itemquery = "SELECT item_id FROM plan_items";
$itemres = @mysql_query($itemquery, $connection);

3.  Now, this is the point where I get lost.  Here's the snippet I've been working from:

Koodi [Valitse] Laajenna

dbquery("UPDATE ".TABLEPREFIX."fanfiction_categories SET numitems = '0'");
$cats = dbquery("SELECT catid FROM ".TABLEPREFIX."fanfiction_categories ORDER BY leveldown DESC");
while($cat = dbrow($cats)) {
unset($subcats);
$subs = dbquery("SELECT catid FROM ".TABLEPREFIX."fanfiction_categories WHERE parentcatid = $cat[0]");
$subcats = array( );
while($sub = dbrow($subs)) {
$subcats[] = $sub[0];
if($categories[$sub[0]]) $subcats = array_merge($subcats, $categories[$sub[0]]);
}
$categories[$cat[0]] = $subcats;
$countquery = dbquery("SELECT count(sid) FROM ".TABLEPREFIX."fanfiction_stories WHERE FIND_IN_SET('$cat[0]', catid) ".(count($subcats) > 0 ? " OR FIND_IN_SET(". implode(", catid) OR FIND_IN_SET(",$subcats).", catid)" : "")." AND validated > 0");
list($count) = dbrow($countquery);
dbquery("UPDATE ".TABLEPREFIX."fanfiction_categories SET numitems = $count WHERE catid = $cat[0]");

I assume I'd need something like this:

Koodi [Valitse] Laajenna

"SELECT count(plan_id) FROM lesson_plan WHERE FIND_IN_SET('$item[0], item_id)" ...but I don't see how to define $item[0].

I know there's a lot that I don't understand but the problem is that most Web manuals and the resource books I have assume that you understand everything and provide a minimal explanation.  I assume that $item[0] is referring to the array, but I just don't get it.

I have gotten a simple count to work (like the one below), but can't figure the more complex one out.

Koodi [Valitse] Laajenna

$countunits = "SELECT COUNT(unit_id) FROM unit_plan";
$unitresult = @mysql_query($countunits, $connection);
while($row = mysql_fetch_array($unitresult)) {
$unit = $row['COUNT(unit_id)'];
}

[Spaceman-Spiff]

If you're just starting out, I don't recommend using FIND_IN_LIST(). It can be a bit overwhelming to use. Instead, just use simpler COUNT statements:

What SQL COUNT() does is count the number of rows in your select statement.

So if your table contains this:

ID	Name	category
1	Apple	fruit
2	Orange	fruit
3	Carrot	vegetable
4	Broccoli	vegetable
4	Cabbage	vegetable

Query: SELECT COUNT(*) AS count FROM tablename;
Result:
5

Query: SELECT category, COUNT(*) AS count FROM tablename GROUP BY category;
Result:
fruit, 2
vegetable, 3

A bit more advanced, if you want to search only within some categories:
Query: SELECT category, COUNT(*) AS count FROM tablename WHERE category IN ('vegetable', 'fruit') GROUP BY category;
Result:
fruit, 2
vegetable, 3
(any other categories aside from 'vegetable' and 'fruit' will be ignored.