数组代码点火器从数据库中提取结果

Question

我有一个食物表，在那里我存储分类ID作为coma分开，如下面的图像食物表

我在模型中做了一个方法，它以数组（类别ID）为参数，从参数中获取与数组ID匹配的食物项。

我做了以下查询

if(count($categoryIds) > 0 && count($allergyIds) == 0 ){
    $tempArr = array();
    foreach($categoryIds as $eachCategoryId){
        $sql = "Select food.food_id,food_name,food_image,food_thumbnail,description,food_variations.price as price,is_active
        from food
        join food_variations on food_variations.food_id = food.food_id
        where FIND_IN_SET($eachCategoryId,category_id)
        and food.restaurant_id = $restaurantId
        and food.is_active = 1
        and food.is_deleted = 0
        and food.food_status = 3
        and food_variations.food_variation_id = food.default_food_variation_id";
        $result = $this->db->query($sql)->result_array();
        array_push($tempArr, $result);
    }
    echo "<pre>";print_r($tempArr);
}

下面是上面查询的结果

Array
(
    [0] => Array
        (
            [0] => Array
                (
                    [food_id] => 10
                    [food_name] => Rama Mckee
                    [food_image] => 
                    [food_thumbnail] => 
                    [description] => asdfs
                    [price] => 34
                    [is_active] => 1
                )

            [1] => Array
                (
                    [food_id] => 6
                    [food_name] => Rishi
                    [food_image] => 
                    [food_thumbnail] => 
                    [description] => test
                    [price] => 120
                    [is_active] => 1
                )

            [2] => Array
                (
                    [food_id] => 5
                    [food_name] => test
                    [food_image] => http://localhost/gastroapp/assets/uploads/food_images/a5918726b920e7cbfc7f90e1afc48091.jpg
                    [food_thumbnail] => http://localhost/gastroapp/assets/uploads/food_images/thumb/a5918726b920e7cbfc7f90e1afc48091.jpg
                    [description] => test
                    [price] => 120
                    [is_active] => 1
                )

        )

    [1] => Array
        (
            [0] => Array
                (
                    [food_id] => 10
                    [food_name] => Rama Mckee
                    [food_image] => 
                    [food_thumbnail] => 
                    [description] => asdfs
                    [price] => 34
                    [is_active] => 1
                )

            [1] => Array
                (
                    [food_id] => 7
                    [food_name] => ezhva
                    [food_image] => 
                    [food_thumbnail] => 
                    [description] => ddsfsd
                    [price] => 20
                    [is_active] => 1
                )

            [2] => Array
                (
                    [food_id] => 8
                    [food_name] => test
                    [food_image] => 
                    [food_thumbnail] => 
                    [description] => test
                    [price] => 22
                    [is_active] => 1
                )

            [3] => Array
                (
                    [food_id] => 6
                    [food_name] => Rishi
                    [food_image] => 
                    [food_thumbnail] => 
                    [description] => test
                    [price] => 120
                    [is_active] => 1
                )

        )

)

我得到了重复的结果，我认为这也可能导致性能问题。

以下是当我只有一个类别的食物给我期望的结果时的查询。

return $this->db->select('food.food_id,food_name,food_image,food_thumbnail,description,food_variations.price as price,is_active')
            ->from('food')
            ->join('food_variations', 'food_variations.food_id = food.food_id')
            ->where_in('category_id',$categoryIds)
            ->where('food.restaurant_id', $restaurantId)
            ->where('food.is_active', '1')
            ->where('food.is_deleted', '0')
            ->where('food.food_status','3')
            ->where('food_variations.food_variation_id IN( select food_variation_id from food_variations where food_variation_id = food.default_food_variation_id )')
            ->get()
            ->result_array();

请帮帮忙。

Answer 1

首先，您需要在数组中插入列表

$list = {ids column}
$list = array_map("intval", explode(",", str_replace(',' , '', $list)));

现在您的列表保存在一个数组$list中

现在你可以称之为

foreach($list as $value) {
$sql = "SELECT * FROM menu WHERE user_id = '$user_id' AND id = '$value' AND deleted = '0' AND active = '1';";
$result = mysqli_query($dBconnection, $sql);
$check = mysqli_num_rows($result);
if ($check>0) {

while($row = mysqli_fetch_assoc($result)) {
cho $row['id'];
}

} else {
echo 'empty list';
}
}