[El Forum]

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

[El Forum]

[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

[El Forum]

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

[El Forum]

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

[El Forum]

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

[El Forum]

[eluser]XMadMax[/eluser]
You're welcome.