Limit the output of a mysql query when a variable is used in WHERE

Question

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'];

Answer 1

If I understand you correctly, you want the latest (most recent timestamp) track from each artist in your artist table.

It would help if you had your table definitions displayed. I think you're confusing ARTIST_ID and TRACK_ID in your query from your tracks table. So I will use the column names ARTIST_ID and TRACK_ID throughout.

(TIMESTAMP is an unfortunate choice for a column name, because it's also a MySQL data type name, by the way. No matter.)

You can do this with one query. Let us construct that query. It's not super simple but it will work just fine.

First, let's get the timestamp of the latest track or tracks by each artist. This returns a virtual table with ARTISTS_ID and latest TIMESTAMP shown.

   SELECT ARTIST_ID, MAX(`TIMESTAMP`) `TIMESTAMP`
     FROM TRACKS
 GROUP BY ARTIST_ID

Now, let's nest that query into another query to come up with a particular track_id that is the latest track from each artist. It is necessary to disambiguate the situation where an artist has more than one track with precisely the same timestamp. In this case we'll grab the lowest numbered TRACK_ID.

I suppose that all the tracks on an album by an artist have the same timestamp, but they have ascending track IDs, so this picks the first track on the artist's latest album.

  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

See how this goes? The inner subquery finds the latest timestamp for each artist, and the outer query uses the subquery to find the lowest-numbered track ID for that artist and timestamp. So, now we have a virtual table that shows the latest track_id for each artist.

Finally, we need to query the joined-together artist and track information to get your list of artists and their latest tracks. We'll join the two physical tables with the virtual table we just figured out.

    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

Think of it this way: You have some physical tables with your data in them. You can also create virtual tables with subqueries and use them as if they were physical tables by including them, nested, in your queries. That nesting is one of the reasons it's called Structured Query Language.

You're going to need indexes on your TIMESTAMP, ARTIST_ID, and TRACK_ID columns for this to work efficiently.

Edit: There really isn't sufficient information about your schema in your question to figure out how unambiguously to get the most recently uploaded track.

If the TRACK_ID is the autoincrementing primary key for the TRACKS table, it's easy. Get the highest numbered track ID left joined to the artist (left joined in case there's no corresponding row in the artist table).

        SELECT T.*, A.*
          FROM TRACKS T
     LEFT JOIN ARTISTS A ON T.ARTIST_ID = A.ARTIST_ID
      ORDER BY T.TRACK_ID DESC
         LIMIT 1

If TRACK_ID isn't an autoincrementing primary key but you almost never have two timestamps the same, do this. If there happen to be two or more tracks with the same timestamp, it will arbitrarily select one of them.

        SELECT T.*, A.*
          FROM TRACKS T
     LEFT JOIN ARTISTS A ON T.ARTIST_ID = A.ARTIST_ID
      ORDER BY T.`TIMESTAMP` DESC
         LIMIT 1

The trick to this data stuff is to be very careful to specify exactly what you want. It's pretty clear from your question that you're trying, in a loop, to get the most recent track for each artist in turn. My query did that without a loop in your program. But, you know what, I don't know the names of all your columns so my SQL might not be perfect.

Answer 2

The relation between artists table and tracks table is one-to-many. So your tracks table should have a column artist_id and foreign key constraint which cross-references this column with id column in artists table. When this is done, the query to get latest tracks would look like:

SELECT id, name, url, MAX(timestamp) timestamp
FROM tracks
GROUP BY artist_id

Answer 3

Big thanks to @OllieJones and @hookman for helping me out on this. I have found the query I need and I have done it all in one query without any PHP so big thanks to them both.

Anyway here it is;

SELECT T.url, A.ID, T.ID
FROM tracks T
LEFT JOIN ARTISTS A ON T.ID = A.ID
WHERE T.ID = A.ID
ORDER BY T.timestamp DESC                                      
LIMIT 1

I took much of @OllieJones query and edited it a bit. I added the WHERE clause so that only artists are chosen and took away the * so only the needed data is returned. I also took @hookman advice and used a load of foreign keys. Gonna help a lot in the future.