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.

PatrikAkerstrand · Accepted Answer · 2009-06-02 17:09:13Z

5

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?

edited Jun 2, 2009 at 17:09

answered Jun 2, 2009 at 13:11

PatrikAkerstrand

45.8k12 gold badges82 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

zuk1 Over a year ago

Like I said the location table is completely irrelevant, it doens't need to be in the query at all. All I need is (in words): "select * from entry-locations where location = 'France'", but then I need to get the entry id's from the resulting records and display data from the entry table for each one...

PatrikAkerstrand Over a year ago

How can you say that it is irrelevant when you want to constrain your results based on the location's name?

PatrikAkerstrand Over a year ago

And no, you get the aggregated data from all tables you join with, ie. all columns. If you just want the data in the entry-table, see my edit above

zuk1 Over a year ago

Pretend there is only two tables, 'entry-locations' and 'entry'. 'entry-locations' contains entry-id and location, entry-id corresponds to an ID in the entry table, location is just a word. Do you understand now?

PatrikAkerstrand Over a year ago

I'm not sure.. but I gave it a shot. If this is not correct, could you provide SHOW CREATE TABLE for each of the involved tables?

|

gnud · Accepted Answer · 2009-06-02 16:36:06Z

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.

Fortega · Accepted Answer · 2009-06-02 13:05:04Z

0

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

answered Jun 2, 2009 at 13:05

Fortega

19.7k15 gold badges78 silver badges115 bronze badges

1 Comment

zuk1 Over a year ago

Could you offer an alternative?

julioc · Accepted Answer · 2009-06-02 13:13:11Z

0

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'

answered Jun 2, 2009 at 13:13

julioc

1095 bronze badges

Collectives™ on Stack Overflow

PHP MySQL Getting Data Out of Multiple Tables

4 Answers 4

9 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

9 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related