Combine several SQL querys to one

Question

I know this is probably really simple but I have tried to find some similar examples and failed.

The problem is that I would like to list 8 random images from a gallery in a database, sorted by added date. And I have managed to do this, but only with several iterating query's that becomes really slow. So if someone would be so kind to teach me about combining them for faster speed, I guess UNION is the way to go? Here is my working (but slooow code)

<?php       
        $latestPictures = mysql_query("SELECT pictureID, addedDate FROM picture ORDER BY addedDate DESC LIMIT 8"); 

        $latestConcerts = mysql_query("SELECT concertID, addedDate FROM concert WHERE pictureID is null ORDER BY addedDate DESC LIMIT 8"); 


        // Add concerts and pictures to array
        while($curFestival = mysql_fetch_object($latestPictures))
        {
            $array[$curFestival->addedDate] = "p" . $curFestival->pictureID;
        }

        while($curConcert = mysql_fetch_object($latestConcerts))
        {
            $array[$curConcert->addedDate] = "c" . $curConcert->concertID;
        }

        // Order array by key
        krsort($array);

        $latestArray = array_slice($array, 0, 8);

        foreach($latestArray as $key => $value) {

            $type = substr($value, 0, 1);
            $ID = substr($value, 1);

            // If type == picture
            if($type == 'p')
            {           
                $picturesPicturesID = mysql_query("SELECT concertID, name FROM photo WHERE concertID IN(SELECT concertID FROM concert WHERE pictureID = $ID) ORDER BY photoID");

                // Get random picture
                $curRandomPicture = rand(0, (mysql_num_rows($picturesPicturesID) - 1));
                $curPictureConcertID = mysql_result($picturesPicturesID, $curRandomPicture, "concertID");
                $curPictureName = mysql_result($picturesPicturesID, $curRandomPicture, "name");             
                $curPicture = mysql_fetch_object(mysql_query("SELECT c.URL, p.name FROM concert c, picture p WHERE p.pictureID = c.pictureID AND c.concertID = $curPictureConcertID")); 

             echo "Some image"; 
             }
            // If type == concert
            if($type == 'c')
            {
                $concertPicturesID = mysql_query("SELECT concertID, name FROM photo WHERE concertID = $ID ORDER BY photoID");

                // Get random picture
                $curRandomPicture = rand(0, (mysql_num_rows($concertPicturesID) - 1));  
                $curPictureConcertID = mysql_result($concertPicturesID, $curRandomPicture, "concertID");
                $curPictureName = mysql_result($concertPicturesID, $curRandomPicture, "name");              
                $curPicture = mysql_fetch_object(mysql_query("SELECT URL, name FROM concert WHERE concertID = $curPictureConcertID")); 

            echo "Some image";
            }
        }
?>

I realized that I forgot to include the tables, here they are:

TABLE OF photo:
PKEY: photoID
FKEY: concertID
name

TABLE OF concert:
PKEY: concertID
FKEY: pictureID
name
URL
addedDate

TABLE OF picture
PKEY: pictureID
name
date

So every post is part of TABLE photo AND concert, but only some is part of picture witch is only used sometimes to group differens albums together. When they are grouped together I whant a random name post from that grouping ID (picture) and if they are by them self a random name post from there (concert).

Answer 1

Accckkk! (In the infamous words of Bill the Cat.)

It's hard to figure out what result set your code really wants from the database.

This query isn't the most efficient, but it will return 8 random rows from the photos table, with those rows ordered by addedDate:

SELECT r.*, c.*
  FROM (SELECT p.*
          FROM photo p
         WHERE p.concertid IS NOT NULL
         ORDER BY RAND()
         LIMIT 0,8
       ) r
  JOIN concert c
    ON c.concertid = p.concertid
 ORDER BY r.addedDate ASC

If you have a really large photo table, this is going to be slow, because that RAND() function has to get called for every single row in the table, and MYSQL has to produce a temporary result set (a copy of the table) and then sort it on that derived column.

(NOTE: I'm assuming here that it's the photo table that you want to return "random" rows from, and I'm assuming that concertid is the primary key on the concert table, and a foreign key from the photo table. It's apparent that you have three tables... concert, picture and photo, but it's not clear which columns are the primary keys and which columns are the foreign keys, so it's likely I have it wrong.)

(NOTE: replace the p.* and c.* with a list of expressions you actually want to return.)

There are more efficient approaches to returning a single random row. In your case, you want exactly eight rows, and you presumably don't want to return a duplicate.