尝试从mysql-query，php创建多维数组

Question

尝试将两个查询组合到数组中。看看我的代码，首先，我想得到所有的水果都有猫A和val好。第二，我会得到fruit匹配第一个查询的标记，cat c和val要么好要么坏。最后，我想让它们像数组一样$arg=（"苹果"=>"4"，"香蕉"=>"2"，"耳朵"=>"0"）；因为梨和val不匹配好或坏，所以0。你有办法做这件事吗？欣赏

<?php
/*
fruit  cat     val    mark
apple   A      good    V
apple   c       OK     4
banana  A      good     W
banana  c      bad     2
pear    A      good     W
pear    c      good     8
*/
$val1 = 'OK';
$val2 = 'bad';
$result1 = $wpdb->get_results( $wpdb->prepare("
	SELECT DISTINCT fruit FROM my_table WHERE cat ='A' AND val='%s' ",$val1));
$result2 = $wpdb->get_results( $wpdb->prepare("
	SELECT mark FROM my_table WHERE cat ='c' AND val='%s' AND fruit IN (SELECT DISTINCT fruit FROM my_table WHERE cat ='A' AND val='%s')",$val2,$val1));
/*how can I combine these two queries into an array like 
$arg = ('apple'=>'4','banana'=>'2','pear'=>'0');
because pear did not match, so 0
*/
?>

Answer 1

最简单的解决方案是重写查询，而不是在php中手动执行，我认为这将达到您想要的效果：

SELECT
  t.fruit,
  CASE
    WHEN t.a_mark IS NOT NULL THEN t.a_mark
    WHEN t.c_mark IS NOT NULL THEN t.c_mark
    ELSE '0'
  END mark
FROM
  (SELECT
    t.fruit,
    ( SELECT MAX(mark)
      FROM my_table
      WHERE fruit = t.fruit AND val = 'good' AND cat = 'A'
        AND mark NOT IN ('V', 'W')) a_mark,
    ( SELECT MAX(mark)
      FROM my_table
      WHERE fruit = t.fruit AND val in ('OK', 'bad') AND cat = 'c'
        AND mark NOT IN ('V', 'W')) c_mark  
  FROM
    (SELECT DISTINCT fruit FROM my_table) t
  ) t
ORDER BY
  t.fruit