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 displayed rows total. and each of those rows should be the latest of that category.

Please help in writing the sql query...

I have a not working sql query right now so i am not including what i wrote...after the sql query, i am writing how i display the result below (if it matters...)


$sql= not working query.... (please provide)

$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['text'] ."<a href='news.php?id=$b'".">"."(more...)"."</a>" ; }?>

[BubikolRamios]

something like this, did not test it doh.

select max(date), category
from table
group by category

[guelphdad]

See latest record per group (http://guelphdad.wefixtech.co.uk/sqlhelp/latestfromgroup.shtml).

[ketanco]

something like this, did not test it doh.

select max(date), category
from table
group by category


this one brought up a result from each category but writes dec 31 1969 and they are not the latest dated entries of each category anyway....

[BubikolRamios]

there is a test, and works.


DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`c2` varchar(45) COLLATE utf8_slovenian_ci DEFAULT NULL,
`date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci ROW_FORMAT=FIXED;

--
-- Dumping data for table `test`
--

/*!40000 ALTER TABLE `test` DISABLE KEYS */;
INSERT INTO `test` (`c2`,`date`) VALUES
('a','2010-05-20 09:00:00'),
('a','2010-05-21 09:00:00'),
('b','2010-05-22 09:00:00'),
('b','2010-05-23 09:00:00');
/*!40000 ALTER TABLE `test` ENABLE KEYS */;



SELECT max(date), c2
FROM test t
group by c2

[Old Pedant]

DATE is a *built in* function, so when you use it as a DB field name, you must put `...` around it.

Thus:

select max(`date`) As LatestDate, category
from table
group by category

Those are *not* apostrophes; those are the "back tick" marks that share the key with ~

[BubikolRamios]

thanks for that, hmm sql worked the same, anyway

[Old Pedant]

It may depend on the version of MySQL. I have accidentally used date in the past and had it work.

But you can see why he would be Dec 31, 1969, if DATE was treated as the function: DATE() as a function means "extract the date-only from my argument". So if you do

SELECT DATE('2009-03-15 20:32:14')

you get only the date, without the time.

And you can imagine that doing

SELECT DATE(0)

would indeed give 31 Dec 1969, the "zero date" for MySQL. (Okay, I admit I thought it would be 1 Jan 1970, so maybe I'm way off base.)

Anyway, it's worth a try for the original poster.

[guelphdad]

Ketanco, did you read the article I linked? It is the solution you want to use.

You also didn't really specify if you are using DATE as actual data type, seems you might be using a varchar/char type.

Old wise one (Pedant), you don't need to use backticks around DATE. It is not a reserved word in mysql. Look on the reserved word list and they do allow the use of about 8 or 10 words which are treated differently basically because at one point they weren't reserved words but should have been.

[ketanco]

Ketanco, did you read the article I linked? It is the solution you want to use.

You also didn't really specify if you are using DATE as actual data type, seems you might be using a varchar/char type.

Old wise one (Pedant), you don't need to use backticks around DATE. It is not a reserved word in mysql. Look on the reserved word list and they do allow the use of about 8 or 10 words which are treated differently basically because at one point they weren't reserved words but should have been.

date is, date datatype...

yes I read it but did not understand... what is c1 and p1 etc...? and also what happens when you combine them with dots to other words such as p1.user id?

[guelphdad]

Good questions.

p1 and c1 are just alias names, shorter than writing out the table name. the table name can be long because you want to be more specific about what it is

SELECT * FROM
carsthatareinmycollection AS c1

for example.

and when you see c1.thishere

it refers to the table (using an alias, you could use the whole table name) and then the specific column.

That is necessary when you join tables and the same column name exists in both tables.

you also do it when you join a table to itself so you can refer to the same column more than one time.

Anything else you didn't understand?