Using subqueries

[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

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

[eluser]ThatDaveCarter[/eluser]
Hi Jeff,

Thanks a lot! I didn't even know REGEXP existed and could be used like that.

Cheers,

Dave

[eluser]CodeIgniteMe[/eluser]
how is this

Code:

SELECT * FROM `table` where `forename` like '%%' AND `surname` like '%%'

different from this?

Code:

SELECT * FROM `table`

[eluser]ThatDaveCarter[/eluser]
[quote author="CodeIgniteMe" date="1340266087"]how is this

Code:

SELECT * FROM `table` where `forename` like '%%' AND `surname` like '%%'

different from this?

Code:

SELECT * FROM `table`

[/quote]

It's not. I simplified the query somewhat to get it to the point, the actual query contains values.

[eluser]CodeIgniteMe[/eluser]
Ok so I was taking that too literal XD

[eluser]ThatDaveCarter[/eluser]
[quote author="CodeIgniteMe" date="1340269345"]Ok so I was taking that too literal XD[/quote]

Yeah, just a little haha -- I should of specified that actually, my bad lol