Most efficient way to query the newest 20 posts per category on the sme page

I am using PHP and mySQL. I have a table of photographs. in the photographs table I have: a link to the photo, a category_id, date.

What would be the best way to list all the categories on the page with the newest 20 photos under each?

Right now I am selecting all the photos and then sorting them out after in PHP. If there gets to be 500 photos in one category this would seem very inefficient. Any better ideas for the SQL end of it?

The only other way I thought of was to loop a 20 limit query for each category, but if there are 100 categories that seems even worse!

pseudo output

[category_list] => {
    [0]=> {
        'category_title' => 'photos at sunset',
        'posts' => {
            [0] => {
                'photo_link' = '1.jpg',
            }
            [1] => {
                'photo_link' = '2.jpg',
            }
        }
    }
    [1]=> {
        'category_title' => 'photos at sunrise',
        'posts' => {
            [0] => {
                'photo_link' = '1.jpg',
          开发者_运维百科  }
        }
    }
}

pseudo code

$query =  
"
SELECT 
    photographs.category_id, photographs.photo_link, categories.title
FROM 
     photographs
INNER JOIN 
     categories
ON 
     category.id = photographs.categories.id
ORDER BY
     category.id DESC
";

$result = $this->pdo->prepare($query);
$result->execute();

while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    $post[] = $row;
}
$result = null;

$count = sizeof($post);
//get a list of the categories
for($i=0; $i < $count; $i++) { 
    $categories[$i] = $post[$i]['title'];
}
$categories = array_unique($categories);

//sort categories alphabetically
sort($categories);

//add the newest 20 photos to each category
$categories_count = count($categories);
$posts_count = count($post);
for($i=0; $i < $categories_count; $i++) { 
    $category_list[$i]['post_count'] = 0;
    for($k=0; $k < $posts_count; $k++) { 
        if ($categories[$i] == $post[$k]['category_title']) {
            if ($category_list[$i]['count'] == 19) {
                break;
            }
            $category_list[$i]['category_title'] = $post[$k]['category_title'];
            $category_list[$i]['post'][] = $post[$k];
            $category_list[$i]['post_count']++;
        }
    }
}

It can be done in a single query.

Assuming this is the table schema:

CREATE TABLE `parkwhiz_demo`.`test` (
`photo_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`category_id` INT UNSIGNED NOT NULL ,
`date` DATETIME NOT NULL
) ENGINE = MYISAM ;

You can get an ordered list of the 20 most recent photos per category with this query:

select photo_id, category_id, date
from test
where (
   select count(*) from test as t
   where t.category_id = test.category_id and t.date >= test.date
) <= 20
order by category_id, date desc;

The PHP loop to create something similar to your desired array structure is:

$output = Array();
$prevRow = false;
$i=-1;
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    if (!$prevRow || $row['category_id'] != $prevRow['category_id']) {
        $i++;
        $output[$i]['category_id'] = $row['category_id'];
        $output[$i]['posts'] = Array();
    }

    array_push($output[$i]['posts'], Array('image_id'=>$row['image_id']));
}

Just a suggestion, but how 'bout running one query for the list of categories and use the results to create a query for the items using a combination of LIMIT and UNION? That way you're only sending two queries; but, it's possible that that's not much more efficient than the second option you described depending on how much overhead each database call requires and how much optimization mySql will do when it sees the UNION (e.g. parallel processing the statement).

I don't know enough about it to recommend it, but it's something i would try.