mysql fetch array not iterating through the records

[BinaryBird]

Hi, i have a small script to display records from two tables. Table 1 keeps track of teams and table 2 keeps track of the members.

<?php
$dl = mysql_connect ("localhost","nitin_nick","test1234");
$db = mysql_select_db("nitin_registration",$dl);

echo "<table border='0' width='986' align='center' cellspacing='1' cellpadding='5'>";
echo "<tr>";

echo "<th>Team Name</th>";
echo "<th>Team Contact</th>";
echo "<th>Member Name</th>";
echo "<th>Member Email</th>";
echo "<th>Mobile Phone</th>";
echo "<th>College</th>";
echo "</tr>";
$result1="";
$result2="";
$query1 = "SELECT * FROM team ";
$q1_set = mysql_query($query1,$dl);

while ($result1 = mysql_fetch_array($q1_set)) {
   $team_id = $result1['id'];
   echo "<tr>";
   echo "<td>".$result1['name']."</td>";
   echo "<td>".$result1['contact']."</td>";
   echo "<td>".""."</td>";
   echo "<td>".""."</td>";
   echo "<td>".""."</td>";
   echo "<td>".""."</td>";
   echo "</tr>";
   $query2 = "SELECT * FROM member WHERE team_id={$team_id} LIMIT 3";

   $q2_set = mysql_query($query2,$dl) ;

   $result2 = mysql_fetch_array($q2_set);
     $count=0;

   while ($result2 && $count<3)  {   //The index of $result2 is not incrementing
        echo "<tr>";
        echo "<td>".""."</td>";
        echo "<td>".""."</td>";
        echo "<td>".$result2['name']."</td>";
        echo "<td>".$result2['email']."</td>";
        echo "<td>".$result2['phone']."</td>";
        echo "<td>".$result2['college']."</td>";
        echo "</tr>";
        $count++ ;

   } 
}


echo "</table>";

?>

The second query where i am trying to pull members with the team id, the result of mysql_fetch_array() is not incrementing the index of the array. When i run the script its goes into a infinite loop,( the second while loop) It just has the first record . Have i done something wrong here? Kindly help me out. Thanks.

[Philip]

$result2 = mysql_fetch_array($q2_set) needs to be in the while loop statement

while($result2 = mysql_fetch_array($q2_set) && $count < 3) { 
...
}

[BinaryBird]

$result2 = mysql_fetch_array($q2_set) needs to be in the while loop statement

while($result2 = mysql_fetch_array($q2_set) && $count < 3) { 
...
}

 

Tried this. But the second query is returning null values. I tried the same query in php myadmin and it returns 3 rows. Is there something i am missing?

[Philip]

In this case, you might want to use a join, but if you continue this way - make sure your first query is pulling the correct info (user ID)

[fenway]

And if you only want 3, use LIMIT.

[premiso]

Well why are you doing a $count incrementor? That is what LIMIT does. It will only return 3 records, no matter what.

 

To make sure your query is valid add this:

 

   $query2 = "SELECT * FROM member WHERE team_id={$team_id} LIMIT 3"; // note that will limit the query to 3
    $q2_set = mysql_query($query2,$dl) or trigger_error("Query Failed: " . mysql_error());

 

And to modify Philip's loop to remove the $count:

   while($result2 = mysql_fetch_array($q2_set)) { 

 

I would completely remove that $count and $count < 3 as it is redundant and not necessary.

[BinaryBird]

In this case, you might want to use a join, but if you continue this way - make sure your first query is pulling the correct info (user ID)

 

I am relatively new to php and mysql. I will have to spend some time to read about Joins.

The first query is definately pulling the right results(user ID) but the second query, for some reason is pulling nulls. I tried the queries in phpmyadmin and i get the desired rows.

 

And i removed the count variable and used LIMIT instead.

[BinaryBird]

Not sure why my earlier logic failed. But joins did solve my problem. Thank you all.

 

Here is the final code that worked as intended:

<?php
$dl = mysql_connect ("localhost","nitin_nick","test1234");
$db = mysql_select_db("nitin_registration",$dl);
echo "<table border='0' width='986' align='center' cellspacing='1' cellpadding='5'>";
echo "<tr>";
echo "<th>Team Name</th>";
echo "<th>Team Contact</th>";
echo "<th>Member Name</th>";
echo "<th>Member Email</th>";
echo "<th>Mobile Phone</th>";
echo "<th>College</th>";
echo "</tr>";
$result1="";
$result2="";
$query1 = "SELECT * FROM team ";
$q1_set = mysql_query($query1,$dl);
while ($result1 = mysql_fetch_array($q1_set)) {
   $team_id = $result1['id'];
   echo "<tr>";
   echo "<td>".$result1['team_name']."</td>";
   echo "<td>".$result1['contact']."</td>";
   echo "<td>".""."</td>";
   echo "<td>".""."</td>";
   echo "<td>".""."</td>";
   echo "<td>".""."</td>";
   echo "</tr>";
   $query2 = "SELECT * FROM team,member WHERE (team.id=member.team_id) AND (team.id={$team_id})";
   $q2_set = mysql_query($query2,$dl);
   while ($result2 = mysql_fetch_array($q2_set)){
        echo "<tr>";
        echo "<td>".""."</td>";
        echo "<td>".""."</td>";
        echo "<td>".$result2['member_name']."</td>";
        echo "<td>".$result2['email']."</td>";
        echo "<td>".$result2['phone']."</td>";
        echo "<td>".$result2['college']."</td>";
        echo "</tr>";
   }
}
echo "</table>";
?>

[fenway]

Not to burst your bubble, but you could save a DB query by simply grabbing the teams from the 2nd query.