CODEWITHSUNDEEP
phpmysql
zuk1
zuk1

Reputation: 18369

PHP MySQL Getting Data Out of Multiple Tables

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.

Upvotes: 0

Views: 3653

Answers (4)

PatrikAkerstrand
PatrikAkerstrand

Reputation: 45721

How about this one?

-- // Selects all the columns from both entry-locations and entry
SELECT * 
FROM entry-locations
JOIN entry e ON e.id = el.entry-id
WHERE el.location = 'France';

-- // To just get the entry data for matching records: (Remove DISTINCT if you
-- // don't mind, or want, each entry with multiple locations in the result set
-- // multiple times)
SELECT DISTINCT e.* 
FROM entry-locations el
JOIN entry e ON e.id = el.entry-id
WHERE el.location = 'France'

Edit: Ok, so I tried removing the location table, and instead used entry-location.location as the name of the location.. Is this correct?

Upvotes: 5

gnud
gnud

Reputation: 78528

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.

Upvotes: 2

julioc
julioc

Reputation: 109

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'

Upvotes: 0

Fortega
Fortega

Reputation: 19682

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

Upvotes: 0

Related Questions