I am searching for the right mysql query for this site http://watiseropderadio.nl (in nederlander). It's a web site to discover what tunes happen to be performed around the popular radio's in Holland.

I wish to create a query what returns 13 rows for every radiostation. You will find 8 different radiostation.

This really is my table:

id       | radioname       | time  | artist    | song
23421    | radio 538       | 19:34 | Queen     | Bohemian
23422    | radio veronica  | 19:35 | Rammstein | Blablabla
23423    | slam fm         | 19:34 | Roxette   | Blablabla
23424    | 3fm             | 19:34 | Blabla    | Blablabla

It is a table with 3,000,000+ records with these key's:

PRIMARY             PRIMARY  3083007   id
radioname           INDEX    8         radioname
track with artist   INDEX    23715     artist

This what I wish to function as the output:

id       | radioname       | time  | artist    | song
23455    | radio 538       | 19:30 | Blabla    | Blablabla
23470    | radio 538       | 19:33 | Blabla    | Blablabla
23484    | radio 538       | 19:36 | Blabla    | Blablabla
23498    | radio 538       | 19:38 | Blabla    | Blablabla
total 13 x ....
23456    | radio veronica  | 19:29 | Blabla    | Blablabla
23476    | radio veronica  | 19:32 | Blabla    | Blablabla
23483    | radio veronica  | 19:36 | Blabla    | Blablabla
23495    | radio veronica  | 19:39 | Blabla    | Blablabla
total 13 x ....

An order from the result does not matter and also the query needs to be fast. I love to check it out myself, but this can be a little to much for me personally.

I'm not sure when the key's are goed with this job, can someone gives some advise?

This really is tricky because, while you will find methods to say "get me only 13 records with this query", to the very best of my understanding there is no method to directly say "get me only 13 records with this each worth of area x".

The only method I'm able to think about to get it done would be to add an "play number" area towards the record, in order to create another table to carry the dpi if you cannot or don't wish to alter the "song performed" record. When records are put into the table, give each the following available number for your radio station. (Really MySQL includes a feature to get this done instantly.) Your query could search for this index <=13, or maybe records could be erased, come with an inner query that does a "choose station, max(play) as maxplay group by station", then your outer query could choose "where play>maxplay-13"


To setup the MySQL auto-increment, see http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html. Note specially the discussion round the sentence, "For MyISAM and BDB tables you are able to specify AUTO_INCREMENT on the secondary column inside a multiple-column index. Within this situation, the produced value for that AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This really is helpful when you wish to place data into purchased groups." (Frankly, it has been some time since i have used this and so i can't supply lots of detail.)

Could it be acceptable to loop with the listing of r / c (as you will find only 8) and fire off 8 separate queries? Something similar to

$stations = array('radio 538', 'radio veronica', 'slam fm', '3fm');
foreach ($stations as $station)
    $query = sprintf('SELECT * FROM table WHERE radioname="%s" ORDER BY time ASC LIMIT 0,13', $station);
    // do something