Displaying latest entries only for all categories

ketanco

Hello,
I have a table as :
id/category/title/date/text

what I want to do is, to display latest entries from each category. so if there are 10 categories, there should be 10 entries total. and each of those rows should be the latest of that category.

I wrote this but it gives error saying unexpected $end for the last line in the code below...Even if it doesnt give error I am not even sure if I set up the logic right for what I want.

for($i=1; $i<=13; $i++)

{
$sql="(select * from table1 WHERE 'category'=$i ORDER BY id DESC LIMIT 1)
}

ORDER BY date DESC LIMIT 2,1";

$query=mysql_query($sql);
?><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><?

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

$b=$row['id'];
$c=date("F j", strtotime($row['date']));

echo"".$c.""."" . " ". $row['title'] . "".
"" . $row['par1'] ."<a href='news.php?id=$b'".">"."(more...)"."</a>" ; }?>

Please help.

Bjom

There is a semicolon missing

$sql="(select * from table1 WHERE 'category'=$i ORDER BY id DESC LIMIT 1);

there might be more missing and you also need to check for missing and misplace { } . that's what causes the error.

As to the how: a query that retrieves exactly what you want would be better:

select category, max(date) from table1 GROUP BY category;

that's part of a query - to get it complete I'd need to know a bit more about the table and what you need.

Roger_Ramjet

Assuming that the id column is an autoinc column, then the max(id) for each category will also be the latest post for each category and integer indexes work faster than other indexes. So using max(id) instead of max(date) is faster and more optimal.

As to the whole query:

SELECT * FROM table1 WHERE id IN (SELECT max(id) FROM table1 GROUP BY category)

Bjom

Good point on the id and integer. As for the whole query: there are not all of the fields in either the group by or an aggregate function - so while MySQL will allow and process it, it can behave in unforseen ways.

ketanco

thanks but the first one by bjom brings one from each category but it brings a fix date of dec 31 1969 for each.... and it doesn't necessarily bring the latest dated rows....

the second one doesnt work at all...

again, what i am looking for is the latest entries for each category....

Bjom

what type is your field date it has to be date or timestamp? And also retry the query with backticks:

select `category`, max(`date`) from `table1` GROUP BY `category`;

ketanco

still the same result with the backticks...

my date field is date type...

here is what i have exactly.... please help...

$sql="(select `id`, `title`, `category`, `par1`, max(`date`) from `table1` GROUP BY `category`)";


$query=mysql_query($sql);
?><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><?


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

$b=$row['id'];
$c=date("F j Y", strtotime($row['date']));



echo"<span id=newsdate1>".$c."</span>"."<span class='newstitle1'>" . "<br/>". $row['title'] . "</span>". 
"<span id=newstext1>" . $row['par1'] ."<a href='news.php?id=$b'".">"."(more...)"."</a>"  ; }?>

Bjom

yeah. that is not very good. standard sql prohibits that you have values in an aggregate query that are not either in an aggregate function or in the group by clause. MySQL allows it, but that does not mean it always behaves well.

For testing purposes please run only the short part that I provided and see if it comes up with the correct dates, exactly like this (w/o brackets or anything, just copy/paste). I'd also suggest that you make a raw output of the retrieved database data without any further processing i.e. use $row['id'] and $row['date'] in the output part...

query:

$sql = "select `category`, max(`date`) from `table1` GROUP BY `category`;";

if that works ok, we can join in the other fields in the next step.

Why do you split up your code into two halves like that? Just echo the meta and stay in php mode. And why not use the full "<?php" opening tag instead of the sometimes problematic short "<?" ?