Fun with Joins/Headaches

[ifm1989]

To dumb this down as much as possible, I have the following tables:

 

wars, wars_participants, users, alliance_members, alliance

 

I want to be able to search active wars.

 

So the query would have to collect data in the following fashion:

 

War #1

- - - - wars_participants (on war_id) -> alliance_members (on participant_user_id) -> alliance (on alliance_id) -> users (on participant_user_id)

- - - - wars_participants (on war_id) -> alliance_members (on participant_user_id) -> alliance (on alliance_id) -> users (on participant_user_id)

 

War #2

- - - - wars_participants (on war_id) -> alliance_members (on participant_user_id) -> alliance (on alliance_id) -> users (on participant_user_id)

- - - - wars_participants (on war_id) -> alliance_members (on participant_user_id) -> alliance (on alliance_id) -> users (on participant_user_id)

 

etc.etc.

 

 

I'm able to get this far, but it only collects data for ONE participant. I need it to collect for all of them, so it can be searchable by alliance name and user name.

$sql = "SELECT * FROM ".$prefix."wars
	LEFT JOIN `".$prefix."wars_participants` ON ".$prefix."wars.war_id=".$prefix."wars_participants.war_id 
	LEFT JOIN `".$prefix."alliance_members` ON ".$prefix."alliance_members.alliance_user_id=".$prefix."wars_participants.participant_user_id 
	LEFT JOIN `".$prefix."alliance` ON ".$prefix."alliance.alliance_id=".$prefix."alliance_members.alliance_id 
	LEFT JOIN `".$prefix."users` ON ".$prefix."users.user_id=".$prefix."wars_participants.participant_user_id 
WHERE ".$prefix."alliance.name LIKE '%$search%' OR ".$prefix."users.user_name LIKE '%$search%' GROUP BY ".$prefix."users.user_id
ORDER BY ".$SORT_CONFIG[$sort]." ".$DIR_CONFIG[$dir]." LIMIT $start,$results";

 

Is there some way I can have "sub querries" or something like that, so the results from one user is in an array inside the larger war array?

[mwasif]

It would be better if you post only the SQL not PHP code.

[fenway]

Why would it only find one?