Formatting data from database

plazz2000

I have data of a soccer match in a mySQL database, stored like this:

goal_id | scorer  | minute
--------|---------|--------
1       | player1 | 17
2       | player2 | 58
3       | player3 | 82

I want to display the scorers on the page like this:

player1 17, 82, player2 58

Can anyone suggest how I could do this?

siwis

SELECT scorer, minute FROM goals ORDER BY scorer

then, I suppose the crudest method would be to use something like the following, embedded in your while {}

while ($score = mysql_fetch_array($score_result)) {
$scorer = $score['scorer'];
$minute = $score['minute'];
if ($current_scorer != $scorer) {
echo "$scorer $minute,";
$current_scorer = $scorer;
} else {
echo " $minute,";
$current_scorer = $scorer;
};
};

plazz2000

Thanks a million.

I made a few small changes to remove the comma after the final minute, and it works a charm.

Thanks.

plazz2000

Just one small thing, as it is, the list of scorers will be in alphabetical order: Barry 39, John 14, 73

How would I get it display like this: John 14, 73, Barry 39 (in order of goal_id, but naming each player just once)

I tried changing the SQL query to ORDER BY goal_id but this breaks the code.

Jeepsta

Wouldnt you have to select the field goal_id in order to do and ORDER BY on it?

So your query would be "SELECT * FROM goals ORDER BY goal_id"

****This is assuming that these are the only fields in your table.

Also, I dont quite understand your method for outputting the data. Your example is John 14, 73, Barry 39. From what I can understand its like saying John scored in the 14th minute and Barry in the 39th minute.....but how does that 73 fit into this? Am I just missing something simple here?

Zach

zalath

No, you don't need to SELECT a field to ORDER BY it - you jsut need to make sure the table it's in is included in the FROM part of the query.

What I fail to see is how that could possibly 'break the code'.

siwis

You can ORDER BY any field in the database without actually having to SELECT it.

Your example is John 14, 73, Barry 39.

I spotted the error in the first post. John (player1) scroes the 1st and third goals at 14 and 73 minutes respectively, whereas Barry only scores the second goal at minute 39.

What the poster wants to do is to ORDER by the first goal scored by each player, thus the player scoring the first goal appears first, followed by (if different) the player who scores the second goal ad finitum. However, if those players score additional goals, the poster simply wants to list the minute point WITH the first instance of the player.

I've slept on it now, and from what I can see, the only way you can do it, is with 2 sql's (someone more experienced with deeper sql's may well be able to teach me something here ;-) )

So a pseudo would take the form:

SELECT player FROM table ORDER BY goal_id GROUP BY player.

This should return an array with the players who scored goals with them in the order of their first goal.

LOOP through using a WHILE

In the WHILE add a second sql:

SELECT minute FROM table WHERE player = '$player' ORDER BY minute

Now loop through the minute markers.

close loop
close loop.

$player_result = mysql_query("SELECT player FROM table ORDER BY goal_id GROUP BY player", $db);
while ($scored = mysql_fetch_array($player_result)) {
	$player_name = $scored['player'];
	$minute_result = mysql_query("SELECT minute FROM table WHERE player = '$player_name' ORDER BY minute", $db);

echo $player_name;
while ($minutes = mysql_fetch_array($minute_result)) {
	$minute = $minutes['minute'];
	echo $minute . ", ";
};
};