I have a mysql statement that queries a database for the latest track. However, since the database is partially normalized the ID's are in different tables. In the query's I get the artist ID'd from the artists table and put them into a variable. The variable in then parsed into a query that looks at the tracks to find the latest one, this is where the problem lies. Since the $artist variable can have tonnes of ID's in, all those ID's are parsed into the query and the outcome is several url's put together even though I have put a LIMIT on the query.
Bear in mind that I cannot LIMIT the artist query as I need to get all the artists from the table and find the latest track out of all the artists.
How would I get just the latest url from the query without limiting the artist query?
//Set up artist query so only NBS artists are chose
$findartist = mysql_query("SELECT * FROM artists") or die(mysql_error());
while ($artist = mysql_fetch_array($findartist)){
$artist = $artist['ID'];
//get track url
$fetchurl = mysql_query("SELECT * FROM tracks WHERE id = '$artist' ORDER BY timestamp DESC LIMIT 1");
url = mysql_fetch_array($fetchurl);
$track_ID = $url ['ID'];
$trackname = $url ['name'];
$trackurl = $url ['url'];
$artist_ID =$url['ID'];
}
ADDITION:
$findartist = mysql_query("SELECT A.*, T.*
FROM (
SELECT T.ARTIST_ID, MIN(T.TRACK_ID) TRACK_ID
FROM (
SELECT ARTIST_ID, MAX(`TIMESTAMP`) `TIMESTAMP`
FROM TRACKS
GROUP BY ARTIST_ID
) L
JOIN TRACKS T ON ( L.ARTIST_ID = T.ARTIST_ID
AND L.`TIMESTAMP` = T.`TIMESTAMP`)
GROUP BY T.ARTIST_ID
) X
JOIN ARTISTS A ON X.ARTIST_ID = A.ARTIST_ID
JOIN TRACKS T ON (X.TRACK_ID = T.TRACK_ID AND X.ARTIST_ID = T.ARTIST_ID)
ORDER BY A.NAME");
while ($artist = mysql_fetch_array($findartist)){
$artist = $artist['ID'];
$trackurl = $artist['url'];