How can I perform a MYSQL search query that returns two different sets of results at the same time? |
[eluser]Dandy_andy[/eluser]
I’m performing a MYSQL query that selects certain information from a table and returns 12 results (using LIMIT). However, I have now run into a situation where I need to split those 12 results into two different queries of 6 results each. So essentially what I’d like to do is get the results of query 1 (6 of them) and also the results of query 2 (6 of them) and combine them so that the return value is 12 in total (6 + 6). I’ve just been reading about transactions – is this the right way to do this or is there another method? I have illustrated what I’m trying to do below (very simplified – obviously the statements below don’t work) Code: $this->db->select('*'); The intention is to ensure the results always return 6 male members and 6 female members regardless of the search criteria (there are far more options but not listed above). Any help is appreciated. Thanks.
[eluser]XMadMax[/eluser]
Dandy_andy, Best method would be making a UNION between both selects. Example: http://heybigname.com/2009/09/18/using-c...ubqueries/ Also, you can make a direct query: SELECT * FROM members WHERE sex='female' LIMIT 6 UNION SELECT * FROM members WHERE sex='male' LIMIT 6
[eluser]Dandy_andy[/eluser]
Thats excellent thanks. Union does the job fine! However, what if I wanted to complicate the search further and rather than return 6+6, have the 12 at random so that the array could return any combination of 6 males and 6 females all shuffled?
[eluser]XMadMax[/eluser]
(SELECT * FROM members WHERE sex=‘female’ LIMIT 6) UNION (SELECT * FROM members WHERE sex=‘male’ LIMIT 6) UNION (SELECT * FROM members WHERE sex=‘other’ LIMIT 6) # ORDER BY rand()
[eluser]Dandy_andy[/eluser]
The problem with your method is that the array that returns the results will always have 6 males grouped together and 6 females. I have used 'shuffle' on the query return to shuffle all the results and that works fine!. Thanks for your help.
|
Welcome Guest, Not a member yet? Register Sign In |