Scoreboard help

[Smackie]

Hey everyone,

       I am creating a scoreboard script for a friend and he wants just to show top 10 scorers alright.

my question is how can i set foreach function to only display the top 10?

here is what the foreach i have right now

				if ($val != 0) {
					$query_rank = "SELECT rank FROM users WHERE callsign='$key'";
					$result_rank = mysql_query($query_rank) or die("Query Failed: ".mysql_error());
					while ($line = mysql_fetch_array($result_rank)) {
						$rank = $line['rank'];
					}
					echo "<tr>";
					echo "  <td align='left'>$rank ".ucwords($key)."</td>";
					echo "  <td align='right'>".number_format($val)."</td>";
					echo "</tr>";
				}
			}

it works right now but shows everyone that has a score which right now is like 16 people i need this to show only 10..

how can i do this?

Thank you
Smackie

[dagon]

use LIMIT in your query

[bradgrafelman]

Quite easily... just LIMIT the query so it only returns the number of results you want.

EDIT: sigh Too slow again.

[Smackie]

i tried that.. since i have WHERE i put it behind the WHERE it gives me a query failed if i put it after the WHERE it displays all of them still. either im doing something wrong or im a complete idiot right now lol..

[dagon]

$query_rank = "SELECT rank FROM users WHERE callsign='$key' LIMIT 10";

[Smackie]

thats what i have and it still shows 16 people on there not 10 :-\

[bradgrafelman]

The MySQL manual page dagon and I both linked you to shows you the order of the conditions/clauses you can use in a SELECT query.

If you still can't figure it out, show us what you tried.

[Smackie]

dagon;10953180 wrote:

$query_rank = "SELECT rank FROM users WHERE callsign='$key' LIMIT 10";

bradgrafelman i have tried it this way and still outputs 16 people instead of 10

[dagon]

Smackie;10953181 wrote:

thats what i have and it still shows 16 people on there not 10 :-[/QUOTE]

I don't see how that's possible, are you sure you uploaded the files after making the changes?

[Smackie]

yes i am editing them on my computer and saving them to my private server to see if they work. before uploading them to his server

[dagon]

sorry, you have to be doing something wrong, that's basic sql

check

right file\path\drive\server
browser\server\isp cache
duplicate code on page
file with same name different location
....

[Smackie]

the file is going to the correct place, there is no duplicates, and i know if i do something wrong it shows an error on the page.. so i know everything is right.. and i know thats basic sql.. but something isn't working.. ill try rebooting my private server see if that might fix it..

[bradgrafelman]

Show us the entire script if that doesn't work.

[Smackie]

<?php
if (($_REQUEST['month'] == 99) || (!$_REQUEST['month'])) {
	$month = date("m");
}
if (($_REQUEST['year'] == 9999) || (!$_REQUEST['year'])) {
	$year = date("Y");
}
?>

<table border="0" width="100%" cellpadding="0" cellspacing="0" class="box">
			<?php
			$top_scorers = array();
			$query_users = "SELECT callsign,rank FROM users ORDER BY callsign";
			$result_users = mysql_query($query_users) or die("Query Failed: ".mysql_error());
			while ($line = mysql_fetch_array($result_users)) {
				$callsign = $line['callsign'];
				$total = 0;
				$query_scores = "SELECT total FROM reports WHERE callsign='$callsign' AND MONTH(date)=$month AND YEAR(date)=$year";
				$result_scores = mysql_query($query_scores) or die("Query Failed: ".mysql_error());
				while ($line = mysql_fetch_array($result_scores)) {
					$total_score = $line['total'];
					$total += $total_score;
				}
				$top_scorers[$callsign] = $total;
			}
			arsort($top_scorers);
			foreach ($top_scorers as $key => $val) {
				if ($val != 0) {
					$query_rank = "SELECT rank FROM users WHERE callsign='$key' LIMIT 10";
					$result_rank = mysql_query($query_rank) or die("Query Failed: ".mysql_error());
					while ($line = mysql_fetch_array($result_rank)) {
						$rank = $line['rank'];
					}
					echo "<tr>";
					echo "  <td align='left'>$rank ".ucwords($key)."</td>";
					echo "  <td align='right'>".number_format($val)."</td>";
					echo "</tr>";
				}
			}
			?>

</table>

Thats what i have right now and it still just shows the same

[dagon]

the 16 is from the $top_scorers array that your looping through which is from a query with out a limit, i'm quite sure this could be done in one if not 2 queries, certainly not 3. then one loop

[Smackie]

i figured it out i did a count in foreach..

			$i = 0;
			foreach ($top_scorers as $key => $val) {
				if ($val != 0) {
					$query_rank = "SELECT rank FROM users WHERE callsign='$key'";
					$result_rank = mysql_query($query_rank) or die("Query Failed: ".mysql_error());
					while ($line = mysql_fetch_array($result_rank)) {
						$rank = $line['rank'];
					}
					echo "<tr>";
					echo "  <td align='left'>$rank ".ucwords($key)."</td>";
					echo "  <td align='right'>".number_format($val)."</td>";
					echo "</tr>";
				if(++$i > 9) break; 
				}
			}

Thanks for the help anyways guys 🙂

Smackie

[dagon]

that really is fugly compared with doing it in an 'optimised' way in the first place

[bradgrafelman]

Three SQL queries, one even nested in a loop from another query? Ouch.