Showing category content with sub categories content

I have main categories and sub categories.

at cat.php?id=1 page; (id=1 is main category)

I want to show also subcategories content.

My categories table:

id - sub - title

(if sub=0 it means this is main category. if not it's sub category)

My current query is like that;

<?php
    $id =   $_GET['id'];
    $data = mysql_query("select * from content where category=".$id." order by id desc");
    while($r=mysql_fetch_array($data)) 
        {
            echo "$r[id] - $r[title]";
        }
?>

Shows only main category content, but not sub categories content. (at cat.php?id=1)

I think I must connect the categories table again to get sub categories' ID. ???

Surely, I need a new query. I need to get sub=".$id." and list here in the same page.

I stuck.

Solution

So you need to get the Id's of the subcategories as well, you can embed a second query inside of that query (or split it into two separate ones, but that adds another request to the server).

You would put something along these lines:

$data = mysql_query("select * from content where category IN (SELECT id FROM categories WHERE id='$id' OR sub='$id') order by id desc");

using the WHERE ... IN lets you select multiple values from a list like (3,5,2)

So it will look similar to this when the subquery executes:

select * from content where category IN (1,3,2,5,13) order by id desc

WARNING:

You need to sanitize your $_GET['id'] so that no sql injection occurs, this will currently allow sql injection.

Also, the mysql_ functions are deprecated and you need to start using PDO prepared statements, I am not familiar enough with them, but they will do the sanitizing of user input for you.