Include within a while loop

Question

I'm sure my inability to solve this problem steams from a lack of knowledge of some aspect of php but I've been trying to solve it for a month now with no luck. Here is a simplified version of the problem.

In my database I have a members table, a childrens table (the children of each member), and a friend requests table (this contains the friend requests children send to each other).

What I'm attempting to do is display the children of a particular parent using the following while loop....

$query = "SELECT * From children " . <br>
         "WHERE parent_member_id = $member_id";     <br>  
$result = mysql_query($query) <br>
      or die(mysql_error());<br>
$num_children = mysql_num_rows($result);<br>
echo $num_children;<br>
while($row = mysql_fetch_array($result)){<br>
   $first_name = $row['first_name'];<br>
   $child_id = $row['child_id'];<br>

<div>echo $first_name<br>

}

This while loop works perfectly and displays something like this...

1) Kenneth
2) Larry

What I'm attempting to do though is also display the number of friend requests each child has next to their name...like this

Kenneth (2)
Larry (5)

To do this I attempted the following modification to my original while loop...

$query = "SELECT * From children " .<br>
         "WHERE parent_member_id = $member_id";<br>



$result = mysql_query($query) <br>
      or die(mysql_error());<br>
$num_movies = mysql_num_rows($result);<br>
echo $num_movies;<br>
while($row = mysql_fetch_array($result)){<br>

   $first_name = $row['first_name'];<br>
   $child_id = $row['child_id'];<br>

echo $first_name; include('counting_friend_requests.php') ;

}

In this version the included script looks like this...

$query = "SELECT <br>children.age,children.child_id,children.functioning_level,children.gender,children.parent_member_id,children.photo, children.first_name,friend_requests.request_id " .
         "FROM children, friend_requests " .
         "WHERE children.child_id = friend_requests.friend_two " .
         "AND friend_requests.friend_one = $child_id"; <br>

$result = mysql_query($query)<br>
  or die(mysql_error());<br>
$count = mysql_num_rows($result);<br>
if ($count==0)<br>
  {<br>
  $color = "";<br>
  }<br>
else<br>
  {<br>
  $color = "red";<br>
  }<br>
echo span style='color:$color' ;<br>
echo $count;<br>
echo /span;<br>

Again this while loop begins to work but the included file causes the loop to stop after the first record is returned and produces the following output...

Kenneth (2)

So my question is, is there a way to display my desired results without interrupting the while loop? I'd appreciate it if anyone could even point me in the right direction!!

Answer 1

Avoid performing sub queries in code like the plague, because it will drag your database engine down as the number of records increase; think <members> + 1 queries.

You can create the query like so to directly get the result you need (untested):

SELECT child_id, first_name, COUNT(friend_two) AS nr_of_requests
From children
LEFT JOIN friend_requests ON friend_one = child_id OR friend_two = child_id
WHERE parent_member_id = $member_id
GROUP BY child_id, first_name;

It joins the children table records with friend_requests based on either friend column; it then groups based on the child_id to make the count() work.