1

I have two separate SQL queries that I would like to combine to a single one if possible.

Query #1 yields all entries from a table in random order

Query #2 will afterwards check whether or not the result can be used

How can I achieve this in a single step directly in SQL?

Code:

// start with a query for all of the photos, returned in random order
$query = "
SELECT DISTINCT m.mediaID
              , m.description
              , m.path
              , m.alwayson
              , m.usecollfolder
              , m.mediatypeID 
FROM $media_table m
WHERE m.mediatypeID = 'photos' 
ORDER BY RAND();
"; 
$result = mysql_query($query) or die ("$text[cannotexecutequery]: $query");
while( $imgrow = mysql_fetch_assoc( $result ) ) {

    // if the picture is alwayson or we are allowing living to be displayed,
    // we don't need to bother 
    // with any further checking  
    if ($imgrow[alwayson] || $allow_living_db ) { 
    break; 

    // otherwise, let's check for living  
    } else { 

    // this query will return rows of personIDs on the photo that are living  

    $query = "    
    SELECT l.personID 
    FROM $medialinks_table l
    JOIN $people_table p ON l.personID = p.personID 
    WHERE l.mediaID = $imgrow[mediaID] 
    AND p.living = 1
     ";
    $presult = mysql_query($query) or die ("$text[cannotexecutequery]: $query");
    $rows = mysql_num_rows( $presult );
    mysql_free_result( $presult );

    // if no rows are returned, there are no living on the photo, so let's display it  
    if ($rows == 0) {
        break;
    }
}
sqluser
  • 5,502
  • 7
  • 36
  • 50
mzurhorst
  • 91
  • 1
  • 9
  • Why isn't the 'alwayson' in the WHERE of the first query? After that then it is a simple JOIN. – user2864740 May 23 '15 at 22:38
  • Please read https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php – Michas May 23 '15 at 23:34
  • Thanks, Michas. I am transitioning to a $wpdb database class in WordPress. This is why I looked at the code at all. – mzurhorst May 24 '15 at 06:36
  • Regarding alwayson: it is not a clear search criteria. For these photos, I am allowed to display them even when people on it are alive. But when alwayson is not true, I may still be allowed to display when there are no living people on it. – mzurhorst May 24 '15 at 06:41
  • I just found the MySQL CASE statement. Will try to catch the two conditions as cases. – mzurhorst May 24 '15 at 07:24

1 Answers1

0
SELECT DISTINCT m.mediaID
              , m.description
              , m.path
              , m.alwayson
              , m.usecollfolder
              , m.mediatypeID 

              , l.personID 

           FROM $media_table m

           JOIN $medialinks_table l
             ON l.mediaID = m.mediaID
           JOIN $people_table p
             ON l.personID = p.personID 
          WHERE m.mediatypeID = 'photos'
            AND p.living = 1

          ORDER 
             BY RAND();
Strawberry
  • 33,750
  • 13
  • 40
  • 57