sql结果到一个数组中没有重复的id和post\u标题

oyt4ldly 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(387)

我正在处理从数据库收到的数据。我想要数组中的数据，但是id和post\u title在结果中返回多个。

wp_posts
+----+------------+
| ID | post_title |
| 1  | radio      |
| 2  | television |
+----+------------+

wp_postmeta
+---------+---------+----------+------------+ 
| meta_id | post_id | meta_key | meta_value |
|    1    |    1    | price    | 12.50      |
|    2    |    1    | _sku     | 5876gh7    |
|    3    |    1    | thumb_id | 56         |
|    4    |    2    | price    | 342.50     |
|    5    |    2    | _sku     | tv784      |
|    6    |    2    | thumb_id | 78         |
+---------+---------+----------+------------+ 

$query = "
    SELECT p.ID, p.post_title, m.meta_id, m.meta_key, m.meta_value
    FROM wp_posts p
    LEFT JOIN wp_postmeta m ON (m.post_id = p.ID)
    WHERE EXISTS (SELECT 1 FROM wp_posts WHERE post_title LIKE '%rad%' AND ID = p.ID)
    OR EXISTS (SELECT 1 FROM wp_postmeta WHERE meta_key = '_sku' AND meta_value LIKE '%rad%' AND post_id = p.ID)
    ORDER BY p.ID, m.meta_id
";

$result     = mysqli_query($connection, $query);

while($row = $result->fetch_object() ){
        $sendback[] = $row;
}

print_r($sendback);

这是我打印出来的结果。您可以看到每个结果中都显示了id和post\u标题。

array(
    [0] => stdClass Object ( 
        [ID]            => 1 
        [post_title]    => Radio  
        [meta_key]      => price 
        [meta_value]    => 12.50 
    ) 
    [1] => stdClass Object ( 
        [ID]            => 1 
        [post_title]    => Radio  
        [meta_key]      => _sku 
        [meta_value]    => 5876gh7
    )
    [2] => stdClass Object ( 
        [ID]            => 1 
        [post_title]    => Radio  
        [meta_key]      => thumb_id 
        [meta_value]    => 56
    )
)

像这样一次，我可以用哪种方法获得id和post\u标题

array(
        'id'            => '1',
        'post_title'    => 'radio',
        'price'         => '12.50',
        'sku'           => '5876gh7',
        'thumb_id'      => '56'
    )

sql Join Arrays php Database

来源：https://stackoverflow.com/questions/62010312/sql-results-to-array-without-duplicated-id-and-post-title-in-one-array

2条答案

按热度按时间

mbyulnm01#

仅使用sql无法直接解决这个问题，您需要处理这些条目。原因是wp\u posttea对于单个wp\u posts条目有许多条目。所以你可以做的是：

$SQL = <<<SQL
  SELECT *
  FROM wp_posts
SQL;

$posts = $dbh->query($SQL)->fetchAll();

你将拥有你所有的职位

foreach($posts as $post) {
  $prodArr[]['id'] = $post['id'];
  $prodArr[count($prodArr) - 1]['post_title'] = $post['post_title'];
  $SQL = <<<SQL
    SELECT *
    FROM wp_postmeta
    WHERE post_id = {$post['id']}
SQL;
  $metas = $dbh->query($SQL)->fetchAll();
  foreach($metas as $meta) {
    if ($meta['meta_key'] == 'price')
      $prodArr[count($prodArr) - 1]['price'] = $meta['meta_key'];
    else if ($meta['meta_key'] == '_sku')
      $prodArr[count($prodArr) - 1]['sku'] = $meta['meta_key'];
    else if ($meta['meta_key'] == 'thumb_id')
      $prodArr[count($prodArr) - 1]]['thumb_id'] = $meta['meta_key'];
  }
}

所以$prodarr将拥有所有产品所需的所有信息，然后您可以对其进行迭代。代码可能包含错误，我还没有测试过。

赞(0）回复(0）举报 2021-07-26

um6iljoc2#

如果您事先知道要返回的元键列表，则可以使用条件聚合来透视数据集并生成预期的结果：

SELECT 
    p.ID, 
    p.post_title, 
    MAX(CASE WHEN m.meta_key = 'price'    THEN m.meta_value END) price,
    MAX(CASE WHEN m.meta_key = '_sku'     THEN m.meta_value END) sku,
    MAX(CASE WHEN m.meta_key = 'thumb_id' THEN m.meta_value END) thumb_id
FROM wp_posts p
LEFT JOIN wp_postmeta m ON m.post_id = p.ID
WHERE 
    EXISTS (SELECT 1 FROM wp_posts WHERE post_title LIKE '%rad%' AND ID = p.ID)
    OR EXISTS (SELECT 1 FROM wp_postmeta WHERE meta_key = '_sku' AND meta_value LIKE '%rad%' AND post_id = p.ID)
GROUP BY p.ID, p.post_title
ORDER BY p.ID

我还怀疑 WHERE 条款可以简化：

SELECT 
    p.ID, 
    p.post_title, 
    MAX(CASE WHEN m.meta_key = 'price'    THEN m.meta_value END) price,
    MAX(CASE WHEN m.meta_key = '_sku'     THEN m.meta_value END) sku,
    MAX(CASE WHEN m.meta_key = 'thumb_id' THEN m.meta_value END) thumb_id
FROM wp_posts p
LEFT JOIN wp_postmeta m ON m.post_id = p.ID
GROUP BY p.ID, p.post_title
HAVING 
    p. post_title LIKE '%rad%'
    OR MAX(CASE WHEN m.meta_key = '_sku' AND m.meta_value LIKE '%rad%' THEN 1 ELSE 0 END) = 1
ORDER BY p.ID

赞(0）回复(0）举报 2021-07-26

我来回答

sql结果到一个数组中没有重复的id和post\u标题

2条答案

相关问题

热门标签

最新问答