Display items by location. Mysql Select.

Question

I have two MySQL tables, "locations" and items":

locations
  `id` `name` `address` `latitude` `longitude`

Now I use a MySQL SELECT that allows a user to enter in their latitude and longitude and it will sort the locations by distance. That works perfect.

Now I have a list of items:

items
  `id` `location` `title` `description` `display`

Now I want to display the items for each location if the display of that item = true. I want this is be efficient, because some locations don't have any items, or no items set to display = true.

Answer 1

this query will give you a list of items ordered by location

SELECT items.*, locations.* FROM items JOIN locations ON locations.id = items.location WHERE items.display = 'true' ORDER BY locations.id;

Answer 2

select location.name, items.title, items.description
from location, items
where location.id = items.location
and items.display = 'true'
order by location.name

I think that does what you want, but it's hard to tell without additional information. For example, is the location field in the items table actually a foreign key to the location table (it's not obvious from context).