时间戳

首页 > 解决方案 > 如何从同一个表中获取多个列

php - 如何从同一个表中获取多个列

问题描述

我有 4 个 $ 查询连接，如下所示：

这选择一个称为形状的属性：

    $sql = "
SELECT p.`ID` AS 'Product ID', 
       p.`post_title` AS 'Product Name', 
       t.`term_id` AS 'Attribute Value ID', 
       REPLACE(REPLACE(tt.`taxonomy`, 'pa_', ''), '-', ' ') AS 'Attribute Name', 
       t.`name` AS 'Shape' 
       FROM `wp_posts` AS p 
       INNER JOIN `wp_term_relationships` AS tr ON p.`ID` = tr.`object_id` 
       INNER JOIN `wp_term_taxonomy` AS tt ON tr.`term_taxonomy_id` = tt.`term_id` AND tt.`taxonomy` LIKE 'pa_shape%'
       INNER JOIN `wp_terms` AS t ON tr.`term_taxonomy_id` = t.`term_id` 
       WHERE p.`post_type` = 'product' 
       AND p.`post_status` = 'publish' ORDER BY p.`ID`";

要从同一表和同一列中选择“清晰度”：

$sql .= "SELECT p.`ID` AS 'Product ID', 
       p.`post_title` AS 'Product Name', 
       t.`term_id` AS 'Attribute Value ID', 
       REPLACE(REPLACE(tt.`taxonomy`, 'pa_', ''), '-', ' ') AS 'Attribute Name', 
       t.`name` AS 'Clarity' 
       FROM `wp_posts` AS p 
       INNER JOIN `wp_term_relationships` AS tr ON p.`ID` = tr.`object_id` 
       INNER JOIN `wp_term_taxonomy` AS tt ON tr.`term_taxonomy_id` = tt.`term_id` AND tt.`taxonomy` LIKE 'pa_clarity%'
       INNER JOIN `wp_terms` AS t ON tr.`term_taxonomy_id` = t.`term_id` 
       WHERE p.`post_type` = 'product'"

并且很少有其他选择语句，例如这个。我试图将它们合并在一起，但我做不到。现在我正在运行多个查询并希望让它与以下内容一起工作，但我没有返回任何值：

$output .= "<table><thead><tr><th>Carat</th><th>Color</th><th>Cut</th><th>Shape</th><th>Clarity</th></tr></thead><tbody>";


if (mysqli_multi_query($con,$sql))
{
  do
    {
    // Store first result set
    if ($result=mysqli_store_result($con)) {
      // Fetch one and one row
      while ($row=mysqli_fetch_row($result))
        {
        $output .= "<td>" . $row['Carat'] . "</td><td>" . $row['Color'] . "</td><td>" . $row['Cut'] . "</td><td>" . $row['Shape'] . "</td><td>" . $row['Clarity'] . "</td></tr>" ;
                }
              // Free result set
              mysqli_free_result($result);
              }
            }
          while (mysqli_next_result($con));
        }

        mysqli_close($con);
        $output .= "</table>";



        return $output;

变量输出用于更大的功能以及我如何在页面上输出它们。请就为什么 mysqli_multi_query() 不起作用的任何建议？或者我是否可以将这样的查询合并为一个。提前感谢所有帮助。

标签： phpmysqlsql

解决方案

如果您只是在不同的查询中获取单独的属性，那么您可以通过修改ON连接中的子句来实现。

1：每个产品每个属性一个记录

SELECT 
    p.`ID` AS 'Product ID', 
    p.`post_title` AS 'Product Name', 
    t.`term_id` AS 'Attribute Value ID', 
    REPLACE(REPLACE(tt.`taxonomy`, 'pa_', ''), '-', ' ') AS 'Attribute Name', 
    t.`name` AS 'Attribute Value' 
 FROM `wp_posts` AS p 
 INNER JOIN `wp_term_relationships` AS tr ON p.`ID` = tr.`object_id` 
 INNER JOIN `wp_term_taxonomy` AS tt ON tr.`term_taxonomy_id` = tt.`term_id` 
    AND (tt.`taxonomy` LIKE 'pa_clarity%' OR tt.`taxonomy` LIKE 'pa_shape%') -- If require add more attribute here and you are good
 INNER JOIN `wp_terms` AS t ON tr.`term_taxonomy_id` = t.`term_id` 
 WHERE p.`post_type` = 'product';

Output:
+-----------------------------------------------------------------------------------+
| Product ID | Product Name | Attribute Value ID | Attribute Name | Attribute Value |
| p1         | p1_name      | 10                 | Clarity        | Not clear       |
| p1         | p1_name      | 11                 | Shape          | Square          |
| p2         | p2_name      | 10                 | Clarity        | Clear           |
| p2         | p2_name      | 11                 | Shape          | Circle          |
+-----------------------------------------------------------------------------------+

注意：如果您仅具有ORDER BY或GROUP BY仅针对特定属性，则此处将不起作用。如果您申请，它将适用于您包含在此联接中的所有属性。

2：每个产品一条记录，所有属性为列

SELECT 
    p.`ID` AS 'Product ID', 
    p.`post_title` AS 'Product Name', 
    GROUP_CONCAT(IF(tt.`taxonomy` LIKE 'pa_clarity%', t.`name`, NULL)) AS 'Clarity',
    GROUP_CONCAT(IF(tt.`taxonomy` LIKE 'pa_shape%', t.`name`, NULL)) AS 'Shape' -- Add more attributes in same way
 FROM `wp_posts` AS p 
 INNER JOIN `wp_term_relationships` AS tr ON p.`ID` = tr.`object_id` 
 INNER JOIN `wp_term_taxonomy` AS tt ON tr.`term_taxonomy_id` = tt.`term_id` 
    AND (tt.`taxonomy` LIKE 'pa_clarity%' OR tt.`taxonomy` LIKE 'pa_shape%') -- If require add more attribute here and you are good
 INNER JOIN `wp_terms` AS t ON tr.`term_taxonomy_id` = t.`term_id` 
 WHERE p.`post_type` = 'product'
 GROUP BY p.`ID`;

Output:
+-------------------------------------------------+
| Product ID | Product Name | Clarity   | Shape   |
| p1         | p1_name      | Not clear | Circle  |
| p2         | p2_name      | clear     | Square  |
+-------------------------------------------------+

推荐阅读