Query only specified number of items from parent/child categories

Question

I'm having trouble figuring out how to query every item in a certain category and only list the newest 10 items by date. Here is my table layout:

download_categories

category_id (int) primary key
title (var_char)
parent_id (int)</pre></code>

downloads

id (int) primary key
title (var_char)
category_id (int)
date (date)</pre></code>

I need to query every file in a main category that let's, say, have 100 items and 5 child categories and only spit out the last 10 added. I have functions right now that just add up all the files so I can get a count by category, but I can't seem to modify the code to only display a certain amount of items based on the date.

I modified the code I am using to count all the files in a category and its children to list the files. The main problem is when I add a LIMIT clause to my SQL, it just limits the results listed from that category, not the overall query.

The code puts all the download categories in an array then loops through each one querying the downloads with that category_id.

Is my best bet is to put the results in an array and then sort it from there?

function list_cat_files($parent, $start) {

    global $menu_array;

    if($start == 1) {

        unset($GLOBALS["total"]);

        $query = mysql_query("SELECT * FROM download_categories");

        while ( $row = mysql_fetch_assoc($query) ) {

            $menu_array[$row['category_id']] =
              array(
                'name'        => $row['title'],
                'parent'      => $row['parent_id'],
                'category_id' => $row['category_id']);

        }
    }

    foreach($menu_array as $key => $value) {

        global $total;

        if ($value['parent'] == $parent) {

            $category_id = $value['category_id'];

            $query1 = mysql_query("SELECT lid AS id, title, date, category_id FROM downloads WHERE category_id='$category_id'");

            while($item = mysql_fetch_array($query1)) {

                $total .= "--- ($item[lid]) : $item[title] <BR><BR>";
            }

            list_cat_files($key, 0);
        }
    }
    return $total;
}

Answer 1

select * from downloads where category_id=X order by date limit 10

Repeat for each category. Unless you want to implement a nested set/modified preorder tree traversal (MPTT): http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Answer 2

Would something like this help? Assuming $categories is an array of the category ids you want.

$query = FALSE;


foreach ($categories as $category){
    $query .= $query ? ' UNION ': '';

    $query .= "SELECT * from `downloads`
        WHERE category_id=$category order by date limit 10\n";

}

// run query here

If it helps, when I deal with complicated category structures, here's how I approach it. I do as many queries as I want to make sorting it out easy for me as the developer. Screw performance. Then I cache the output as needed and use the cached file for public consumption. Categories don't change that often, so those resource hungry queries are only run on occasion when an admin is changing things around, and only by 1 person at a time, rather than everyone who is visiting.