连接两个mysql表并在php中填充一个数组 - Joining two mysql tables and populate an array in php

Joining two mysql tables and populate an array in php

我有两个表

第一个表名是"Library"

ID Name
1  A
2  B

第二个表名是"Books"

BID Book_Creater Book_Name
1   Variation1   Book1
1   Variation2   Book2
1   Variation3   Book3
2   Variation1   Book4
2   Variation2   Book5

sql

$sql = mysql_query("select library.ID,books.Book_Creater,books.Book_name from library,books where library.ID = books.BID;

Result is

ID   Book_Creater   Book_name
1    Variation1     Book1
1    Variation2     Book2
1    Variation3     Book3
2    Variation1     Book4
2    Variation2     Book5

现在我想创建一个数组，它应该是这样的这就是我卡住的地方

array(
      [1] => array(
                   [Variation1] => Book1
                   [Variation2] => Book2
                   [Variation3] => Book3
                  )
      [2] => array(
                   [Variation1] => Book4
                   [Variation2] => Book5
                  )
)

$res = array();
//for each result row
while($row = mysql_fetch_array($sql)) 
{
    //add a row to the multi dimensional result array
    //where the first key = id and the second key = Book_Creater
    $res[$row['id']][$row['Book_Creater']] = $row['Book_name'];
}
print_r($res);

简单的方法是像当前那样进行连接，并在循环时将它们推入数组(可能为了简洁，当ID作为键不存在时显式创建新的子数组)。

像这样:-

<?php
$store_array = array();
$sql = mysql_query("SELECT library.ID,books.Book_Creater,books.Book_name 
                FROM library
                INNER JOIN books 
                ON library.ID = books.BID");
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) 
{
    if (!array_key_exists($row['ID'], $store_array))
    {
        $store_array[$row['ID']] = array();
    }
    $store_array[$row['ID']][$row['Book_Creater']] = $row['Book_name']
}
print_r($store_array);
?>

您还可以将一些工作转移到数据库中，并将它们连接在一起。然后将结果拆分放入数组中:-

<?php
$store_array = array();
$sql = mysql_query("SELECT library.ID, GROUP_CONCAT(CONCAT_WS('#~#', books.Book_Creater,books.Book_name)) AS details
                FROM library
                INNER JOIN books 
                ON library.ID = books.BID
                GROUP BY library.ID");
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) 
{
    $store_array[$row['ID']] = array();
    $details = explode(',', $row['details']);
    foreach($details as $detail)
    {
        $detail_line = explode('#~#', $row['detail']);
        $store_array[$row['ID']][$detail_line[0]] = $detail_line[1];
    }
}
print_r($store_array);
?>

请注意，如果您对实际数据执行此操作，您可能希望选择不会出现在数据中的分隔符，并且默认情况下GROUP_CONCAT具有相当有限的最大长度。

还请注意，我已经使用了mysql_*函数，因为这是你似乎正在使用的，但这些已被弃用，现在可能不应该使用