-2

I have a table for news and I want to select the 3 newest things in each row, they're numbered 1 as the oldest and each data I add it will be a number higher. So how do you get the three newest things and display the them in places? (Sorry I can't think of how to phrase my question)

EDIT: I don't have like html or anything so show the desired effect, but I can explain it. I have an SQL Table that has 4 rows. ID, TITLE, DATE, and POST. This is a news feed, and I want to display the 3 newest news "articles" on the homepage. But I don't want to just display them as lists, I want to put the title in a top div, next to the date, I want to order it by the primary key (ID) and then have the post in a div under the title and date. Here's an attempt to show you:

Title | Date
Newest news. Etc. Insert Big Paragraph of news here etc etc.

Title | Date
2nd Newest news. Etc. Insert Big Paragraph of news here etc etc.

Title | Date
3rd Newest news. Etc. Insert Big Paragraph of news here etc etc.
peterm
  • 91,357
  • 15
  • 148
  • 157
user2565624
  • 83
  • 1
  • 6

3 Answers3

1

first, please dont use 'date' as your field name, lets say you rename it as news_date. How about this?

<?php 
$query = mysql_query ("SELECT * FROM yourtable ORDER BY id DESC LIMIT 3");
$number = 1;
while ($result = mysql_fetch_array($query)) { 
    $id = $result['id'];
    $title = $result['title'];
    $news_date = $result['news_date'];
    $post = $result['post'];
?>
    <div name='title'><?php echo $title; ?></div> || <div name='news_date'><?php echo $news_date; ?></div>
    <p>News <?php echo $number; ?></p>
    <p><?php echo $post; ?></p>
 <?php   
    $number++;
    }
?>
kayla
  • 156
  • 1
  • 12
  • Question updated to make more sense. Also, would ORDER BY show #1 then #2 then #3 or vice versa? I also need to know how to echo the results. Like echo #3 title in one area, then #2 title in another, etc. I don't want it to be in a list. – user2565624 Jul 15 '13 at 04:56
  • I dont really understand your question, but i updated my answer. – kayla Jul 15 '13 at 05:46
  • I think I can work with this for the desired effect. Thank you. – user2565624 Jul 15 '13 at 05:54
  • youre welcome.. let me know if you have a problem implementing it – kayla Jul 15 '13 at 05:59
  • Correct me if I'm wrong, but I believe how you have it will list all the titles from the query. I want it to say get the highest ID, and the I don't know how to display the title/date/post of the selected ID, then subtract 1 from the ID, and get that title/date/post and so on. – user2565624 Jul 15 '13 at 06:04
  • do you mean getting all data from the table and sorting it in php? then all you should do is save all results in an array then sort it with PHP sorting function. then display it again just like anything you want – kayla Jul 15 '13 at 06:12
0

If I am getting you correctly you can use LIMIT and ORDER BY

Example

 `SELECT columns FROM yourtable ORDER BY primary_key DESC LIMIT 0,2`
alwaysLearn
  • 6,882
  • 7
  • 39
  • 67
  • On this it would be 0,2 to get the first three, since theres 0,1,2. – user2565624 Jul 15 '13 at 04:47
  • I also updated the question to make it more clear. – user2565624 Jul 15 '13 at 04:54
  • O yes you are right .. Thanks for correcting me ... as far as fetching of data is concerned this will work – alwaysLearn Jul 15 '13 at 05:18
  • Does the ORDER BY do 1 to 99 or 99 to 1? Also how would you make the primary_key auto increment – user2565624 Jul 15 '13 at 05:29
  • is order by 1 to 99 or 99 to 1 depends on `ASC` or `DESC` used with it. Since I have used `DESC` so it will be 99 to 1. For auto incriment you can refer this http://stackoverflow.com/questions/251479/make-an-id-in-a-mysql-table-auto-increment-after-the-fact – alwaysLearn Jul 15 '13 at 05:51
  • Okay, and then how would I echo the one part? Like how would I echo the newest title or the third newest post? – user2565624 Jul 15 '13 at 05:53
  • When you fetch data , you will have an indexed/associative array . Use corresponding key to refer the post u want – alwaysLearn Jul 15 '13 at 05:54
  • Can you please look at Kayla's answer? That's kind of how I want to do it, but I want it only to echo the $number title/post and the subtract 1 from the number and the do the next title/post and etc. – user2565624 Jul 15 '13 at 06:01
  • His answer seems perferct to me – alwaysLearn Jul 15 '13 at 06:06
0

If my understanding of your question is correct, it seems you have a table with a row identifier (numbered 1 to ... n) and you want to run a query that will bring back the 3 most recent inserted records ?

If so:

Lets assume you have a table called Books with the below structure:

Books:
- RowNumber
- Col_1
- Col_2
- ...

What you need to do is run the following SQL:

SELECT 
    TOP 3 *
FROM
    Books
ORDER BY
    RowNumber DESC

I hope this helps :)

Taz Hussein
  • 51
  • 4