show all items under subcategories

[OsaMasw]

Yesterday when I was working on php script I noticed that categories and subcategories works seperatly
if I clicked on main category its show all articles under this main category and nothing imported from subcategories, now am working on somthing like this, so what should I do to avoid this mistake ?
someone told me to use inner join but really I don't get the whole picture of this. I can't imagin how I can use this in my SQL command.
for example
Category with id = 1 has subcategories with parent_id = 1 thier ids are : 15 - 20 - 38
if I specified category 1 in my search it will show all items alson in subcategories .

[blocblue]

Post your database schema for the articles, category and sub category tables and the PHP code you already have.

[Biiim]

Left join does that well:

SELECT * FROM `category` `c`
LEFT JOIN `sub_category` `sc` ON `c`.`cat_id` = `sc`.`cat_id`
WHERE `c`.`cat_id` IN(1,3,8)

you can use group by's to count up how many times a field occurs too

eg. how many subcats each category has

SELECT `c`.`cat_id`,count(*) as `num_subcats` FROM `category` `c`
LEFT JOIN `sub_category` `sc` ON `c`.`cat_id` = `sc`.`cat_id`
GROUP BY `c`.`cat_id`;

[OsaMasw]

My db scheme is like

Category table

id - cat_name - parent_id
________________________________
1 - Electronic - 0
2 - TV - 1
3 - Phones - 1
4 - Cars - 0
5 - Something - 1
6 - Used cars - 4
etc ......

and items category

id - item_name - item_cat
____________________________________
1 - Sony - 2
2 - hYNDAY - 6
3 - Toshipa - 2
4 - Samsung - 3
etc .....

now when user click on Electronic category I want to show all items under it and show
Sony - toshipa - samsung

[arti18]

when you insert your sub category in database pass the id's of category too.

[OsaMasw]

when I insert the subcategory i pass the main category with it, like figure above.

[arti18]

can you give us the code how you fatch the category and subcategory from data base.

[OsaMasw]

I fetch the main category by calling all category with parent_id = 0
and pass the id to this function;

function display_child($parent) { 

    $result = mysql_query('SELECT * FROM category '. 

                           'WHERE parent_id="'.$parent.'";'); 

    while ($row = mysql_fetch_array($result)) { ?>
   | <li dir="rtl" class="child" ><a href="cat.php?cat=<? echo $row['cat_id']; ?>"><? echo $row['cat_name']; ?></a></li>  
 <?

    } 

} 

[arti18]

$subcategory="select * from subcategory where category_id='$cat'";

put that code for fatching subcategory and check table name name and field name that you give in your db put that names in query and after that run the code

[OsaMasw]

but i dont have subcategory table, all categories are in the same table.

[arti18]

then you have to create subcategory table and pass category id for particular subcategory it is the easy way and reduce rdundancy from database

[arti18]

as you mentioned above you creat item category table in that table store category id from your Category table and then use this query to fetch data
$subcategory="select * from item category where category_id='$cat'";
and also you have to insert category id in your item category table when you insert data in item category i hope you will get me

[blocblue]

Something like the following should let you find all items in a specified category (including sub categories).

I wasn't sure what your table names were, so I've called them items and categories.

SELECT `i`.*
FROM `items` `i`
INNER JOIN `categories` `c` ON (`c`.`id` = `i`.`item_cat`)
WHERE (`c`.`id` = 1 OR `c`.`parent_id` = 1);

[OsaMasw]

am sorry for late in response , I'll try blocblue code and return with answer.

EDIT: forgive my stupidity in this but here is what I want
when user click on mypage.php?cat=5
which is main category it will show all items under subcategory that has active value = 1
I tried to edit this sql and nothing appeared.

$sql ="SELECT `i`.*
FROM `ads` `i`
INNER JOIN `category` `c` ON (`c`.`cat_id` = `i`.`ad_cat`)
WHERE (`c`.`cat_id` = ".$cat_id." OR `c`.`parent_id` = 1) AND ad_active = 1 ORDER BY ad_date DESC";

[OsaMasw]

any help ?
I think I began to understand the JOIN command, I have vacation today so I'll try this tommorrow

$sql="SELECT *
FROM ads
INNER JOIN category
ON ads.ad_cat=category.cat_id
WHERE ads.ad_cat =".$cat_id."
ORDER BY ads.ad_date DESC";

[blocblue]

Without seeing your full database schema, it's difficult to know whether this is correct. Also, it's advisable to include the table alias with column names referenced in the query.

$sql = "SELECT `i`.*
FROM `items` `i`
INNER JOIN `categories` `c` ON (`c`.`id` = `i`.`item_cat`)
WHERE (`c`.`id` = {$cat_id} OR `c`.`parent_id` = {$cat_id})
AND `ad_active` = 1
ORDER BY `ad_date`";

[OsaMasw]

@blocblue, thanks for the help, I was understanding the code you gave me before
I solved my problem.
I think I should do more practice on JOIN commands.