doob0526 2019-04-29 06:09
浏览 108
已采纳

如何使用php和mysql在多维数组中返回多级类别

i am trying to get multidimensional array from categories saved in database with recursion but i am unable to properly format the output i need.

i have tried following code which is working fine if i want to output ul-li multi-level list but not working when i am trying to convert it into array

function get_categories($parent_id=0){
    global $link;

    $menu=[];

    $sql="SELECT * from terms where parent='$parent_id'";

    $result=mysqli_query($link,$sql);


    while ($row=mysqli_fetch_assoc($result)) {

      $sub_cats=get_categories($row['id']);

      $menu[$row['name']]=$sub_cats;
    }

    return $menu;
  }

and db schema is in attached file db schema looks like

expected output is

array(
  [0]=>array(
    ['name']=>language
    ['sub_cats']=>array(
      [0]=>array(
        [name]=>english
        ['sub_cats']=>array(
          [0]=>array(
            [name]=>john milton
            ['sub_cats']=>0
          )
          [1]=>array(
            [name]=>william blake
            ['sub_cats']=>0
          )
        )
      )
      [1]=>array(
        ['name']=>spanish
        ['sub_cats']=>0
      )
      [2]=>array(
        ['name']=>sanskrit
        ['sub_cats']=>0
      )
    )
  )
  [1]=>array(
    ['name']=>xyz
    ['sub_cats']=>0
  )
)

current output is

Array
(
    [Language] => Array
        (
            [english] => Array
                (
                    [abcd] => Array
                        (
                        )

                )

            [hindi] => Array
                (
                )

            [sanskrit] => Array
                (
                )

        )

    [science] => Array
        (
        )

)

or any other format which can be easily transported via JSON and interpreted via JavaScript on other side.

and i know i am using multiple queries which can slow down the process, so if possible tell me the correct way.

also i am using mysqli for now to grasp the concept and i will shift my code in prepared statements later.

  • 写回答

1条回答 默认 最新

  • doujiu8918 2019-04-29 08:01
    关注

    Try this,

    $mysqli = new mysqli("localhost", "user", "password", "database");
    if ($mysqli->connect_errno) {
        echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
    }
    
    $menu = [];
    
    
    function get_categories($parent_id = 0, &$menu)
    {
        global $mysqli;
    
        if ($stmt = $mysqli->prepare("SELECT * from terms where parent=?")) {
    
            $stmt->bind_param("i", $parent_id);
    
            $stmt->execute();
    
            $result = $stmt->get_result();
    
            while ($row = $result->fetch_assoc()) {
    
                $reference = &$menu[];
                $reference['name'] = $row['name'];
                $sub_cats = get_categories($row['id'], $reference);
                $reference['sub_cats'] = $sub_cats ? $sub_cats : 0;
    
            }
    
            $stmt->close();
        }
    }
    
    get_categories(0,$menu);
    echo '<pre>';
    print_r($menu);
    echo '<pre>';
    

    I have used mysqli prepared statements, just to avoid sql injection and I recommend you to use prepared statements as well.

    Output,

    Array
    (
        [0] => Array
            (
                [name] => language
                [0] => Array
                    (
                        [name] => english
                        [0] => Array
                            (
                                [name] => john milton
                                [sub_cats] => 0
                            )
    
                        [1] => Array
                            (
                                [name] => william blake
                                [sub_cats] => 0
                            )
    
                        [sub_cats] => 0
                    )
    
                [1] => Array
                    (
                        [name] => spanish
                        [sub_cats] => 0
                    )
    
                [2] => Array
                    (
                        [name] => sanskrit
                        [sub_cats] => 0
                    )
    
                [sub_cats] => 0
            )
    
        [1] => Array
            (
                [name] => xyz
                [sub_cats] => 0
            )
    
    )
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题