• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Using subqueries

#1
[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

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

#3
[eluser]ThatDaveCarter[/eluser]
Hi Jeff,

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

Cheers,

Dave

#4
[eluser]CodeIgniteMe[/eluser]
how is this
Code:
SELECT * FROM `table` where `forename` like '%%' AND `surname` like '%%'
different from this?
Code:
SELECT * FROM `table`

#5
[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.

#6
[eluser]CodeIgniteMe[/eluser]
Ok Big Grin so I was taking that too literal XD

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

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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.