CODEWITHSUNDEEP
mysqlcodeigniter
Omicans
Omicans

Reputation: 561

Need proper Query for database

I need a little help in writing a proper query. Here is the scenario...

I am implementing some auto suggestions to an input field. I had this query for simple suggestions.

$temp = $this->db
    ->select('state as label, state as value')
    ->where('last_name !="" AND state LIKE ', $term.'%' )
    ->get('user_profile')
    ->result_array();

This query works perfectly for me. But the problem is that this query brings up the results from only one column which is the state column. Now what I need is that I want to fetch results which match up any of the three columns there. the query I am writing now is

$temp = $this->db
    ->select('zip as label, zip as value')
    ->where(
        'last_name !="" 
        AND zip LIKE ', $term.'% 
        OR suburb LIKE ', $term.'% 
        OR city LIKE ', $term.'%' )
    ->get('user_profile')
    ->result_array();

This query is not being executed at all. There must be a syntax error somewhere which I am not able to identify...I want that the results that match up the criteria i show them in the following form.

zip, suburb, city

Previous query just brings up the results from one column and I display it as it is. Now i need to bring up results from 3 columns and need to show them as they are.

Any help would be highly appreciated.

Thanks.

Upvotes: 0

Views: 35

Answers (2)

ABorty
ABorty

Reputation: 2522

try this

$where = "last_name !=' ' AND (zip LIKE '%{$term}%' OR suburb LIKE '%{$term}%' OR city LIKE '%{$term}%')";
$temp = $this->db->select('zip as label, zip as value')->where($where)->get('user_profile')->result_array();

i think will help you.the query should be like this. Now you can change it accordingly.Please let me know if you face any problem.

Upvotes: 1

Mark
Mark

Reputation: 1386

Replace:

where('last_name !="" AND zip LIKE ', $term.'% OR suburb LIKE ', $term.'% OR city LIKE ', $term.'%' )

with:

where('last_name !="" AND zip LIKE '. $term.'% OR suburb LIKE '. $term.'% OR city LIKE '. $term.'%' )

That would be a good start. Also, enable error reporting to get a better idea of your problem (to do this in CI, open up your project's index.php file and set:

define('ENVIRONMENT', 'development')

Upvotes: 0

Related Questions