PHP Mysql按顺序选择查询对

Question

我正在为一所舞蹈学校制作一个客户数据库应用程序。

我需要显示所有参与同一舞蹈水平的客户的概述。为此，我连接了三个表(请看下面的查询)，每个客户在tabel CRM_CONTACTS中都有一个唯一的ID，并且在它的行中还有一个对他或她的合作伙伴(PARTNER_ID)的引用。

表CRM_CONTACTS

ID   CONTACTS_LNAME
1    VON KLOMPENBURG
2    Mc Donalds
3    MC Adams
4    Mr X

然后我有CRM_PRODUCTS ID PRODUCTS_NAME

 1     Beginners     2     intermediate     3     advanced

然后是我将产品/级别分配给联系人并指示其合作伙伴的表

ID   CONTACTS_ID    PRODUCTS_ID   PARTNER_ID
1    1              1             4
2    2              1             3
3    3              1             2
4    4              1             1

现在我想收到一个基于parter_id的按夫妇排序的列表，所以对于初学者来说，我会得到一个这样的列表1 VON KLOMPENBURG 2 Mr X 3 Mc Donalds 4 Mc Adams

下面是我的select语句

 $result = mysqli_query($coni,"SELECT CRM_PRODUCTS_PURCHASE.ID, CONTACTS_ID, 
CRM_PRODUCTS.PRODUCTS_NAME, 
CRM_PRODUCTS.PRODUCTS_PRICE,CRM_PRODUCTS_PURCHASE.PARTNER_ID, 
CRM_PRODUCTS_PURCHASE.PARTNER_NAME, 
PRODUCTS_PURCHASE_REMARKS,PRODUCTS_PURCHASE_DISCOUNT, 
PRODUCTS_PURCHASE_PAIDBYBANK,PRODUCTS_PURCHASE_PAIDBYCASH, 
CRM_CONTACTS.CONTACTS_LNAME, CRM_CONTACTS.CONTACTS_FNAME 
FROM CRM_PRODUCTS_PURCHASE
LEFT JOIN CRM_CONTACTS ON CRM_PRODUCTS_PURCHASE.CONTACTS_ID = CRM_CONTACTS.ID 
LEFT JOIN CRM_PRODUCTS ON CRM_PRODUCTS_PURCHASE.PRODUCTS_ID = CRM_PRODUCTS.ID
WHERE CRM_PRODUCTS_PURCHASE.PRODUCTS_ID = '". $PRODUCTS_ID . "'");

Answer 1

你可以这样做：

$result = mysqli_query($coni,"SELECT CRM_PRODUCTS_PURCHASE.ID, CONTACTS_ID, 
CRM_PRODUCTS.PRODUCTS_NAME, 
CRM_PRODUCTS.PRODUCTS_PRICE,CRM_PRODUCTS_PURCHASE.PARTNER_ID, 
CRM_PRODUCTS_PURCHASE.PARTNER_NAME, 
PRODUCTS_PURCHASE_REMARKS,PRODUCTS_PURCHASE_DISCOUNT, 
PRODUCTS_PURCHASE_PAIDBYBANK,PRODUCTS_PURCHASE_PAIDBYCASH, 

CRM_CONTACTS.CONTACTS_LNAME, CRM_CONTACTS.CONTACTS_FNAME 
FROM CRM_PRODUCTS_PURCHASE
LEFT JOIN CRM_CONTACTS ON CRM_PRODUCTS_PURCHASE.CONTACTS_ID = CRM_CONTACTS.ID 
LEFT JOIN CRM_PRODUCTS ON CRM_PRODUCTS_PURCHASE.PRODUCTS_ID = CRM_PRODUCTS.ID
WHERE CRM_PRODUCTS_PURCHASE.PRODUCTS_ID = '". $PRODUCTS_ID . "'
ORDER BY IF (ID < PARTNER_ID, ID, PARTNER_ID)");

你的顾客将会被成双成对地订购。