[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();
|