Viewing query results

[cmb]

this is my query code

<?php
$wsi_query = "(SELECT * FROM (SELECT a.no_Yards AS 'yards', a.Day_Swam AS 'date', a.Season AS 'season', (SELECT sum(no_yards) FROM swimming WHERE Season = '$ws') AS 'totalyards', (SELECT AVG(no_Yards) FROM swimming WHERE Season = '$ws') AS 'avg', (SELECT count(*) FROM swimming WHERE Season ='$ws') AS 'timelaps', (SELECT 1 + count(*) FROM swimming b WHERE b.no_Yards > a.no_Yards AND Season = '$ws') AS hrank, (SELECT 1 + count(*) FROM swimming b WHERE b.no_Yards < a.no_Yards AND Season = '$ws') AS lrank FROM swimming  AS a) AS x WHERE Season = '$ws' )";
$wsi_result = mysql_query($wsi_query) or die("Query failed ($wsi_query) - " . mysql_error());
$wsi_row = mysql_fetch_assoc($wsi_result);	
?>

what it should do is output both the min and the max values like this

<?php
while ($mm_wsi_row = mysql_fetch_assoc($wsi_result)){
	if ($mm_wsi_row['hrank'] == 1){
		if ($mm_wsi_row['date'] == "0000-00-00"){$dayswam = "";}else{$dayswam = $mm_wsi_row['date'];}
		echo "<tr>";
		echo "<td style='background-color:#F90'>Max Length:</td>";
		echo "<td>" . number_format($mm_wsi_row['yards']) . "</td>";
		echo "<td>" . $dayswam . "</td>";
		echo "</tr>";
	}
	 if($mm_wsi_row['lrank'] == 1){
		if ($mm_wsi_row['date'] == "0000-00-00"){$dayswam = "";}else{$dayswam = $mm_wsi_row['date'];}
		echo"<tr>";
		echo "<td style='background-color:#F90'>Min Length:</td>";
		echo "<td style='background-color:#FF9'>". number_format($mm_wsi_row['yards']) . "</td>";
		echo "<td style='background-color:#FF9'>" . $dayswam . "</td>";
		echo "</tr>";
	}
?>

the problem is that it doesn't work right all the time for some it just shows the min while others it just shows max I've tested the query in MySQL workbench so i know that that is working right but idk why it works right for some and doesn't work for others

[btherl]

When you test the query in mysql workbench for one of the seasons where the min or max is not showing, what output do you get?

[cmb]

when i put season = 6 these are the records it brings back which is correct and it doesn't show the min only the max in the php also when season = 1 the same thing happens idk if its important but seasons start at 0 and go to 6

yards;date;season;totalyard;avg;timelaps;hrank;lrank
1300;2011-11-18;6;22500;2500.0000;9;9;1
1700;2011-11-19;6;22500;2500.0000;9;8;2
2200;2011-11-21;6;22500;2500.0000;9;7;3
2500;2011-11-22;6;22500;2500.0000;9;6;4
3000;2011-11-23;6;22500;2500.0000;9;3;7
3300;2011-11-25;6;22500;2500.0000;9;1;9
2800;2011-11-26;6;22500;2500.0000;9;4;6
3100;2011-11-29;6;22500;2500.0000;9;2;8
2600;2011-11-28;6;22500;2500.0000;9;5;5

[cmb]

also i just tested to see what is actually being returned by the php so i added this to my query to limit the results to the 2 i actually care about "WHERE Season = '$ws' AND (hrank = '1' OR lrank = '1')" and used this while statement

 
<?php
while ($mm_wsi_row = mysql_fetch_assoc($wsi_result)){
	echo $mm_wsi_row['yards'];
}
?>

and  it only returns 1 value instead of 2 like it's  suppose and when  i run the same thing (replacing $ws with 6) in workbench i get 2 results

[cmb]

i uploaded it my site and now only the season 6 doesn't work and also i found that when the query runs and i output all the data in a while loop this is what i get

1700 hrank 10 lrank 2
2200 hrank 9 lrank 3
2500 hrank 8 lrank 4
2600 hrank 7 lrank 5
2800 hrank 6 lrank 6
3000 hrank 5 lrank 7
3100 hrank 4 lrank 8
3300 hrank 3 lrank 9
3700 hrank 2 lrank 10
4100 hrank 1 lrank 11

it's missing this "1300 hrnank 11 lrank 1" and thats why im not getting the min but why would it do this

[btherl]

Can I confirm - when you run this query in workbench it gives all results, including lrank = 1, but when you run it in php and output all rows it is missing the lrank = 1 row?  Did you copy and paste the query when doing this comparison?

[cmb]

yes thats excatly what im saying and i did copy and paste except for were i changed the Season = '' i put the variable in its place

[xyph]

Every time I've seen this issue come up there's either an error in the query, or PHP is connecting using a MySQL user with insufficient privileges.

 

You should make sure the variable contains what you expect. Chances are it doesn't.

[cmb]

i don't think either of those things are the problem because the query works fine for the other 6 variables and some of the other data coming to the page uses the same varaible and its correct and idk about the privileges thing how could i test for that

[btherl]

The way to copy and paste a query is to have your php script echo the query like this:

 

print "<br>About to run this query: $wsi_query<br>";

 

And then copy and paste it exactly.

 

Also can you please paste the code you used to display all query results in a loop?

[cmb]

this is the while loop i use

<?php
while ($mm_wsi_row = mysql_fetch_assoc($wsi_result)){
	echo  $mm_wsi_row['yards'] . " hrank " . $mm_wsi_row['hrank'] . " lrank " . $mm_wsi_row['lrank'];
	echo "<br />";
}
?>

and when it runs this is the resuts

1700 hrank 10 lrank 2
2200 hrank 9 lrank 3
2500 hrank 8 lrank 4
2600 hrank 7 lrank 5
2800 hrank 6 lrank 6
3000 hrank 5 lrank 7
3100 hrank 4 lrank 8
3300 hrank 3 lrank 9
3700 hrank 2 lrank 10
4100 hrank 1 lrank 11

here is a link to the actual page in use if u want to see http://swimming.photosbychristian.com/index.php