Codeigniter search with comma-separated values

Question

I was trying to achieve an advanced search using codeigniter framework. I have a search box that has few icons as a search based. Then if the user try to find 1 icon for example an icon of a parking lot it should display all the profiles that has that icon. And if the user click more icons for example parking lot, beer, good for couple...only the profiles that has those characteristics will be displayed..

Database info :

-----------------------------------------------------
place_id            icon_ids
-----------------------------------------------------
1                 1,2,3,4,5,6,7,8
2                 1,2,3,7,9
3                 1,3,4      
4                 1,2,4,5,9,10
5                 1,2,3,5,6,8,9,10
-----------------------------------------------------

MODEL

public function get_search_content($info_id, $search_queries, $query_type, $limit, $start, $action_type)
    {
        if($query_type === 'icons')
        {

            $count_id = explode(',', $info_id);
            $count_id = count($count_id);

            if($count_id > 1)
            {

                $search  = "lai.info_id IN (".$info_id.")";


                $query = $this->db->select('*, c.id AS cat_id, bh.id AS hour_id, bh.place_id AS bh_place_id, c.name AS cat_name, l.place_id AS place_id, lai.place_id AS lai_place_id')

                ->where($search)

                ->join('listing_additional_info lai', 'lai.place_id=l.place_id', 'LEFT')
                ->join('cities cy', 'cy.city_id=l.city_id', 'LEFT')
                ->join('cats c', 'c.parent_id=l.cat_id', 'LEFT')
                ->join('business_hours bh', 'bh.place_id=l.place_id', 'LEFT')
                ->group_by('lai.place_id', 'ASC')
                ->limit($limit, $start)
                ->get('listings l');
            }
            else
            {

                $search  = "FIND_IN_SET('".$info_id."', lai.info_id)";

                $query = $this->db->select('*, c.id AS cat_id, bh.id AS hour_id, bh.place_id AS bh_place_id, c.name AS cat_name, l.place_id AS place_id, lai.place_id AS lai_place_id')
                ->distinct()
                ->where($search)

                ->join('listing_additional_info lai', 'lai.place_id=l.place_id', 'LEFT')
                ->join('cities cy', 'cy.city_id=l.city_id', 'LEFT')
                ->join('cats c', 'c.parent_id=l.cat_id', 'LEFT')
                ->join('business_hours bh', 'bh.place_id=l.place_id', 'LEFT')
                ->group_by('lai.place_id', 'ASC')
                ->limit($limit, $start)
                ->get('listings l');
            }


        }
}

public function m_search_queries($limit, $start)
    {

        // $info_id = $this->input->get('info_id');
        $info_id = $this->input->get('info_id_');

        $search_queries = $this->input->get('q');

        $submit_search = $this->input->get('submit_search');

        if($submit_search == 'true')
        {
            if(!empty($info_id))
            {

                    echo $this->get_search_content($info_id, $search_queries, 'icons', $limit, $start, 'content');


            }
            elseif(!empty($search_queries))
            {
                echo $this->get_search_content($info_id, $search_queries, 'input', $limit, $start, 'content');

            }
            else
            {
                $rs = '';
                $rs .= '<div class="bg bg-info text-white p-3">Sorry, we could not find what you are looking for.</div>';

                echo $rs;
            }
        }

    }

VIEW

<form method="GET" action="<?= base_url().lang(); ?>/search" class="">
        <div class="mb-2" id="add-data-info">
           <?php for($i=1; $i<=13; $i++){ ?>
           <div class="btn add_selector search_add_selector search_add_selector_<?= $i; ?> search_add_info_view">
            <input type="checkbox" value="<?= $i; ?>" class="search_add_info">
            <img id="" class="svg" src="<?= base_url(); ?>theme/myown/img/icon/svg/<?= $i; ?>.svg?<?= time(); ?>" width="28">
           </div>
          <?php } ?>
          <div class="my-2">
          <div class="w-100 border border-muted my-4"></div>
           <input type="hidden" name="query_type" value="icons">
           <button type="submit" name="submit_search" class="btn btn-info btn-lg btn-rounded" value="true">Search</button>
          </div>
        </div>
        <input type="hidden" id="search_selected_additional" name="info_id_">
</form>

SCRIPT

<script>
var _add_info = $('.search_add_selector .search_add_info').on('change', function() {
    var add_info = $('.search_add_selector .search_add_info:checked');

    var _selected = add_info.map(function() {return this.value;}).get().join(',')

    var _checkIfChecked = $('.search_add_selector_'+this.value+' .search_add_info:checked').length > 0;

    if(_checkIfChecked)
    {

     $('.search_add_selector_'+this.value).addClass('selected_info');
    }
    else
    {

     $('.search_add_selector_'+this.value).removeClass('selected_info');
    }

                    $('#search_selected_additional').val(_selected);
                });
</script>

For example: The user has the value selected 1,2,5 then only the places that has at least 1,2,5 icon_ids will be displayed The result should be: 1 4 5

I am using the find_in_set() function it displays pretty well on 1 icon selected.

But for more than 2 icons it displays even if the profile has only 1 characteristic. What I'm trying to achieve is when the user is looking for a specific place that has parking lot, pet shop, beer only the places that has those characteristics will be displayed. Even if some profiles has more than 3 characteristics.

It should be displayed as long as the profile has those 3 characteristics at least.

I am looking forward for your kind help. Thank you so much in advanced everyone!

Answer 1

I've create this and tested it in my environment and it works like a charm:

    $this->db->select('whatever')->from('your_table');
    $this->db->group_start();
    foreach($selected_values as $value)
    {
        $this->db->where("find_in_set($value, icon_ids)");
    }
    $this->db->group_end();
    $result = $this->db->get()->result_array();

I tested it again with your values and it works just fine on (1,2,5) it shows (1,4,5)