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
$updatequery = "UPDATE plan_items SET item_count = '0'";
$updateresult = @mysql_query($updatequery, $connection);
2. Select the item_ids.
$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:
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:
"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.
$countunits = "SELECT COUNT(unit_id) FROM unit_plan";
$unitresult = @mysql_query($countunits, $connection);
while($row = mysql_fetch_array($unitresult)) {
$unit = $row['COUNT(unit_id)'];
}
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.