I have two tables: locations and listings.locations
\[code\]id\[/code\] \[code\]title\[/code\] \[code\]address\[/code\] \[code\]latitude\[/code\] \[code\]longitude\[/code\]listings
\[code\]id\[/code\] \[code\]location\[/code\] \[code\]info\[/code\] \[code\]status\[/code\]\[code\]SELECT locations.title, locations.address, ( 3959 * acos( cos( radians('".$center_lat."') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('".$center_lng."') ) + sin( radians('".$center_lat."') ) * sin( radians( latitude ) ) ) ) AS distance FROM locations ORDER BY distance\[/code\]This will list the locations in order by location with the users provided latitude and longitude. Works perfect, but what I really want to do is.. [*]List one "listings" per location, and have the locations remain in order. [*]If a location has more then one "listings" have it be completely random.Would it be better to do this all in one SQL query?Or populate all the locations that have atleast one "listings", then use another query to select a random "listings" for that location?UPDATEProvided create table:\[code\]CREATE TABLE `listings` ( `id` int(11) NOT NULL AUTO_INCREMENT, `token` varchar(4) DEFAULT NULL, `location` varchar(45) DEFAULT NULL, `info` varchar(45) DEFAULT NULL, `status` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;CREATE TABLE `locations` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(45) DEFAULT NULL, `address_street` varchar(45) DEFAULT NULL, `addrees_city` varchar(45) DEFAULT NULL, `address_state` varchar(45) DEFAULT NULL, `address_zip` varchar(45) DEFAULT NULL, `latitude` decimal(10,6) DEFAULT NULL, `longitude` decimal(10,6) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;\[/code\]
\[code\]id\[/code\] \[code\]title\[/code\] \[code\]address\[/code\] \[code\]latitude\[/code\] \[code\]longitude\[/code\]listings
\[code\]id\[/code\] \[code\]location\[/code\] \[code\]info\[/code\] \[code\]status\[/code\]\[code\]SELECT locations.title, locations.address, ( 3959 * acos( cos( radians('".$center_lat."') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('".$center_lng."') ) + sin( radians('".$center_lat."') ) * sin( radians( latitude ) ) ) ) AS distance FROM locations ORDER BY distance\[/code\]This will list the locations in order by location with the users provided latitude and longitude. Works perfect, but what I really want to do is.. [*]List one "listings" per location, and have the locations remain in order. [*]If a location has more then one "listings" have it be completely random.Would it be better to do this all in one SQL query?Or populate all the locations that have atleast one "listings", then use another query to select a random "listings" for that location?UPDATEProvided create table:\[code\]CREATE TABLE `listings` ( `id` int(11) NOT NULL AUTO_INCREMENT, `token` varchar(4) DEFAULT NULL, `location` varchar(45) DEFAULT NULL, `info` varchar(45) DEFAULT NULL, `status` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;CREATE TABLE `locations` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(45) DEFAULT NULL, `address_street` varchar(45) DEFAULT NULL, `addrees_city` varchar(45) DEFAULT NULL, `address_state` varchar(45) DEFAULT NULL, `address_zip` varchar(45) DEFAULT NULL, `latitude` decimal(10,6) DEFAULT NULL, `longitude` decimal(10,6) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;\[/code\]