Database query with where / or where

[eluser]skunkbad[/eluser]
I'm making my own authentication system with multiple user levels. Users can not access accounts of users on the same level, but do have access to their own. A "site manager" is user level 6, and the "Sudo" is level 9. Standard user is level 1. I'm working on the query to search in accounts, but it isn't working:

Code:

$this->db->like('user_name', 'whatever');
$this->db->where('user_level <', $handoff['user_level']);
$this->db->or_where('user_name', $handoff['user_name']);
$rows = $this->db->get($this->user_table);

When the two inside lines of code try to modify the query, the query result is no different than if they weren't there at all. Any suggestions?

[eluser]Thorpe Obazee[/eluser]
Try using the profiler and see what query was built. I doubt that the sql statement you want was the one built by the query builder.

[eluser]skunkbad[/eluser]
I get:

SELECT *
FROM (`users`)
WHERE `user_level` < '6'
OR `user_name` = 'testuser'
AND `user_email` LIKE '%yahoo%'

which is I think what I want... a search through users that are less than level 6, including the logged in user (in this case 'testuser'), with an email that includes "yahoo"

but it returns all users.


Actually, with some testing in phpMyAdmin, I figured that I actually want this query:

SELECT *
FROM (
`users`
)
WHERE (
`user_level` < '6'
OR `user_name` = 'sitemanager'
)
AND `user_email` LIKE '%yahoo%'
LIMIT 0 , 30

but I don't know how to make one using the active record class

[eluser]Thorpe Obazee[/eluser]
[quote author="skunkbad" date="1245139874"]I get:

SELECT *
FROM (`users`)
WHERE `user_level` < '6'
OR `user_name` = 'testuser'
AND `user_email` LIKE '%yahoo%'

which is I think what I want... a search through users that are less than level 6, including the logged in user (in this case 'testuser'), with an email that includes "yahoo"

but it returns all users.[/quote]

You like to get all users with user_level < 6 and the guy with the user_name, 'tesuser' AND someone who might have a yahoo email?

[eluser]skunkbad[/eluser]
yes, user level 6 is the first level considered "admin"

How can I modify my query with the active record class to get this:

SELECT *
FROM (
`users`
)
WHERE (
`user_level` < '6'
OR `user_name` = 'testuser'
)
AND `user_email` LIKE '%yahoo%'
LIMIT 0 , 30

because that is what I want.

[eluser]Thorpe Obazee[/eluser]
Try the last part of $this->db->where(); in the AR page in the user_guide, where it says something like this

Code:

$this->db->where('MATCH (field) AGAINST ("value")', NULL, FALSE);

Use that format.

[eluser]skunkbad[/eluser]
I think I've got it working now with this:

Code:

$this->db->like('user_email', 'yahoo');
$custom_query = "(user_level < '" . $handoff['user_level'] . "' OR user_name = '" . $handoff['user_name'] . "')";
$this->db->where($custom_query);
$rows = $this->db->get($this->user_table);

Thanks for your help

[eluser]Thorpe Obazee[/eluser]
Glad you have it working.