如何从MySQL表中获取多对多关系数据，并使用php进行排序和显示？

I am very new to php and mysql. I am trying to learn as I go and am stuck on this issue.

我是php和mysql的新手。我正在努力学习，因为我坚持这个问题。

I have a many-to-many relationship. The tables are: users and state. Many companies in the users table operate in many states and each state has many different companies. Each company on the user table has a unique id that is stored on the state table. The state table then has a column for the state name that the company operates in. A new row is created in the state table for each state the company is in. That all seems to work ok.

我有多对多的关系。表格是：用户和州。用户表中的许多公司在许多州运营，每个州都有许多不同的公司。用户表上的每个公司都有一个存储在状态表中的唯一ID。然后状态表有一个公司所在州名称的列。在状态表中为公司所处的每个州创建一个新行。这一切似乎都可以正常工作。

I wrote the following php code to pull all the company id's from the state table for any given state ($state) and then display all the company names (pulled from the users table with the company id) that operate in the given state.

我编写了以下php代码，从状态表中为任何给定状态（$ state）提取所有公司ID，然后显示在给定状态下运行的所有公司名称（从带有公司ID的users表中提取）。

My problem is that I would like to order the list alphabetically of companies that operate in any given state. This code will not do that. Can anyone offer a better way to do this that will allow the list to be ordered (or that will just generally be more efficient and better)?

我的问题是，我想按字母顺序排列在任何特定州运营的公司。此代码不会这样做。任何人都可以提供更好的方法来执行此操作，以便列出订单（或者通常会更高效和更好）？

$state=$_GET['state'];
echo $state;
$aid=array();
$result=mysql_query("SELECT * FROM state WHERE state='$state'");
while($row=mysql_fetch_assoc($result)){
$v=$row['company_id'];
array_push($aid,$v);};

foreach($aid as $val){
$result1=mysql_query("SELECT * FROM users WHERE company_id='$val'");
$row1=mysql_fetch_assoc($result1);
$company=$row1['company'];
echo $company.'<br/>';};

The table structures are:

表结构是：

users

用户

company_id
company
(other columns that are not relevant)

state

州

index
company_id
state
(no additional columns)

3 个解决方案

#1

Try this SQL statement:

试试这个SQL语句：

SELECT 
    s.state, u.company 
FROM 
    states s, users u 
WHERE 
    s.company_id = u.company_id 
AND 
    s.state = "$state" 
GROUP BY 
    s.state 
ORDER BY 
    s.state, u.company

This should give you an alphabetic list of companies per state selected. The ORDER BY uses the state first in case you decide to expand your query to get more than one states.

这应该为您提供每个州选择的公司的字母列表。如果您决定扩展查询以获得多个状态，ORDER BY将首先使用状态。

#2

You typically would introduce a third table in a relational database to express a many-to-many relationship. The table could be users_states and have only to fields: user_id and state_id. So you tables should probably look like this:

您通常会在关系数据库中引入第三个表来表达多对多关系。该表可以是users_states，只有字段：user_id和state_id。所以你的表应该看起来像这样：

You do the sort in your SQL. So the query might look like:

您在SQL中进行排序。所以查询可能如下所示：

SELECT u.*, s.*
FROM users AS u
INNER JOIN users_states AS us ON u.user_id = us.user_id
INNER JOIN states AS s ON us.state_id = s.state_id
WHERE s.state_id = ?
ORDER BY u.name ASC

Here I am assuming that a field name is what you want to sort by and that you will filter on some value passed for state_id.

在这里，我假设一个字段名称是您要排序的，并且您将过滤为state_id传递的某个值。

#3

See this URL:-

看到这个网址： -

Many-to-many relationship select and order by

多对多关系选择和排序

Probably something similar to this:

可能与此类似：

SELECT
    a.person_id
FROM
    table AS a,
    table AS b
WHERE
    a.person_id = b.person_id AND
    a.favorite_id = 1 AND
    b.favorite_id = 2
ORDER BY
    ( IF( a.is_main_favorite = "y", 1, 0 )
      +
      IF( b.is_main_favorite = "y", 1, 0 ) ) DESC

By the way: You may want to store 1/0 instead of y/n in the database so that you won't need the IF call

顺便说一下：您可能希望在数据库中存储1/0而不是y / n，这样您就不需要IF调用

#1