![]() |
Using subqueries - Printable Version +- 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: Using subqueries (/showthread.php?tid=52569) |
Using subqueries - El Forum - 06-16-2012 [eluser]ThatDaveCarter[/eluser] Hi, I'm currently executing a query using $this->db->query($sql,$data), which has worked fine for me so far, however the query now needs some adjustments, and needs to contain a subquery. I'm having trouble figuring out a way to do this, and was wondering what the best way to do this in CI would be. My current MySQL query reads similar to this: Code: SELECT * FROM `table` where `forename` like '%%' AND `surname` like '%%' I need to update it to this: Code: SELECT * FROM `table` where (`forename` like '%%' AND `surname` like '%%') OR userid in ((select userid from `genres` where `genre` like '%genre1%' or `genre` like '%genre2%')) The Genres part of this query will be dynamic, depending on input from a HTML form (an array of items is posted) I've tried numerous iterations that I can think of, to no avail. My head is fried right now.. Any tips/guidance please? Regards, Dave Using subqueries - El Forum - 06-16-2012 [eluser]jmadsen[/eluser] Dave, the expression you are trying to create is like this: Code: SELECT * FROM users WHERE email LIKE '%jeff%' OR email LIKE '%madsen%' Create this using REGEXP: Code: SELECT * FROM users WHERE email REGEXP 'jeff|madsen' NOTE! the lack of whitespace inside the quotes - if you have it there, it will be searched on literally (i.e., ' jeff | madsen ' will only look for jeff or madsen, never jeffmadsen) Using subqueries - El Forum - 06-20-2012 [eluser]ThatDaveCarter[/eluser] Hi Jeff, Thanks a lot! I didn't even know REGEXP existed and could be used like that. Cheers, Dave Using subqueries - El Forum - 06-21-2012 [eluser]CodeIgniteMe[/eluser] how is this Code: SELECT * FROM `table` where `forename` like '%%' AND `surname` like '%%' Code: SELECT * FROM `table` Using subqueries - El Forum - 06-21-2012 [eluser]ThatDaveCarter[/eluser] [quote author="CodeIgniteMe" date="1340266087"]how is this Code: SELECT * FROM `table` where `forename` like '%%' AND `surname` like '%%' Code: SELECT * FROM `table` It's not. I simplified the query somewhat to get it to the point, the actual query contains values. Using subqueries - El Forum - 06-21-2012 [eluser]CodeIgniteMe[/eluser] Ok ![]() Using subqueries - El Forum - 06-21-2012 [eluser]ThatDaveCarter[/eluser] [quote author="CodeIgniteMe" date="1340269345"]Ok ![]() Yeah, just a little haha ![]() |