+- 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: How can I perform a MYSQL search query that returns two different sets of results at the same time? (/showthread.php?tid=54635)
How can I perform a MYSQL search query that returns two different sets of results at the same time? - El Forum - 09-18-2012
[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('*');
$this->db->from('members');
$this->db->where('sex', 'female');
$this->db->limit(6);
$this->db->select('*');
$this->db->from('members');
$this->db->where('sex', 'male');
$this->db->limit(6);
$query = $this->db->get();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.
How can I perform a MYSQL search query that returns two different sets of results at the same time? - El Forum - 09-18-2012
[eluser]XMadMax[/eluser]
Dandy_andy,
Best method would be making a UNION between both selects.
Example: http://heybigname.com/2009/09/18/using-code-igniters-active-record-class-to-create-subqueries/
Also, you can make a direct query:
SELECT * FROM members WHERE sex='female' LIMIT 6
UNION
SELECT * FROM members WHERE sex='male' LIMIT 6
How can I perform a MYSQL search query that returns two different sets of results at the same time? - El Forum - 09-18-2012
[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?
How can I perform a MYSQL search query that returns two different sets of results at the same time? - El Forum - 09-18-2012
[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()
How can I perform a MYSQL search query that returns two different sets of results at the same time? - El Forum - 09-18-2012
[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.
How can I perform a MYSQL search query that returns two different sets of results at the same time? - El Forum - 09-18-2012
[eluser]XMadMax[/eluser]
You're welcome.