mySQL - 在单个选择中使用xref表从相关表中获取数据

问题描述：

I have two tables, A and B which are xref'd together in AB_xref. At the moment to get data from table B I'm dumping all rows rows from A into an array and then looping over each index finding the multiple B rows that relate to that A row. I'm then stitching the rows from B into the array of rows from A - creating a nested array.

Is there any way to do this using a single select and conditions?

我有两个表，A和B，它们在AB_xref中一起外部参照。目前从表B获取数据我将所有行从A转储到数组中，然后循环遍历每个索引，找到与该A行相关的多个B行。然后我将B中的行拼接到A的行数组中 - 创建一个嵌套数组。 p>

有没有办法用一个select和条件来做到这一点？ p > div>

答

See if this gets you somewhere close to where you want to be:

Query:

SELECT
   packages.name AS package_name,
   activities.name AS activity_name
FROM
   package_activity_xref pax
INNER JOIN packages ON packages.id = pax.package_id
INNER JOIN activities ON activities.id = pax.activity_id

PHP:

$results = get_assoc_array_from_sql($your_query);
$outputArray = array();

foreach($results as $result) {
   if (array_key_exists($result['package_name'], $outputArray)) {
      // we already have an array at this package, push the new item
      array_push($outputArray[$result['package_name']], $result['activity_name']);
   } else {
      // the target array doesn't exist yet, make it
      $outputArray[$result['package_name']] = array($result['activity_name']);
   }
}

Basically, this code will loop through the entire xref table once (O(n) where n is the number of records in xref). For each record, it will look at the package name and see if the key exists in $outputArray yet. If it does not, make the value at that key an array with the current activity name. If it does exist, append the current activity to that array.

If you do a print_r($outputArray) after execution, you should see an array with x items where x is the number of distinct packages. Each element in the array will be another array with y elements where y is the number of activities for the given package.

Note: you will need to implement get_assoc_array_from_sql yourself but I assume you either have some implementation you are using already or you are using mysqli_fetch_assoc() or something similar.

mySQL - 在单个选择中使用xref表从相关表中获取数据

相关推荐