MySQL query trouble

[eluser]jbads[/eluser]
The problem I have with the following query is that it is still returning results from all the regions, not just the region speicfied in $region. I can't for the life me figure out how to return results only where the user_region column has data that matches $region.

Could anybody offer any advice on this?

Code:

function _search_process($users, $members, $region, $keyword){
    
    //Get data for all users based on search item and region
    if($region    !==    'All')
    {    
        $i    =    $this->db->select($members.'.user_id,'.$members.'.user_avatar,'.$members.'.user_display_name,'.$members.'.user_location,'.$members.'.user_region,'.$members.'.user_skills,'.$users.'.username,'.$users.'.banned_id')
                    ->from($members)
                    ->join($users, $users.'.user_id = '.$members.'.user_id', 'left')
                    ->like('user_firstname', $keyword)
                    ->or_like('user_lastname', $keyword)
                    ->or_like('user_display_name', $keyword)
                    ->or_like('user_location', $keyword)
                    ->or_like('user_skills', $keyword)
                    ->or_like('user_interests', $keyword)
                    ->or_like('user_work', $keyword)
                    ->or_like('user_about', $keyword)
                    ->where('user_region', $region)
                    ->having(array($users.'.banned_id =' => '0'))
                    ->get();
                    
        return $var    =    ($i->num_rows > 0) ? $i->result_array() : false;
    }

Regards, Jake

[eluser]Crafter[/eluser]
Firstly your syntax !== is wrong, You might want to check on that.

The effect you see probably points to a non functional where clause. I'll probably look in there.

[eluser]Hannes Nevalainen[/eluser]
Turn on profiling to see how the query string looks:

Code:

$this->output->profiler(true);

If you can't figure out what's wrong then postback the query to this thread =)

[eluser]Sumon[/eluser]
try with :

Code:

$i    =    $this->db->select($members.'.user_id,'.$members.'.user_avatar,'.$members.'.user_display_name,'.$members.'.user_location,'.$members.'.user_region,'.$members.'.user_skills,'.$users.'.username,'.$users.'.banned_id')
                    ->from($members)
                    ->join($users, $users.'.user_id = '.$members.'.user_id', 'left')
                    ->where('user_region', $region)
                    ->like('user_firstname', $keyword)
                    ->or_like('user_lastname', $keyword)
                    ->or_like('user_display_name', $keyword)
                    ->or_like('user_location', $keyword)
                    ->or_like('user_skills', $keyword)
                    ->or_like('user_interests', $keyword)
                    ->or_like('user_work', $keyword)
                    ->or_like('user_about', $keyword)
                    ->having(array($users.'.banned_id =' => '0'))
                    ->get();

let us know still unexpected regions ?

[eluser]jbads[/eluser]

Code:

SELECT jcc_user.user_id, jcc_user.user_avatar, jcc_user.user_display_name, jcc_user.user_location, jcc_user.user_region, jcc_user.user_skills, jcc_users.username, jcc_users.banned_id FROM (`jcc_user`) LEFT JOIN `jcc_users` ON jcc_users.id = jcc_user.user_id WHERE `user_region` = 'Ruapehu' AND `user_firstname` LIKE '%jet%' OR `user_lastname` LIKE '%jet%' OR `user_display_name` LIKE '%jet%' OR `user_location` LIKE '%jet%' OR `user_skills` LIKE '%jet%' OR `user_interests` LIKE '%jet%' OR `user_work` LIKE '%jet%' OR `user_about` LIKE '%jet%' HAVING jcc_users.banned_id = 0

Is the query, tried removing the if statement altogether, still same results unfortunatley.

[eluser]Sumon[/eluser]
if you post jcc_user and jcc_users table structure along with some data then it will be more easier for us to give a try. by the way does it make change in result if the sql is:

Code:

SELECT jcc_user.user_id, jcc_user.user_avatar, jcc_user.user_display_name, jcc_user.user_location, jcc_user.user_region, jcc_user.user_skills, jcc_users.username, jcc_users.banned_id
FROM (`jcc_user`)
LEFT JOIN `jcc_users` ON jcc_users.id = jcc_user.user_id
WHERE `user_region` = 'Ruapehu' AND (`user_firstname` LIKE '%jet%' OR `user_lastname` LIKE '%jet%' OR `user_display_name` LIKE '%jet%' OR `user_location` LIKE '%jet%' OR `user_skills` LIKE '%jet%' OR `user_interests` LIKE '%jet%' OR `user_work` LIKE '%jet%' OR `user_about` LIKE '%jet%') HAVING jcc_users.banned_id = 0

[eluser]jbads[/eluser]
Thanks,

Adding the brackets fixed my problem when I ran the code in phpMyAdmin. However, How do I add those brackets into my query, I'm using method chaining.

[eluser]Sumon[/eluser]
how is going on with

Code:

$query = $this->db
          ->select("jcc_user.user_id, jcc_user.user_avatar, jcc_user.user_display_name, jcc_user.user_location, jcc_user.user_region, jcc_user.user_skills, jcc_users.username, jcc_users.banned_id")
          ->from('jcc_user')
          ->join('jcc_users', 'jcc_users.id = jcc_user.user_id', 'left')
          ->where('jcc_user.user_region', 'Ruapehu')
          ->orwhere('jcc_user.user_firstname LIKE', '%jet%')
          ->orwhere('jcc_user.user_lastname LIKE', '%jet%')
          ->orwhere('jcc_user.user_display_name LIKE', '%jet%')
          ->orwhere('jcc_user.user_location LIKE', '%jet%')
          ->orwhere('jcc_user.user_skills LIKE', '%jet%')
          ->orwhere('jcc_user.user_interests LIKE', '%jet%')
          ->orwhere('jcc_user.user_work LIKE', '%jet%')
          ->orwhere('jcc_user.user_about LIKE', '%jet%')
          ->having('jcc_users.banned_id = 0')
         ->get();