PHP MySQL Joining Tables

Question

I'm trying to create a directory and I have two tables, entry and location. Each entry will be able to have multiple locations so I'm assuming the best idea (correct if wrong) is to create a third table where I specify 'links'. Example:

Marly:France
Karla:Argentina
Smith:USA
Katie:France
Smith:United Kingdom
Katie:USA

When I want to list people from France I'd go into that third table and pull up names where location = France. I'm fine with doing everything up to this point, but now I need to retrieve data from the entry table for each Name of each person in France (according to the third table). I'm pretty sure I've seen this done before. Any pointers? :D

chaos · Accepted Answer · 2009-06-02 17:11:17Z

1

SELECT `entry`.*
FROM `entry`
JOIN `entry-locations` ON `entry-locations`.`entry-id` = `entry`.`name`
WHERE `entry-locations`.`location` = 'Aberdeen'

$res = mysql_query('all-that-stuff-above');
while($row = mysql_fetch_assoc($res)) {
    echo $row['name'] . "\n";
}
mysql_free_result($res);

edited Jun 2, 2009 at 17:11

answered Jun 1, 2009 at 15:02

chaos

125k34 gold badges306 silver badges310 bronze badges

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

9 Comments

Eric Over a year ago

I'd limit the columns returned on that puppy, and I'd add a few NOT IS NULL clauses in the WHERE section. Otherwise, you'll be getting some unintended results.

chaos Over a year ago

I only know for sure that OP wants the entry name, so I limited to that. As to the rest, you mean IS NOT NULL, and they aren't necessary; NULL will never match 'France'.

Kuroki Kaze Over a year ago

Isn't INNER JOIN better in this case? If we want, e.g., to list people from France, we don't want to see ppl w/o countries.

zuk1 Over a year ago

I actually need all the information from the entry table for each name located in 'France'.

chaos Over a year ago

@Kuroki Kaze: Yeah. I doubt it actually makes a difference here, but it's better practice. Edited. @zuk1: Okay, edited accordingly.

|

Ionuț G. Stan · Accepted Answer · 2009-06-02 17:13:44Z

1

SELECT
    entryt.name AS name,
    locationt.location AS location
FROM locationt, entryt
WHERE locationt.location = "France"

Remember: Change the names I used there:

I named the entry table as "entryt"
And the location table as "locationt"

Should return "name" with all the people that live in France.

Pretty sure that would work. Still, correct me if I'm wrong.

edited Jun 2, 2009 at 17:13

Ionuț G. Stan

180k19 gold badges196 silver badges206 bronze badges

answered Jun 1, 2009 at 15:08

Daniel Sorichetti

1,9511 gold badge21 silver badges35 bronze badges

Comments

StackKrish · Accepted Answer · 2009-06-01 16:00:09Z

0

SELECT * FROM entry WHERE name in (SELECT name FROM links WHERE location = 'FRANCE');

I have assumed the table names as 'entry' & 'links' and column names as 'entry.name' and 'links.location'.

answered Jun 1, 2009 at 16:00

StackKrish

7224 silver badges8 bronze badges

Collectives™ on Stack Overflow

PHP MySQL Joining Tables

3 Answers 3

9 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related