Displaying from mysql table accoring to id and another column

[ketanco]

Hello,

I have a mysql table that has a few columns... One column is the "id", which is auto increment and primary key, another is called "category", and another is called paragraph1.

I would like to do two things:

1) Display the value of category column, that is in the row which has id of 2nd to latest (no matter what other columns are). Also use the value of category column as a link such as <a href="value.php">value</a>


2) Also, separately, I would like to display the paragraph1 values of two more rows. Those rows need to have a category value of, say, "aaa", AND they need to be the latest two entries (for that category aaa) so they need to have biggest and second to biggest id (again for that category aaa)

I know roughly what to do, but just dont know the exact syntax and quotations etc....Please help....

[Old Pedant]

I, for one, am a little lost.

Could you show sample DB content and then expected HTML output based on that sample? Possibly with more than one sample and more than one HTML??

Regarding question 1: I think I can at least give you the SQL query.

SELECT X.id, X.category
FROM ( SELECT id, category
FROM table
ORDER BY id DESC
LIMIT 2 ) AS X
ORDER BY id ASC
LIMIT 1

See the trick? The inner query gets the last two records, by id, in the table. Then the outer query picks the first of those two records. So it indeed gets the penultimate record, by id.

[ketanco]

You are right. I read my question again, and it needs slight modification. the revised version of the question would be as follows:

I meant to say, display the values from the latest, say, three rows, which belongs to a particular category

/id/category/date/title/paragraph1
/1/apple/2009-06-07/title text 1/paragraph text 1
/2/orange/2009-06-08/title text 2/paragraph text2
/3/apple/2009-07-07/title text3/paragraph text3

so again, what i want is to display date title paragraph etc...from latest three entries from apple category for example.

[Old Pedant]

Then it's a piece of cake.

SELECT id, category, `date`, title, paragraph1
FROM yourtable
ORDER BY `date` DESC
LIMIT 3

Note: that query will get the records in *reverse* order. Latest date first.

If you need the latest 3 records but want them in forward order:

SELECT X.* FROM (
SELECT id, category, `date`, title, paragraph1
FROM yourtable
ORDER BY `date` DESC
LIMIT 3 )
ORDER BY `date` ASC


Note that `date` needs the tick marks because DATE() is a builtin function in MySQL.

[ketanco]

Then it's a piece of cake.

SELECT id, category, `date`, title, paragraph1
FROM yourtable
ORDER BY `date` DESC
LIMIT 3

Note: that query will get the records in *reverse* order. Latest date first.

If you need the latest 3 records but want them in forward order:

SELECT X.* FROM (
SELECT id, category, `date`, title, paragraph1
FROM yourtable
ORDER BY `date` DESC
LIMIT 3 )
ORDER BY `date` ASC


Note that `date` needs the tick marks because DATE() is a builtin function in MySQL.

Thanks but how do you separate it by category? This will give the latest entries from all categories without distinguishing. But I just want one category, say apples... Nothing from another category...