array variables in msql statement

Question

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>'; 
}

Shouldn't it be $child instead of $team in your foreach loop? — Mikey
– Mikey, Commented Sep 29, 2013 at 23:32
Unless it's a typo, echo $arra[0]; may need to be echo $array[0]; if you have a variable called $array, or echo array[0]; or echo $team[0]; --- either way, echo $arra[0]; doesn't look right. — Funk Forty Niner
– Funk Forty Niner, Commented Sep 29, 2013 at 23:37
You should not be using mysql_* functions in new code, as it is deprecated and will be removed in the future. — Cat
– Cat, Commented Sep 29, 2013 at 23:38

Osama Jetawe · Accepted Answer · 2013-09-29 23:32:58Z

3

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());

answered Sep 29, 2013 at 23:32

Osama Jetawe

2,7057 gold badges27 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user1032531 · Accepted Answer · 2013-09-29 23:39:25Z

2

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.

answered Sep 29, 2013 at 23:39

user1032531

26.5k77 gold badges248 silver badges422 bronze badges

2 Comments

user2055697 Over a year ago

When I echo "$team" I get an ID printed on the page. Why cant I just put "$team" in my sql statement like this : WHERE id = '$team'")

user1032531 Over a year ago

@user2055697. You can. Just make sure your SQL is what you think it is. Team sure be an integer data type, and if it isn't, you might need quotes around it (however, still change your database schema to make it an integer).

Community · Accepted Answer · 2017-05-23 11:58:22Z

1

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.

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Sep 29, 2013 at 23:49

Mikey

6,7564 gold badges25 silver badges49 bronze badges

Collectives™ on Stack Overflow

array variables in msql statement

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related