Check if a record exists in the last 30 records

djpaul

Hello!
I want to check if a record exists in the last 30 records that were added.
For example, i have a table wich contains about 34000 records by now.
But i want to check if the songID is in the last 30 records.

Is this somehow possible?

Now i select the last 30 records and put them in an array, and let php check if this songID is in the array.
But it must be much easier if mysql this does!

Regards!
Paul

Oct 5 '08 #1

Atli

Hey Paul.

How does your table look like?
Is the "songID" column the primary key for that table?

I would be very surprised if this isn't possible, but without knowing exactly how you table looks like I can't really say for sure.

Oct 5 '08 #2

djpaul

No, it's like this:

ID - int - primary key - auto increment
songID int -
filename varchar
date_played - datetime
artist varchar
title varchar
...and furter

Oct 5 '08 #3

labmonkey111

This may not be the ideal way to do it, but this might work:

Then check mysql_num_rows to see if anything was returned.

Oct 5 '08 #4

djpaul

Hmm, that may work.
I gonna try it tomorrow and let you know!

Thanks!

Oct 5 '08 #5

djpaul

Hmm, too bad!
I get an error: #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

I just updated mysql from 5.0.21 to 5.1.28 but it didn't seem to help.
Strange...

Oct 7 '08 #6

Atli

Strange. I thought that worked in all versions 5 and above...

Anyhow. You could also try something like this:
The idea is to fetch the ID of the 30'th row from the top using the subquery and match only against rows with higher ID's.

Tested this on version 5.0.51

Oct 7 '08 #7

djpaul

Aahh!
That's a lot better.
It maybe a bug of mysql, but i think it's strange that you cannot use a limit in a IN function.
Too bad!

Thanks Atli!

Paul

Oct 8 '08 #8