MySQL: Join two tables, select one row at random.

Question

First off, I created a screen cast, in order to explain what I have and what I'm attempting to create. Much easier to understand.

Please view the screen cast here: http://www.youtube.com/v/lZf3S3EGHDw?fs=1&hl=en_US&rel=0&hd=1

Tables:

CREATE TABLE `locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(45) DEFAULT NULL,
  `latitude` decimal(10,6) DEFAULT NULL,
  `longitude` decimal(10,6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO `locations` (`id`,`title`,`latitude`,`longitude`)
VALUES
    (1,'Randall Automotive Car Repair',42.729642,-84.515524),
    (2,'Belle Tire',42.662458,-84.538177),
    (3,'Better Buy Muffler & Breaks',42.740845,-84.589541),
    (4,'Kwik Car Wash',42.721221,-84.545926);


CREATE TABLE `listings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `token` varchar(4) DEFAULT NULL,
  `location` varchar(45) DEFAULT NULL,
  `info` varchar(70) DEFAULT NULL,
  `status` varchar(45) DEFAULT NULL,
  `auto_inactive` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO `listings` (`id`,`token`,`location`,`info`,`status`,`auto_inactive`)
VALUES
    (35,'4uaJ','1','All employees are NSA certified.','active','0'),
    (36,'RdcX','1','Family Owned and Operated','active','0'),
    (37,'WuaZ','1','Also repair Small engines','active','0'),
    (38,'2jxD','2','Open on the weekends.','active','0'),
    (39,'Xsu4','2','Two locations in this town.','active','0'),
    (40,'p9cB','2','Advertise on Tiger\'s Baseball','active','0'),
    (41,'mYa3','2','Started right here in Michigan','active','0'),
    (42,'Q8g5','3','Building built in 1997','active','0'),
    (43,'l734','3','Great ratings from BBB','active','0'),
    (44,'7cLY','4','Open in the Winter','active','0'),
    (45,'gtlU','4','Largest car wash in town','active','0'),
    (46,'fEjK','4','Owned and Operated by John Smith','active','1285614174'),
    (47,'dRcu','4','Opened in 1987','inactive','0');



<?php

include_once('include.php'); // Calls the Mysql Database`
ini_set('date.timezone', 'America/Detroit');


$user_latitude = 42.7160084;
$user_longitude = -84.5615018;


$sql =  mysqli_query($mysqli, "SELECT 
                                        loc.id, 
                                        loc.title, 
                                        ( 3959 * acos( cos( radians('".$user_latitude."') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('".$user_longitude."') ) + sin( radians('".$user_latitude."') ) * sin( radians( latitude ) ) ) ) AS distance

                                FROM 
                                        locations loc 

                                WHERE EXISTS(SELECT NULL FROM listings li
                                        WHERE li.location = loc.id 
                                        AND li.status = 'active' 
                                        AND (li.auto_inactive > '".time()."' OR li.auto_inactive = '0')) 

                                ORDER BY distance");


while($locations = mysqli_fetch_array($sql)) {

        $listings = mysqli_fetch_array(mysqli_query($mysqli, "SELECT listings.token, listings.info FROM listings WHERE (listings.location = '".$locations['id']."') AND listings.status = 'active' AND (listings.auto_inactive > '".time()."' OR listings.auto_inactive = '0') ORDER BY RAND()"));


        echo '<a href="listing.php?id='.$listings['token'].'"><h2>'.$locations['title'].'</h2></a>';

        echo '<h5>Distance: '.sprintf ('%.2f', $locations['distance']).' mi</h5>';
        echo '<p>'.$listings['info'].'</p>';
        echo '<hr/>';
}

?>

Please let me know if you need anything clarified. Thank you!

Answer 1

I don't know how well it will perform or even if it will work, but it appears you can join in a sub query and limit/order by rand on that. I found this question/answer here that I am basing this off of. try something like this:

SELECT 
    loc.id, 
    loc.title, 
    ( 3959 * acos( cos( radians('".$user_latitude."') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('".$user_longitude."') ) + sin( radians('".$user_latitude."') ) * sin( radians( latitude ) ) ) ) AS distance,
    listings.token,
    listings.info
FROM
    locations loc
JOIN
    (SELECT
             location,
             token,
             info
         FROM listings
         WHERE (listings.location=loc.id)
             AND status = 'active'
             AND (auto_inactive > '".time()."' OR listings.auto_inactive = '0')
         ORDER BY RAND() LIMIT 1) listings
     ON listings.location=loc.id
ORDER BY distance

edit: Also, if this does work, the join will limit the locations to only those with a listing so you wouldn't need to check that.

Answer 2

This one works:

SELECT t1.title, t2.token, t2.info
    FROM
        (SELECT loc.id AS id, loc.title AS title,( 3959 * acos( cos( radians('".$user_latitude."') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('".$user_longitude."') ) + sin( radians('".$user_latitude."') ) * sin( radians( latitude ) ) ) ) AS distance
            FROM locations loc
            WHERE EXISTS(SELECT NULL FROM listings li
                    WHERE li.location = loc.id
                        AND li.status = 'active'
                        AND (li.auto_inactive > UNIX_TIMESTAMP() OR li.auto_inactive = '0'))
        ) t1
    JOIN
        (SELECT DISTINCT(listings.location) AS location, listings.token AS token, listings.info AS info
            FROM listings
            WHERE listings.status = 'active'
                AND (listings.auto_inactive > UNIX_TIMESTAMP() OR listings.auto_inactive = '0')
            ORDER BY RAND()
        ) t2
    ON t1.id=t2.location
        GROUP BY t2.location
        ORDER BY t2.location ASC;

I also would suggest altering the listings table to make the location, status and auto_inactive columns type int - there's no point in using varchar for them.