SQL的CONCAT()函数实例:无限级分类排序

SQL语句:
SELECT * FROM `shop_catalog` order by CONCAT(`path`, `id`)

Select Code
1
2
3
4
5
6
7
8
9
10
/*
  遍历分类
  */
  $sql = "SELECT * FROM `shop_catalog` order by CONCAT(`path`, `id`)";
  $res = mysqli_query($conn, $sql);
  while ($row=mysqli_fetch_assoc($res)) {
    $list_cata[] = $row;
    $list_catapid[] = $row['pid'];
  }
  mysqli_free_result($res);

查询结果如图:
concat_01

分类列表下拉效果:

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<table border="0" width="80%">
<tr>
  <td width="100" align="right">所属分类</td>
  <td><select name="cata_id" class="form-control" style="width:200px;">
  <option value="0">---选择所属的分类---</option>
  <?php 
    foreach ($list_cata as $key => $val) {
      $path = $val['path'];
      $num = substr_count($path, ',');
      $str_blank = str_repeat('+    ', $num-1);
      $value_id = " value='".$val['id']."'";
      $disabled = NULL;
      if (in_array($val['id'], array_unique($list_catapid))) {
        $value_id = null;
        $disabled = " disabled class='disabled'";
      }
      echo "<option{$value_id}{$disabled}>{$str_blank}{$val['name']}</option>";
    }
  ?>
  </select></td>
</tr>

concat_02