3 sqli queries: easier way?

[Jakebert]

Is there a less complicated/neater way to do this:

	
<?php
// the first query selects the group_ids that the user belongs to from the cross reference table
$groups = $connection->query("SELECT group_id FROM group_membership WHERE user_id = '$user'");
while ($info = mysqli_fetch_assoc($groups)) 
{
	// 2nd query: What ballots are members of those groups eligible for? (from the groups/ballots cross reference table)
	$ballots = $connection->query("SELECT ballot_id FROM group_ballots WHERE group_id = " . $info['group_id']);
	while ($finfo = mysqli_fetch_object($ballots)) {

// 3rd query: get the info of the ballots from query 2
		$search = $connection->query("SELECT * FROM ballots WHERE id = " . $finfo->ballot_id);
		$output = mysqli_fetch_assoc($search);
		echo $output['display_name'];
		echo "<br />";
		echo $output['open_date'];
		}		
	}?>

 

I'd also like to be able to echo out a "nothing found" message if any of those comes up with 0 results. Maybe my table design could be neater?

[darkfreaks]

Multi_query

[Jakebert]

Sorry to be so dense but since the second and third queries are based on the results of their predecessors, how would multiquery work?

[darkfreaks]

nevermind i read that it is not very safe to use it for security purposes, but you can use  Inner join to combine the select queries into one.

 

http://www.w3schools.com/sql/sql_join_inner.asp

[Adam]

Running a query for each row of a result set is bad practise and really inefficient, never mind running a subsequent query for each row from each of those queries as well. In your case let's say the user belongs to 10 groups. So 10 rows are returned in the top-level query, which means the second-level query is ran 10 times. Let's then say that each group has on average 5 ballots available, which means the third-level query is ran around 5 times for every group. That means, if my math is right, you're running about 78 queries in total!

 

Instead, as mentioned, you could use a join that will do everything in a single query:

 

$sql = "
    SELECT b.*
    FROM group_membership gm
    JOIN group_ballots gb ON (gm.group_id = gb.group_id)
    JOIN ballots b on (gb.ballot_id = b.id)
    WHERE gm.user_id = '{$user}'
";

 

I used b.* because I don't know the names of the columns, but I would add them manually instead of using *.