By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,828 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,828 IT Pros & Developers. It's quick & easy.

MYSQL table rows and PHP array

P: 7
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
Expand|Select|Wrap|Line Numbers
  1. SELECT players.*, locations.* FROM players LEFT JOIN locations ON players.id = locations.pid
Jan 18 '07 #1
Share this Question
Share on Google+
3 Replies


P: 12
SELECT DISTINCT location FROM table_name
Jan 18 '07 #2

P: 7
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.
Jan 18 '07 #3

ronverdonk
Expert 2.5K+
P: 4,258
SELECT DISTINCT location FROM table_name
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:
Jan 18 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.