CODEWITHSUNDEEP
phparrays
user2055697
user2055697

Reputation: 57

array variables in msql statement

I have sql statement that adds results into the variable But when I put that variable($team) in my second sql statement it doesn't work WHERE id = '$team'. How would I go about this?

$query = mysql_query("SELECT team_name FROM team");
$team = array();
while($row = mysql_fetch_assoc($query)){
$team[] = $row;}
echo $arra[0];
$loop=count($team);
for($x=0;$x<$loop;$x++)
foreach($team[$x] as $child) {



$result = mysql_query("SELECT * FROM members 
WHERE id =  '$team'") 
or die(mysql_error());  
while($row = mysql_fetch_array( $result )) {
echo '<td>' . $row['first_name'] . '</td>'; 
}

Upvotes: 0

Views: 82

Answers (3)

Mikey
Mikey

Reputation: 6766

I think your question is unclear and your code is a little bit messy. 

$query = mysql_query("SELECT team_name FROM team");
// loop through each team
while($team = mysql_fetch_assoc($query)){
    // find members for this team
    $query = mysql_query("SELECT * FROM members WHERE id = '" . $team['team_name'] . "'") or die(mysql_error());
    while ($member = mysql_fetch_array($query)) {
        echo '<td>' . $member['first_name'] . '</td>'; 
    }
}

Though, the best way is to use joins like user1032531 has shown instead of making several calls to the database.

Upvotes: 1

user1032531
user1032531

Reputation: 26281

I agree with jetawe's use of join, but I recommend a different type of join.

SELECT m.first_name
FROM team AS t
LEFT OUTER JOIN members AS m ON m.id=t.team_name;

PS. Be sure to use a surrogate key for table "team" and not use the name.

Upvotes: 2

Osama Jetawe
Osama Jetawe

Reputation: 2705

use join php function if the $team is an array like:

$team = join(',', $team);
$result = mysql_query("SELECT * FROM members WHERE id in ($team)") or die(mysql_error());

Upvotes: 3

Related Questions