PHP / MySQL关系/回声场

Question

I'm trying to build my 1st relationship database and it looks like this so far:

Relationships Table

Partners Table

Location Table

I want to echo all the row info including the related location names on my page, how would I get the following to echo?

ID: 2 Name: Salisbury Removals Locations: Salisbury
ID: 4 Name: Inbetween Removals Locations: Salisbury, Southampton
ID: 5 Name: Southampton Removals Locations: Southampton

=====SOLVED!=====

$sql = "SELECT partner_id, partner_name, email_address, active FROM partners WHERE active ='yes' ORDER BY partner_id ASC";


                                $connect->query($sql);

                                if ($partners = $connect->query($sql)) {

                                    foreach ($partners as $partner) {

                                        echo '<li><ul>';
                                        echo '<li>' . $partner['partner_id'] . '</li>';
                                        echo '<li>' . $partner['partner_name'] . '</li>';
                                        echo '<li>' . $partner['email_address'] . '</li>';
                                        echo '<li>' . $partner['active'] . '</li>';

                                        // START GET LOCATIONS FROM RELATED TABLE
                                        echo '<ul>';

                                        $sql2 = "SELECT p.partner_name AS Name, p.partner_id AS ID, l.location_name AS Locations from partners_locations r, partners p, locations l WHERE p.partner_id = r.partner_id AND l.location_id = r.location_id AND r.partner_id =" . $partner['partner_id'] . "";

                                        $connect->query($sql2);

                                        if ($locations = $connect->query($sql2)) {

                                            foreach ($locations as $location) {

                                                echo '<li>' . $location['Locations'] . '</li>';
                                            }

                                        } else {

                                            echo "Error: No Locations<br>";

                                        }

                                        echo '</ul>';
                                        // END GET LOCATIONS FROM RELATED TABLE

                                        echo '</ul></li>';

                                    }

                                } else {

                                     echo "Error: No Active Partners<br>";

                                }

Answer 1

The best thing you could do is make a new table to contain the partner_id and location_id.

tbl_relationships_new

Pros for this approach :-

1).When you need to remove a location from a partner, you wouldn't need to edit the column locations. You could simply delete an entry from this new table.

2). When you need to add more data in the locations field, you could simply just insert into the new table, which is rather easy than having to update partners.locations.

Now, you could use easy left joins to get the required data. SQL query for my table solution.

SELECT t.*,p.*,l.* FROM tbl_relationships_new t, partners p, locations l LEFT JOIN 
    partners 
    ON
    t.partner_id = p.partner_id 
    LEFT JOIN 
    locations 
    ON 
    l.location_id = t.location_id 
    WHERE
    t.partner_id = 2

UPDATE

Here are the queries based on your table structure.

1). ID: 2 Name: Salisbury Removals Locations: Salisbury

SELECT p.partner_name AS Name, p.partner_id AS ID, l.location_name AS Locations from relationships r, partners p, locations l WHERE p.partner_id = r.partner_id AND l.location_id = r.location_id AND r.partner_id = 2

2). When there are 2 locations.

SELECT p.partner_name AS Name, p.partner_id AS ID, l.location_name AS Locations from relationships r, partners p, locations l WHERE p.partner_id = r.partner_id AND l.location_id = r.location_id AND r.partner_id = 5

UPDATE

Solution without explicitly mentioning an ID.

SELECT p.partner_name AS Name, p.partner_id AS ID, l.location_name AS Locations from relationships r, partners p, locations l WHERE p.partner_id = r.partner_id AND l.location_id = r.location_id