通过ID的内部联接显示名称

本文介绍了通过ID的内部联接显示名称的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有这个查询:

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,

我尝试过:

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

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是国家表中ID的前例键 segment_id是细分表中ID的外键

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

我的数据库架构:

表格名称:国家/地区细分

table name: countrysegments

id country_id segment_id

表格名称:国家/地区

id country

表名:细分

id segment

这在class.php中:public function select(){ $stmt = $this->conn->prepare("SELECT country FROM国家/地区") or die($this->conn->error); if($stmt->execute()){ $result = $stmt->get_result(); return $result; } }

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; } }

，这在index.php中

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

请我全力帮助您

推荐答案

我的版本带有一些代码. ;)

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. :)