MYSQL table rows and PHP array

what kind of PHP operation am i looking for when i am trying to do something like this:

I have a player table, and a location table. Each player is unique, each location is unique, therefore having an auto_increment(id and lid) field in each table to give each player and location a unique identifier. Each location can be associated with a player. I am inserting the auto_increment field from the players table(id), into the player ID field of the locations table(pid). My question is, when i have multiple locations associated with a single player, how do store the results of a query that is finding many rows in the locations database, into an array that i can then display.

here is my query which displays all players and their associated locations

re: MYSQL table rows and PHP array

SELECT DISTINCT location FROM table_name

re: MYSQL table rows and PHP array

well, unfortunatly, selecting a distinct location wont work here. We want to select a distinct PLAYER record, which will have SEVERAL distinct locations associated with it. how do i query the locations table, find each location associated with that player, and put those rows into an array which i can then display using HTML.

re: MYSQL table rows and PHP array

I don't think that is quite the answer that is expected. Since this is a PHP forum I assume that you want a PHP solution.

Since you (most of the times) do not know what the result columns will be, you want a solution that is independent of the number of result columns and dynamically builds the resulting output grid.

The following code is a reworked code snippet by janezr at jcn dot si from the PHP documentation. It takes the result and displays it in a grid at the screen, more or less like the MySQL command interface displays. You can easily adapt that if you want the results to be stored in an array.[php]$result=mysql_query("SELECT players.*, locations.* FROM players
LEFT JOIN locations ON players.id = locations.pid ")
or die ("invalid query: ".mysql_error());
if (mysql_num_rows($result) > 0) {
$numfields = mysql_num_fields($result);
echo "<table>\n<tr>";
// print the header i.e. column names
for ($i=0; $i < $numfields; $i++) {
echo '<th>'.mysql_field_name($result, $i).'</th>';
}
echo "</tr>\n";
// print the data, i.e. column values
while ($row = mysql_fetch_row($result)) {
echo '<tr><td>'.implode($row,'</td><td>')."</td></tr>\n";
}
echo "</table>\n";
}
else
echo 'No results!";[/php]Ronald :cool: