Geek asks and answers

Get N Rows of Groups / Themes / Themes

I am looking for the correct mysql query for my site http://watiseropderadio.nl (in Dutch). This is a website to find out which songs were played on the popular radio in Holland.

I want to make a query that returns 13 rows for each radio station. There are 8 different radio stations.

This 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
....

This is a table with 3,000,000 + entries and with these keys:

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

This is what I want to be 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 ....

The order of the result does not matter, and the query should be fast. I like to try it myself, but for me it is a little for me.

I don’t know if the key is suitable for this work, can anyone give some advice?

+3
source share
2 answers

, , : " 13 ", , : " 13 x".

, , - "play number" , "" ". , , . ( MySQL , .) <= 13, , , " select station, max (play) maxplay group ", " play > maxplay-13 "

MySQL, . http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html. , , : " MyISAM BDB AUTO_INCREMENT . AUTO_INCREMENT MAX (auto_increment_column) + 1 WHERE prefix = given-prefix. , ". ( , , , .)

+1

( 8) 8 ? -

<?php
$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
}
?>

[]

0
source

More articles:


All Articles