从mysql查询构建多维php数组

yvfmudvl 于 2021-06-20 发布在 Mysql

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

我正在尝试从mysql查询创建多维数组。这个阵列有3层深。每个用户都有一个 membershipid ，此成员身份id可以有多个 characterid 的。每一个 characterid 的可以有多个属性。
membershipid->characterid（最多可以是3个）->浅色，racehash。

$query = "SELECT p.membershipid, p.displayname, c.characterid, c.light, c.racehash
    FROM Players as p
    LEFT JOIN Characters as c
    ON c.membershipid = p.membershipid";
    $result = $mysqli->query($query) or die("SQL Error: Members konden niet worden opgehaald.");

    // Loop through the requested data, add to an array
    while ($data = mysqli_fetch_assoc($result)) {
        $categorylist[ $data['membershipid'] ] = array(
                    'displayname' => $data['displayname'],
              array('characterid' => $data['characterid'],
                    'light' => $data['light'],
                          'racehash' => $data['racehash']
            )
                    );

    } // end while statement

// A test to see how many items in array          
echo "<pre>\n";
print_r($categorylist);    
echo "</pre>\n";

这是可行的，但在查询结果显示每个人时，每个人只显示一个字符 character id 对于每个 membership id .

Array
(
    [4611686018428931875] => Array
        (
            [displayname] => White_Anomaly
            [0] => Array
                (
                    [characterid] => 2305843009264668680
                    [light] => 554
                    [racehash] => 3887404748
                )

        )

    [4611686018437972738] => Array
        (
            [displayname] => Bpunisher7
            [0] => Array
                (
                    [characterid] => 2305843009277456739
                    [light] => 392
                    [racehash] => 2803282938
                )

        )

sql查询的结果如下：

我可能做了一些错误的数组定义和覆盖我的characterid的每一次，因为我只得到最新的 characterid 显示。
首选阵列：

Array
(
    [4611686018428931875] => Array
        (
            [displayname] => White_Anomaly
            [characters] => Array
                (
                    [0] => Array
                        (
                            [characterid] => 2305843009264668678
                            [light] => 370
                            [racehash] => 3887404748
                        )
                    [1] => Array
                        (
                            [characterid] => 2305843009264668680
                            [light] => 554
                            [racehash] => 3887404748
                        )
                )
        )

    [4611686018437972738] => Array
        (
            [displayname] => Bpunisher7
            [characters] => Array
                (
                    [0] => Array
                        (
                            [characterid] => 2305843009265241161
                            [light] => 534
                            [racehash] => 2803282938
                        )
                    [1] => Array
                        (
                            [characterid] => 2305843009265241163
                            [light] => 524
                            [racehash] => 3887404748
                        )
                    [2] => Array
                        (
                            [characterid] => 2305843009277456739
                            [light] => 392
                            [racehash] => 3887404748
                        )
                )
        )
)

mysql Arrays php multidimensional-array

来源：https://stackoverflow.com/questions/52624893/build-a-multidimensional-php-array-from-mysql-query

2条答案

按热度按时间

whhtz7ly1#

如果您允许我稍微重新构造您的结果数组，这应该是一个更好的方法：

// Loop through the requested data, add to an array
while ($data = mysqli_fetch_assoc($result)) {
    extract($data,EXTR_PREFIX_ALL,'data');
    $categorylist[$data_membershipid]['displayname']     = $data_displayname;
    $categorylist[$data_membershipid][$data_characterid] = ['light'    => $data_light,
                                                            'racehash' => $data_racehash];

} // end while statement

如您所见，我将characterid移到了子数组的键。这样就不会覆盖其他字符。
我还对数组使用了较新的表示法。这个 extract() 只是为了让代码更容易阅读，但是你可以去掉它。
或许最好将字符放入自己的数组中，如下所示：

// Loop through the requested data, add to an array
while ($data = mysqli_fetch_assoc($result)) {
    extract($data,EXTR_PREFIX_ALL,'data');
    $categorylist[$data_membershipid]['displayname'] = $data_displayname;
    $categorylist[$data_membershipid]['characters'][$data_characterid] = ['light'    => $data_light,
                                                                          'racehash' => $data_racehash];

} // end while statement

赞(0）回复(0）举报 2021-06-21

hlswsv352#

如果存在$categorylist[$data['membershipid']]，它将被下一个字符覆盖。
您必须检查$categorylist[$data['membershipid']]是否存在。在本例中，使用array\u push（）添加第二个字符。否则代码仍然有效。

赞(0）回复(0）举报 2021-06-20