xtrad

Sorry about that! I'll try to clarify... The image i attached to my second post in this thread shows the output of the query from my first post. It's basically a list of all machines, in alphabetical order, showing the high-score on each machine, who scored it and when it was scored. That list is great when you wonder who has the high-score on a particular machine. But, if a player wonders how many machines he has the high-score on, he has to look down that entire list (about 130 rows) and count how many times he sees his name. So, i wanted to create another, separate list which has all the players' names with a number next to them representing the number of machines they have the high-score on. Preferably with the player with most high-scores at the top. Or perhaps instead of just a number next to each player, i could actually list all the machines each player has the high-score on. Anyway, it's writing that query i am stuck with. As the query in my first post on this thread already finds all the high-scores i tried to adapt it to count the number of high-scores each player has but have not succeeded.

A list showing the player's name and the number of high-scores they have already exists but it's just in html and is updated manually. I was simply trying to recreate that list automatically using an SQL query. But now you mention it, i think it would be a lot better to show the player's name and all the machines they have high-scores on, instead of just a number for how many high-scores they have.

OK, this is an example of what the above query outputs: [/img] Basically it's an alphabetical list of the machines showing the high-score, the player who scored it, and the meet it was scored at. The full list includes around 130 machines. So, you can find out how many high-scores you have by counting how many times your name appears on the list. But i wanted to make it easier by writing another query that counts how many high-scores each player has and produces a simple list like this (ordered by the biggest number of high-scores): Player, Number of high-scores player_a, 10 player_b, 9 player_c, 5 etc I have tried adapting the query i used to get the highscores, but without success. [attachment deleted by admin]

Hi, I have a table, 'LeagueResults1', with the following five columns: resultID, machine, player, score, meet I am trying to write a query to produce a list of how many high-scores each player has. I simply want to show their name and the number of high-scores they have. I currently produce a list showing the high-score for every machine with the corresponding player and meet next to it: $query = "SELECT f.machine, f.player, f.score, f.meet FROM (SELECT machine, MAX(score) AS maxscore FROM LeagueResults1 GROUP BY machine) AS X INNER JOIN LeagueResults1 AS f ON f.machine = x.machine AND f.score = x.maxscore GROUP BY machine"; I tried to adapt this query to count the number of high-scores each player has, but have had no success. Any suggestions would be much appreciated.

Thanks for the reply. I've got it working perfectly now.

Thanks for the quick reply. It seems like a simple solution but when i insert $counter = 0; before the html as below... $counter++; $bgcolor = ($counter % 2)?"#e8edff":"#ffffff"; $counter = 0; echo "<tr class='border'>\n <td class='firstcolumn' bgcolor='".$bgcolor."'>$n</td>\n <td bgcolor='".$bgcolor."'>$player</td>\n <td class='scores' bgcolor='".$bgcolor."'>$score</td>\n </tr>\n"; ... this results in all of the table's rows being blue. I also tried inserting $counter = 0; before $counter++; but this also results in all table rows being blue. So it seems that the counter is being reset for every row, but i only want it reset at the beginning of each table. Again, any suggestions will be appreciated. Thanks.

Hi, I'm using a table to list results from a MySQL query. I wanted to make the rows of the table alternate colours and found the following code on this website which does the job perfectly: $counter++; $bgcolor = ($counter % 2)?"#e8edff":"#ffffff"; echo "<tr class='border'>\n <td bgcolor='".$bgcolor."'>$n</td>\n <td bgcolor='".$bgcolor."'>$player</td>\n <td bgcolor='".$bgcolor."'>$score</td>\n </tr>\n"; On my webpage i have six tables listing results from six MySQL queries and i use the above code after each query to construct the table and alternately colour the rows blue then white. My problem arises when there are an odd number of rows in the first table. This results in the first row of the second table being coloured white, not blue. I would like the first row of each table to be blue. Is there a simple way to reset the counter for each table? Any advice much appreciated. Thanks.

I solved this problem using PHP in the end. It was easier to do it that way. Thanks for your replies/suggestions though - they made me think a lot more about what i was trying to do.

Thanks again for taking the time to reply. So, i had the following two queries: $query = "SELECT f.machine, f.player, f.score, f.meet FROM (SELECT machine, MAX(score) AS maxscore FROM LeagueResults1 GROUP BY machine) AS X INNER JOIN LeagueResults1 AS f ON f.machine = x.machine AND f.score = x.maxscore GROUP BY machine"; $query = "SELECT machine,AVG(score) AS avgscore FROM LeagueResults1 WHERE score > 0 GROUP BY machine"; And i've been trying to combine them into one query. I've tried making the second query (the query that finds the average scores for each machine) a subquery of the first query: $query = "SELECT f.machine, f.player, f.score, f.meet, (SELECT AVG(score) AS avgscore FROM LeagueResults1 WHERE score > 0) FROM (SELECT machine, MAX(score) AS maxscore FROM LeagueResults1 GROUP BY machine) AS X INNER JOIN LeagueResults1 AS f ON f.machine = x.machine AND f.score = x.maxscore GROUP BY machine"; But it just returns a zero for every row in the average column. I have tried other ways of writing the query but then it completely fails to execute. I know what i am doing is obviously not right, but despite researching and thinking about it, i can't figure out how to combine the two queries properly.

Thanks for your suggestions. However, i still haven't been able to get it working how i'd like. A quick re-cap: I want the results of the following two queries to be used in the same html table. The first query finds the high score for every machine. The second query finds the average score on every machine. I know that both of these queries work on their own as i am using them already. It's when i try to combine the results into one html table that i have problems: $query = "SELECT f.machine, f.player, f.score, f.meet FROM (SELECT machine, MAX(score) AS maxscore FROM LeagueResults1 GROUP BY machine) AS X INNER JOIN LeagueResults1 AS f ON f.machine = x.machine AND f.score = x.maxscore GROUP BY machine"; $result = mysqli_query($cxn,$query) or die ("Couldn't execute query"); $nrows = mysqli_num_rows($result); for ($i=0;$i<$nrows;$i++) { $row = mysqli_fetch_assoc($result); extract($row); $score = number_format($score); } $query = "SELECT machine,AVG(score) AS avgscore FROM LeagueResults1 WHERE score > 0 GROUP BY machine"; $result = mysqli_query($cxn,$query) or die ("Couldn't execute query"); $nrows = mysqli_num_rows($result); for ($i=0;$i<$nrows;$i++) { $row = mysqli_fetch_assoc($result); extract($row); $avgscore = number_format($avgscore); echo "<tr class='border'>\n <td class='firstcolumn'>$machine</td>\n <td style='text-align: right' class='scores'>$score</td>\n <td>$player</td>\n <td>$meet</td>\n <td style='text-align: right' class='scores'>$avgscore</td>\n </tr>\n"; echo "<tr><td colspan='5' class='lines'><hr class='dashes'></td></tr>\n"; } echo "<table>\n"; I've also attached a screenshot of the html table that the above code produces and will explain this below: - The machine column correctly lists all machines in alphabetical order. - The High Score, Player and Meet columns are correct only for the last row of the table (the Vector machine), but they have been repeated on every row of the table. - The UKPBL Average column correctly displays the average score for each of the machines in the Machine column. So, the problem is that although i am getting the correct High Score, Player and Meet for the Vector machine/row, it's just repeating it for all the other machines/rows. If you are patient enough to offer some advice i would really appreciate it! [attachment deleted by admin]

Thanks for your reply. Do you mean changing this query: $query = "SELECT machine,AVG(score) AS avgscore FROM LeagueResults1 WHERE score > 0 GROUP BY machine"; ... to this query: $query = "SELECT machine,AVG(score) AS avgscore FROM LeagueResults1 GROUP BY machine HAVING MIN(score) > 0"; I did this but now it doesn't list the average score for every machine. There are currently 23 machines in the table, but the new query only lists 17 of them. I've looked at the machines that aren't listed but can't see why they are missing. The original query listed all 23 machines.

Hi, I have a table called LeagueResults1 with the following five columns: ResultID, Machine, Player, Score, Meet I have an SQL query that finds the highest score per machine: $query = "SELECT f.machine, f.player, f.score, f.meet FROM (SELECT machine, MAX(score) AS maxscore FROM LeagueResults1 GROUP BY machine) AS X INNER JOIN LeagueResults1 AS f ON f.machine = x.machine AND f.score = x.maxscore GROUP BY machine"; I display the results in an html table with the following headings: Machine, Highscore, Player, Meet I also have an SQL query that finds the average score per machine: $query = "SELECT machine,AVG(score) AS avgscore FROM LeagueResults1 WHERE score > 0 GROUP BY machine"; I display the results in an html table with the following headings: Machine, Average Score However i now want to use the results of both queries in the same html table with the following headings: Machine, Highscore, Player, Meet, Average Score I can't figure out how to get the results from the two separate queries into the same html table. Actually, i'm not sure whether the solution is to rewrite my two SQL queries as a single query, or whether the two queries should be kept and PHP used to organise the results. I've tried both but have still not been successful. Any help would be much appreciated.

I now have the league positions displaying perfectly. Thanks for your help and quick replies.

Excellent. Thanks for your help. To get this working i just needed to alter the line: if ($score !== $row['score']) { to if ($score != $row['score']) { Now i am wondering if there is a way to make the positioning more accurate. For example, when two players are equal third, the positions appear so: 1 2 3 3 4 5 But really, they should appear so: 1 2 3 3 5 6 Fourth place should not exist, because equal third should be the third and fourth positions. So, after giving equal positions, the code needs to give the next position as the row number, not simply as the next number in the sequence. If there is a way to do this, i would really appreciate someone pointing me in the right direction. Thanks, again.

Hi, I'm displaying an SQL table, ordered by a column named 'points'. It's showing the total points for players in a league. I have used: for ($n=1;$n<$nrows;$n++) to number the rows of the table from 1 onwards to make it easy for players to see their position in the league. So far, so good. However, as some players have the same number of points as each other, i need the PHP code to take account of this and give them the same position. A simplified example of what i am trying to achieve is: Position Points Player 1 25 A 2 23 B 3 22 C 3 22 D 5 18 E 6 15 F I've searched around for advice on how to go about this but not come up with anything and would really appreciate any help. Thanks.