+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Database query with where / or where (/showthread.php?tid=19685)
Database query with where / or where - El Forum - 06-15-2009
[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?
Database query with where / or where - El Forum - 06-15-2009
[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.
Database query with where / or where - El Forum - 06-15-2009
[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
Database query with where / or where - El Forum - 06-15-2009
[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?
Database query with where / or where - El Forum - 06-15-2009
[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.
Database query with where / or where - El Forum - 06-15-2009
[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.
Database query with where / or where - El Forum - 06-15-2009
[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
Database query with where / or where - El Forum - 06-15-2009
[eluser]Thorpe Obazee[/eluser]
Glad you have it working.