PHP MySQL Getting Data Out of Multiple Tables

Question

I asked a similar question yesterday which may have been poorly worded, either way I didn't understand and this is something I really need to crack :) I've never done it before and would be very useful for so many of my projects.

This is for a directory website. I have three tables: entry, location, and entry-locations. entry contains information about a building such as name, address, image, etc. location is simply a list of possible locations each building could be. The location table is pretty much irrelevant for this example, it just contains information about the location which I could display on other areas of the site.

entry-locations is a table which links the entries to the locations. It only has two fields, entry-id and location... If you're wondering why I need a seperate table for this is because the same building could have multiple locations (don't ask).

Basically, what I need to do is display listings from each location it's own page. For example, I need to list every building in France, so the query needs to go through the entry-locations table returning every record with the location 'France', then it needs to pull all the data from the entry table corresponding to the entry-id's returned.

I'm sure there is a way to do this with one query and would be extremely greatful if I could be shown how, I could replicate this in so many projects.

Answer 1

select * from entry where id in (select entry-id from entry-locations)

Answer 2

If I got your idea, you need: select e.* frmm entry join entry-locations as as l ON l.entry-id=e.id WHERE l.location='France'

Answer 3

Imagine you have this data:

Entry:

|id|name|
| 1|Foo |
| 2|Bar |

Entry-Location:

|entry-id|location|
|1       |France  |
|2       |Greece  |
|2       |France  |

This is how I understand the tables from your description. A more common approach is to have

Entry(id,name)
Location(id,name)
Entry_Location(entry_id, location_id)

This is also the source of some of the confusions in the other posts, I think.

Now, ask MySql to fetch data from both tables, where the id's match up.

SELECT entry.* 
FROM `entry`, `entry-location` as el
WHERE entry.id = el.`entry-id`
AND el.location = 'France';

MySql now treats your data like one table, looking like this:

|entry.id|entry.name|el.location|
|       1|       Foo|     France|
|       1|       Foo|     Greece|
|       2|       Bar|     France|

And from that table it selects the entries where el.location = 'France', and returns the specified fields.

This query fetches all the fields from the entry table that matches the requirements you set. First it makes MySql think of the two tables as one table, by SELECT-ing from both of them.

Have a look at MySql's SELECT reference.