Results 1 to 6 of 6
Sep 24, 2002, 01:31 #1
MySQL possible to reduce # of queries?
I'm developing my first site with a MySQL-database. It is a shopping site in which all items are sorted into categories. The main categories (cat0) do not contain any items, but the lower categories (cat1 and cat2) do.
In the format (cat0,cat1,cat2):
- (x,0,0) is a main category
- (x,x,0) is a lower category which can contain items and subcategories
- (x,x,x) can only contain items
I have two tables:
-'cat': containing the ID's of the categories, their name and their hierarchy.
-'stock': containing all the items in stock, with a reference 'cat' to the category ID in which the item resides
I need to find out the number of items that are in each category, including subcategories. I have knocked up the following code which works perfectly but I am a little concerned about the number of queries that are involved (over 130 at the moment, and the site will grow considerably).
Is there a way I can reduce the number of queries by joining databases or something else?
Also since I have no experience at all using databases of any kind it is very well possible I have made a serious design error when I created these tables. Is this the way this is normally done, using two tables with one containing a reference to another, or is there a better way?
Thanks for helping!!
$query = 'SELECT ID, cat0, cat1 FROM cat WHERE cat1 !=0 AND cat2 = 0';
$query_result = mysql_query($query);
while ( $row = mysql_fetch_assoc($query_result) )
$query2 = 'SELECT ID FROM cat WHERE cat0 = '.$row['cat0'].' AND cat1 = '.$row['cat1'];
$query_result2 = mysql_query($query2);
while ( $row2 = mysql_fetch_assoc($query_result2) )
$query3 = 'SELECT cat FROM stock WHERE cat = '.$row2['ID'];
$query_result3 = mysql_query($query3);
$cat[$row['ID']] += mysql_num_rows($query_result3);
$cat[$row2['ID']] += mysql_num_rows($query_result3);
Sep 24, 2002, 06:00 #2
- Join Date
- Jul 2002
- Toronto, Canada
- 63 Post(s)
- 3 Thread(s)
> need to find out the number of items that are
> in each category, including subcategories
select cat,count(*) from stock group by cat
> Is this the way this is normally done, using two
> tables with one containing a reference to another
looks fine to me, except for the cat table
the normal way to design a hierarchy is
create table cat
( ID integer
, catname varchar
, parentcat integer )
each subcategory contains a foreign key to its parent
cats at the top of their trees have a null parent key
Sep 24, 2002, 08:21 #3
I see the point in using a field for parentcat instead of putting the complete hierarchy in every key. Hmm should've thought about that, it would have made things easier.
I have been reading about 'GROUP BY' but I don't understand the purpose of it at all. The MySQL manual does not even try explaining it, it's probably assumed to be common knowledge. Does it return a group of results in one row?
I'm going to experiment with this function and try to figure it out.
And the manual mentions this about COUNT(*):
COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether they contain NULL values.
Sep 24, 2002, 08:49 #4
Ok I still don't really get the exact meaning of the GROUP BY thing, but I have managed to get some results from it. It does not seem to be possible to use mysql_fetch_assoc() because I don't know by what name I can access the count, $row['COUNT'] doesn't work.
I do seem to have the solution to my endless mysql calls in sight, since it seems I can lose the inner loop of my original code using this function. I'll try to work it out. Thanks r937!!
Sep 24, 2002, 08:57 #5
- Join Date
- Aug 1999
- Lancaster, Ca. USA
- 1 Post(s)
- 0 Thread(s)
Assign your count an alias to use it elsewhere...
ie: SELECT count(*) AS total FROM stock
This would give you the total product count. To access it you would use $result['total'], just like any other column.
COUNT returns all rows. If your field allows NULL as a value, it will count them as valid fields. The trick is to do the count either on the entire record [count(*)] or on a field where you no that a NULL isn't accepted like the primary index [count(catid)]. Using COUNT on the record as a whole is generally faster than using it on a single field in the record.
Sep 25, 2002, 08:40 #6
I'm starting to understand it... when you query a "COUNT(*) GROUP BY cat" you get the number of individually existing cats back, with a count of the total amount of times this cat exists in the table. I can use the 'AS' to get an associative array back.
So basically I can get the same results using only two queries: one to get all the categories (from the cat table) and one to get the count of items that share the same cat (from the stock table); put both results in an array and do my calculations with the arrays instead of querying all the time. This would be a major improvement
Thanks for the insight!!