Thread: Retrieving 2nd entry in db query

I log each time a user logs in to the database and on the home page show their last login date and time.

However, from the login page, I save the login details, so I don't want to show the most recent entry on the home page as that will be when they just logged in. I want to show the next most recent entry.

The tbl has:-

Code:

ID field autonumber
userref field INT
username field VARCHAR
login_time DATETIME
ip_address VARCHAR

So, assuming I had:-

Code:

ID     login_time
1       19/03/2009 08:35
2       19/03/2009 09:35

The next time I login it will add another entry, say for 10:35. I then want to retrieve the 09:35 entry to show on the home page.

Is there an easy way to do this?

I'm thinking of something like SELECT TOP 2, but then I'm not sure how to get the 2nd entry.

Preferably want to do it using a Stored Proc.

Any ideas?

OK. I got it.Use LIMIT. I forgot you use LIMIT in MySQL, not TOP.

Code:

SELECT * FROM tblLogins WHERE userref=@userref ORDER BY loginref DESC LIMIT 1,1

Does this query gives you 2 records RR??
coz i think it'll give 1 record only.

Originally Posted by micky

Does this query gives you 2 records RR??
coz i think it'll give 1 record only.

No, it only gives 1 record, but that's all I wanted. I just wanted the 2nd record in the list as the first record will be when they just logged in. So, I don't even need to do anything with moving to the 2nd record or anything.

I thought I might have to get the first 2 records and then use code to move to the 2nd record...

All done in one query...

ya, u really dont need 2 records from db

but i wonder how that can be done in a query!!
anyway, glad u solved it

Originally Posted by micky

ya, u really dont need 2 records from db

but i wonder how that can be done in a query!!
anyway, glad u solved it

You mean how to get the first 2 records?
Something like this:-

Code:

SELECT * FROM tblLogins WHERE userref=@userref ORDER BY loginref DESC LIMIT 0,1

I think...To get 2 records starting from the second use LIMIT 1,2

Originally Posted by richyrich

You mean how to get the first 2 records?

no, i mean how to differentiate which is first record and which is second!!

Originally Posted by micky

no, i mean how to differentiate which is first record and which is second!!

Not sure I get you micky.

It depends on how they're ordered. I just reverse the autonumber ID field as the order by clause. So the most recent is first in the list and next most recent is second in the list.

Originally Posted by richyrich

Not sure I get you micky.

It depends on how they're ordered. I just reverse the autonumber ID field as the order by clause. So the most recent is first in the list and next most recent is second in the list.

hmm, ya ......that might work