CODEWITHSUNDEEP
phparraysjson
Subby
Subby

Reputation: 5480

PHP add array to an object

I am trying to query the database to:

  1. Get a football match record
  2. Get all the players that are part of that match

There are three tables: Matches | Players | Match_Players

Match_Players simply connects the two other tables together.

I am trying to return the result in JSON.

The problem is with the second query. It returns an Errant Query. This is what I have so far: 

$matches = array();
if(mysql_num_rows($matchResult)) 
{
    while($match = mysql_fetch_assoc($matchResult)) 
    {
        $players_query = 
            "SELECT p.* FROM match_players mp 
            LEFT JOIN players p on p.id = mp.player_id
            WHERE mp.id = ".$match->player_id; // <--- This is the error

        $playersResult = mysql_query($players_query,$link) or die('Errant query: '.$players_query);
        $players       = array();

        if(mysql_num_rows($playersResult)) 
        {
            while($player = mysql_fetch_assoc($playersResult)) 
            {
                $players[] = $player;
            }
        }

        $match->$players = $players;    
        $matches[] = $match;
    }
}

Update

I have changed the second SQL statement by replacing $match->player_id to $match['player_id'];. However, I am not receiving the collection of players. All I can see is the match details. Any idea why?

Upvotes: 1

Views: 61

Answers (2)

furier
furier

Reputation: 1977

Shouldn't the query be like this?

"SELECT p.* 
FROM match_players mp 
RIGHT JOIN players p on p.id = mp.player_id
WHERE mp.match_id = ".$match->id;

or like this

"SELECT p.*
FROM players p, match_players mp, matches m
WHERE p.id = mp.player_id AND mp.match_id = m.id
AND m.id = ". $match->id;

created an sqlfiddle to try and check the queries and they are fine...

Upvotes: 1

Steve
Steve

Reputation: 20459

Use 

$match['player_id']

(array syntax) not 

$match->player_id

(object syntax) or use 

mysql_fetch_object

Though i expect you should do one query, not a loop

Upvotes: 2

Related Questions