php - 如何在sql查询中对数据进行排序(根据数据库字段的json格式中存在的值)

Question

Array
(
    [0] => Array
        (
            [20] => stdClass Object
                (
                    [qopid] => 20

                    [data] => {"room":0,"category":0}

                )

            [21] => stdClass Object
                (
                    [qopid] => 21

                    [data] => {"room":0,"category":0}

                )

            [22] => stdClass Object
                (
                    [qopid] => 22

                    [data] => {"room":0,"category":0}

                )

        )

    [1] => Array
        (
            [23] => stdClass Object
                (
                    [qopid] => 23

                    [data] => {"room":"3","category":0}

                )

            [24] => stdClass Object
                (

                    [data] => {"room":"3","category":0}

                )

            [25] => stdClass Object
                (

                    [data] => {"room":"3","category":0}

                )

        )

    [2] => Array
        (
            [26] => stdClass Object
                (
                    [qopid] => 26

                    [data] => {"room":"4","category":0}

                )

            [27] => stdClass Object
                (
                    [qopid] => 27

                    [data] => {"room":"4","category":0}

                )

            [28] => stdClass Object
                (
                    [qopid] => 28

                    [data] => {"room":"4","category":0}

                )

        )

)

在这里，我希望数据能够显示出来，因为房间为 0 的数据应该始终排在最后 ([data] => {"room":0,"category":0})

意味着我希望零号房间的数据始终放在最后。

下面是我的sql查询

 $sql = 'SELECT q.*   FROM ' . \SystemTables::DB_TBL_QUOTATION_PRODUCT . ' q  LEFT JOIN ' . \SystemTables::DB_TBL_ITEM . ' ii ON q.iid = ii.iid   WHERE q.qoid = "' . $qoid . '" ';

                if ($rooms == 0)
                {
                    $like = "'" . '%{"room":' . $rooms . ',%' . "'";
                }
                else
                {
                    $like = "'" . '%{"room":"' . $rooms . '",%' . "'";
                }
                if ($cat_id != null && $cat_id > 0)
                {
                    $like1 = "'" . '%[{"id":"' . $cat_id . '"}]%' . "'";
                    $sql .= ' AND c_catid LIKE ' . $like1 . '';
                }


                $sql .= ' AND q.data LIKE ' . $like . '';

上面是我的sql查询，怎么做呢？因为排序依据不是精确的列。我需要按数据中存在的房间进行排序。

Answer 1

实现此目的的一种方法是嵌套 foreach 循环

foreach ($array as $key => $value) {   // Loop thru each array
    foreach ($value as $object) {      // Loop thru each object
        $data = $object->data;         // Set data value to $data
        $decode = json_decode($data);  // Decode $data string
        if ($decode->room == 0) {      // Check if room is 0
            $move = $array[$key];      // Set array value to $move
            unset($array[$key]);       // Unset array first
            $array[$key] = $move;      // Then add array to last
        }
    }
}

print_r($array); // print array