Select * from item i, item2cat i2 where i.item_id = i2.item_id and i.expires < now() and i.approved = 1 and i2.cat_id = 3
>> This does not sort by membership as mentioned
2. What do you mean by category's children? how is this defined? similarly with q 3 this does not seem to have a parent child relationship. If there is you can not do this with a query (unless you only have a fixed number of levels) you would have to do this with a stored procedure.
>> category table, cat_id and p, where p is the parent forms the relationship. Ok so what if you do know the levels?
select count(*) from item2cat where cat_id = 3
>> This is just for one cat, it does not count children items
Mysql has no default functions to find parents or children - so only if you are using mysql 5 would this work - you would have to have a function where you pass the category id to it and that of the parent you wish to check for - and it recursivley goes through all parents and checks if that parent category falls into the relationship and return true if it does.
>> Sure but you can form the relationship through self joins?
Finally - you should only ask one question at a time on EE - and the mysql space is probably not the best place to ask PHP questions (q 3)
>> The question all involve the same database schema and relate directly to Mysql, I dont see how you consider that PHP? I simply said no PHP code should be needed to process results.
Main Topics
Browse All Topics
by: Raynard7Posted on 2006-11-16 at 13:35:38ID: 17960317
1.
Select * from item i, item2cat i2 where i.item_id = i2.item_id and i.expires < now() and i.approved = 1 and i2.cat_id = 3
where 3 is the cat you want to choose from.
2. What do you mean by category's children? how is this defined? similarly with q 3 this does not seem to have a parent child relationship. If there is you can not do this with a query (unless you only have a fixed number of levels) you would have to do this with a stored procedure.
If you just wanted a count of items you would do
select count(*) from item2cat where cat_id = 3
Mysql has no default functions to find parents or children - so only if you are using mysql 5 would this work - you would have to have a function where you pass the category id to it and that of the parent you wish to check for - and it recursivley goes through all parents and checks if that parent category falls into the relationship and return true if it does.
Finally - you should only ask one question at a time on EE - and the mysql space is probably not the best place to ask PHP questions (q 3)