?
Solved

How to get the last 15 records from a MySQL table in the right order

Posted on 2004-11-30
6
Medium Priority
?
218 Views
Last Modified: 2010-07-27
Dear Experts,

I have a MySQL table called "messages" which has two fields : name and message.

Now I need an example how to query this table and only fetch the LAST 15 records in (I guess) reverse order.

For example, when I have 1500 records:
The result must be one string containing record 1486, 1487, 1488 and so on: FIELD "name" + "some text" + field "MESSAGE" + " some text".

The idea is that people can enter a name and some short text in two fields on a webpage. Those fields are stored correctly into the table.
Now I need to display only the last 15 messages in chronological order, so last placed message last.

Kind regards,

Maurice
0
Comment
Question by:MauriceW
4 Comments
 
LVL 6

Expert Comment

by:holli
ID: 12705086
using MS-ACCESS you can have a "LAST n"-statement in your SQL-statement.
that is not possible with mysql.

you will have to use a "counter" field wich value you can use in your statement ( eg. SELECT ... WHERE counter >= 1445 and counter < 1500) or fetch all records in an array and get the last 15 out of these
0
 
LVL 18

Accepted Solution

by:
kandura earned 400 total points
ID: 12705210
I hope you either have a date field, or a primary key field, otherwise sorting will be impossible.

Assuming you have an auto_increment primary key, you'd do:
    select name, message from messages order by message_id desc limit 15

If instead you have a create_date field:
    select name, message from messages order by create_date desc limit 15

These statements will select the last 15 records, but not in the order you want them. Since it's only 15, I suggest fetching them all, and reversing the result set in perl:

my @messages;
my $sth = $dbh->prepare('select name, message from messages order by message_id desc limit 15 ');
$sth->execute();
while(my $r = $sth->fetchrow_hashref) {
    push @messages, $r ;
}

foreach (reverse @messages) {
    print "Name: ", $_->{name}, $/;
    print "Message: ",  $_->{message}, $/;
}



0
 
LVL 7

Expert Comment

by:rugdog
ID: 12710276
AFAIK,  if no order clause is specified in a MySQL query, the order the rows  are retrieved is the order they were inserted, so if you need to fetch the last 15, try this:

1. compute the total number of rows
  select count(*) from mesages;
2. substract 15 to the count minus one (since first row is 0) and use that number in limit, so, for example, if the count is 100 try:

select * from messages limit 84,15
0
 
LVL 9

Expert Comment

by:gruntar
ID: 12710876
Kandura gave you the right answer :) Listen to him

Cheers
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Six Sigma Control Plans
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question