hello
i have table (news) and have this fields
id,subject,body,date,cat
how can i select last 3 news for each cat in this table in one select ?
thank you
SELECT * FROM 'news' ORDER BY 'id' DESC LIMIT 0 , 3not working
in my table [news]
|id| |subject| |body| |date| |category|
1 sub1 body1 1.1.2011 news
2 sub2 body2 1.1.2011 news
3 sub3 body3 1.1.2011 news
4 sub4 body4 1.1.2011 news
5 sub5 body5 1.1.2011 news
6 sub6 body6 1.1.2011 sports
7 sub7 body7 1.1.2011 sports
8 sub8 body8 1.1.2011 sports
9 sub9 body9 1.1.2011 sports
10 sub10 body10 1.1.2011 sports
11 sub11 body11 1.1.2011 art
12 sub12 body12 1.1.2011 art
13 sub13 body13 1.1.2011 art
14 sub14 body14 1.1.2011 art
15 sub15 body15 1.1.2011 art
i want select the red records the last 3 record for each category news,sports,art
how ?
If you want to select the last 3 at the bottom, simply do what PHPjordan suggested but this time, replace the keyword DESC with ASC that is
SELECT * FROM ‘news’ ORDER BY ‘id’ ASC LIMIT 3
I’m not sure why he had LIMIT 0, 3
what about other category records ?
The only way I can think of doing this is to use the select where function. I have only been doing this for a week tho so i’m sure theres a better way.
SELECT * FROM news WHERE category=‘news’ ORDER BY ‘id’ ASC LIMIT LIMIT 0,3
Im not sure if that code is 100% correct but I think it is. you then just have to replace the category for each other category in your table.
Bit of a long way of doing things but thats all i have.
Sam
@carrot, that would be a wrong approach because he is not only trying to limit the results to rows where the category is news. he wants 3 specific rows which are those at the bottom. so no where clause is necesarry. all he needs to do is return the three oldest records unless i didn’t get it right.
I think he wants the last 3 from each category i.e. the last 3 of news, the last 3 of sport and the last 3 of art.
Is my method not right?
Sam
Try using this code. It grabs the total number of records you have per each category, stores it up in variable $n and offsets by 3 for the amount of rows you want. You then run the query again just ordering by id and limiting from the offset amount three records. This should give you the last 3 of each category. I maybe doing something wrong but my hopes is that you kind of grasp what I am doing here.
[php]<?php
$cat = array(‘news’, ‘sports’, ‘art’);
foreach($cat as $col)
{
$result1 = $db->query(“SELECT count(*) AS $n FROM news WHERE category=’$col’”);
$row1 = $result1->fetch_assoc();
$offset = row1[$n] - 3;
$result2 = $db->query("SELECT * FROM news WHERE category='$col' ORDERBY id ASC LIMIT $offset, 3");
// Remaining code
}
?>
[/php]
By the way, I use mysqli a lot so if you don’t, sub the normal commands from my own. Hope this works.
thank you all
thank you OpzMaster
your code is good but i have a lot of categories but i listed 3 for example
thank you