show name from inner join with the id [duplicate]

问题:

This question is an exact duplicate of:

• inner join between 3 tables 3 answers

i have this query :

select * from countrysegments 
   inner join country on countrysegments.country_id  = country.id
   inner join segments on countrysegments.segment_id = segments.id

all i need to know is how to show the name of the country inside table country,

and for each country show all the segments available,nothing worked with me, if someone could help me would me great, thank u

i tried a lot with no answers,

i tried this:

select * from countrysegments inner join country on countrysegments.country_id = country.country inner join segments on countrysegments.segment_id = segments.segment

iknow im far from the correct answers but please can anyone help?

country_id is a foregin key of the id in country table segment_id is a foreign key of the id in segments table

my database schema:

table name: countrysegments

id      country_id    segment_id

table name: country

id      country

table name: segments

id          segment

this is in class.php:public function select(){ $stmt = $this->conn->prepare("SELECT country FROMcountry") or die($this->conn->error); if($stmt->execute()){ $result = $stmt->get_result(); return $result; } }

and this in index.php`

                            <th class="text-center">country</th>

                            <th class="text-center">segments</th>
            </thead>
            <tbody>
            <?php

                require 'class.php';

                $conn = new db_class();
                $read = $conn->select();

                while($fetch = $read->fetch_array(MYSQLI_ASSOC)){ 
                                      foreach($fetch as $field=>$value){
   echo '<tr><td>' . $value . '</td>';
}


}       


            ?>




            </tbody>
        </table>`

with this solution i only have the query that show me the countries but i need to show in every country all the segments available using a dropdown menu

please i need your help all

回答1:

My version with some code. ;)

<table>
<thead>
    <tr>
        <th class="text-center">country</th>
        <th class="text-center">segments</th>
    </tr>
</thead>
<tbody>
<?php
    require 'class.php';

    $conn = new db_class();
    $read = $conn->select(); // <-- here you'd call a query like this:
/*
"SELECT country.id AS countryID, country.country, segments.id AS segmentID, segments.segment
FROM countrysegments 
inner join country on countrysegments.country_id  = country.id
inner join segments on countrysegments.segment_id = segments.id
ORDER BY country.id, segments.id "
*/

// Then do some transformation for easier readability when creating the table!!
    $countryId = 0;
    $countries = [];
    while($fetch = $read->fetch_array(MYSQLI_ASSOC)) {
        if($countryId != $fetch['countryID']){
            $countryId = $fetch['countryID'];

            $countries[$countryId] = [
                'country' => $fetch['country'],
                'segments' => [],
            ];
        }

        $countries[$countryId]['segments'][] = [
            'segmentID' => $fetch['segmentID'],
            'segment' => $fetch['segment'],
        ];
    }

    // Here you can read the code to build the table easily ;)
    foreach($countries as $country){
        echo "<tr>";
            echo "<td>{$country['country']}</td>";
            echo "<td><select>";
            foreach($country['segments'] as $segment){
                echo "<option value=\"{$segment['segmentID']}\">{$segment['segment']}</option>";
            }
            echo "</select></td>";
        echo "</tr>";
    }
    ?>
</tbody>
</table>

Hope this helps. :)

回答2:

select country.id, country.country,segment.segment from countrysegments 
inner join country on countrysegments.country_id  = country.id
inner join segments on countrysegments.segment_id = segments.id where country.id = (any id of country).

it will give all segments of require country or you can remove where clause to get all segments of al countries and then you can play with results.