[SOLVED] Trying to create a simple search facility for a project

[eluser]edjon2000[/eluser]
Hello again

I am trying to put together a simple search facility for a project and seem to be getting some strange results.

I am taking the input from a form view and passing it through my search_admin controller to my search_model.

I seem to be having a problem with the returned results, sometimes I get the correct result or no results if none are returned which is fine, but sometimes I get all results returned which is confusing me totally, I think my logic may be to blame here.

Here is my view file

Code:

<div class="vacancy-search-form">
                &lt;?php
                //$options_v = array('Please Select ...' => 'Please Select ...');
                echo form_open('site_search', 'class="vacancy-search-form"');
                echo form_fieldset();

                // vacancy select menu
                echo form_label('Vacancy', 'vacancy');
                echo form_dropdown('vacancy', $vac_name, set_value('vacancy', 'vacancy'), 'id="vacancy" title="Please select a vacancy"');
                echo form_error('vacancy');

                // sector select menu
                echo form_label('Sector', 'sector');
                //$options_s = array('Please Select ...' => 'Please Select ...');
                echo form_dropdown('sector', $sec_name, set_value('sector', 'sector'), 'id="sector" title="Please select sector of interest"');
                echo form_error('sector');

                // location select menu
                $data = array();
                echo form_label('Location', 'location');
                $data = array(
                        'name' => 'location',
                        'id' => 'location',
                        'title' => 'Please type a location ...',
                        'value' => set_value('location', 'Please type a location')
                );
                echo form_input($data);
                echo form_error('location');

                // form buttons
                echo form_submit('submit', 'Submit Search', 'title="Search !"');
                echo form_reset('reset', 'Reset Form', 'title="Clear form"');

                echo form_fieldset_close();
                echo form_close();
                ?&gt;
            </div>&lt;!-- end of div .vacancy-search-form --&gt;

This is my controller method

Code:

function form_search()
    {
        unset ($_POST['submit']);

        $this->load->model('search_model');
        $found = $this->search_model->search_form($_POST);

        print '<pre>';
        print_r($found);
        print '</pre>';

        exit('<br />script stopped');
    }

And the model method

Code:

function search_form($options = array())
    {
        if (!$this->_required(array
                                    (
                                        'vacancy',
                                        'sector',
                                        'location'
                                    ), $options)
            )
            return false;

        $vacancy = $options['vacancy'];
        if ($vacancy == 'All Vacancies')
        {
            $vacancy = '%';
        }
        $sector = $options['sector'];
        if ($sector == 'All Sectors')
        {
            $sector = '%';
        }
        $location = $options['location'];

        $this->db->select('id, vacancy_name, vacancy_sector, vacancy_area, vacancy_region');
        $this->db->where('vacancy_name', $vacancy);
        $this->db->where('vacancy_sector', $sector);
        $this->db->like('vacancy_area', $location, 'after');
        $this->db->or_like('vacancy_region', $location, 'after');
        $returns = $this->db->get('live_jobs');

        echo $this->db->last_query() . "<br />";
        #exit('script stopped');
        $query = array();
        $ans = array();
        foreach ($returns->result_array() as $result)
        {
            $query['id'] = $result['id'];
            $query['name'] = $result['vacancy_name'];
            $query['sector'] = $result['vacancy_sector'];
            $query['area'] = $result['vacancy_area'];
            $query['region'] = $result['vacancy_region'];
            $ans[] = $query;
        #}

//        print '<pre>';
//        print_r($query);
//        print '</pre>';
        }
        #exit('script stopped');

        return $ans;
    }

Any help with this would be greatly appreciated
Jon

[eluser]Nicolas Santos[/eluser]
hello,

This is due to your or_like in the end. you should use ->where() with a where string and brackets to correctly define your where clause..

Code:

$where = "name='Joe' AND (status='boss' OR status='active')";
$this->db->where($where);

[eluser]edjon2000[/eluser]
Hi Nicolas

Thanks for the response I knew it was a fault with my logic, you were quite right, well spotted, I actually ended up rewriting my search model method based on what you said, so it is now like this:

Code:

function search_form($options = array())
    {
        if (!$this->_required(array
                                    (
                                        'vacancy',
                                        'sector',
                                        'location'
                                    ), $options)
            )
            return false;

        $vacancy = $options['vacancy'];
        if ($vacancy == 'All Vacancies')
        {
            $vacancy = '';
        }
        $sector = $options['sector'];
        if ($sector == 'All Sectors')
        {
            $sector = '';
        }
        $location = $options['location'];
        if ($location == 'Please type a location')
        {
            $location = '';
        }

        $this->db->select('id, vacancy_name, vacancy_sector, vacancy_area, vacancy_region');
        $where = "vacancy_name LIKE '%$vacancy%'    AND    vacancy_sector LIKE '%$sector%' AND    (vacancy_area LIKE '%$location%' OR vacancy_region LIKE '%$location%')";
        $this->db->where($where);
        $returns = $this->db->get('live_jobs');
//        echo $this->db->last_query() . "<br />";
        $query = array();
        $ans = array();
        if ($returns->num_rows > 0)
        {
            foreach ($returns->result_array() as $result)
            {
                $query['id'] = $result['id'];
                $query['name'] = $result['vacancy_name'];
                $query['sector'] = $result['vacancy_sector'];
                $query['area'] = $result['vacancy_area'];
                $query['region'] = $result['vacancy_region'];
                $ans[] = $query;
            }
        }
        return $ans;
    }

It now seems to work correctly, thanks again for your help

Jon

[eluser]edjon2000[/eluser]
After looking at it again I was able to cut the search_model method down further to this:

Code:

function search_form($options = array())
    {
        $vacancy = $options['vacancy'];
        if ($vacancy == 'All Vacancies')
        {
            $vacancy = '';
        }
        $sector = $options['sector'];
        if ($sector == 'All Sectors')
        {
            $sector = '';
        }
        $location = $options['location'];
        if ($location == 'All Locations')
        {
            $location = '';
        }

        $this->db->select('id_pref, id, vacancy_name, vacancy_sector, vacancy_area, vacancy_region, ts');
        $where = "vacancy_name LIKE '%$vacancy%' AND
                            vacancy_sector LIKE '%$sector%' AND
                            (vacancy_area LIKE '%$location%' OR
                            vacancy_region LIKE '%$location%')";
        $this->db->where($where);
        $query = $this->db->get('live_jobs');

        return $query->result();
    }

Thanks again for your help

Jon