-
Apr 23rd, 2016, 01:16 PM
#1
Thread Starter
Fanatic Member
sql query needed to display products for webshop
im working on a core PHP project to develop online shop
category depth is category->sub_Category->sub_sub_Category
my category table structure is like
id
parent_id
category_name
parent id is 0 for TOP parent category
sub category parent_id is id of its parent
and same for sub sub category
product table structure is
id
parent_id
name
price
description
more_fields
parent_id is the id of category, sub_category, sub_sub_category under which products resides
i hope, i wrote it clearly. And now my question is how can i get all products under category or sub_category
for example
1) when Top category is selected then it should return products that are in itself (top parent categry) its sub_category and sub_sub_category
2) when sub category is selected then it should return products that are in itself (sub category) and its sub_sub_category
-
Apr 24th, 2016, 03:38 AM
#2
Addicted Member
Re: sql query needed to display products for webshop
well i didnt get your category structure, since some numbers is missing but i would recommend you add another field name is_parent so you know which is parent and not, back the the queries
first one
Code:
$query = "SELECT * from product WHERE parent_id='$your_id'";
however for your 2nd query we need to change category table structure :
is_parent in your category table will help you to get the right category EXAMPLE:
if category one is the parent of category two we have is_parent set to 1 for category 2 , category 3 will have is_parent to 2 , you get the point ?
now everytime a user try to access a category , first thing you do is to check if its parent or not ,if its parent then we need parent id and all the category with is_parent set to that or those id which will end up with our parent id (sub sub category) :
Code:
//if its a middle category and id is 2 we want its parent and child category:
$query_1 = "SELECT is_parent FROM category WHERE id=2 // get category 2 parent
$query_2 ="SELECT is_parent,cat_name,id FROM category WHERE is_parent=2 // find everychild
now that u know which category you want
Code:
$query = "SELECT * FROM product WHERE category_id IN ($array)'";
we have an array of ids we want from product
you can avoid the query_1 by having every is_parent number to its id when u generating the menu using <select> <option name=2-1>
and then explode "name" to get category id and parent id
hope i said everything clearly
remember we are now using an array in our where clause , use mysqli or PDO
Goodluck
Body Language tells the truth! even from the grave tsaeb eht morf gninnur ,nwod deaH
All the big things started from little! teef my tsap evom sekans ,duol raor slluB
Lietome.ir
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|