cmb Posted November 29, 2011 Share Posted November 29, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/252079-viewing-query-results/ Share on other sites More sharing options...
btherl Posted November 30, 2011 Share Posted November 30, 2011 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? Quote Link to comment https://forums.phpfreaks.com/topic/252079-viewing-query-results/#findComment-1292484 Share on other sites More sharing options...
cmb Posted December 1, 2011 Author Share Posted December 1, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/252079-viewing-query-results/#findComment-1292851 Share on other sites More sharing options...
cmb Posted December 1, 2011 Author Share Posted December 1, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/252079-viewing-query-results/#findComment-1292858 Share on other sites More sharing options...
cmb Posted December 2, 2011 Author Share Posted December 2, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/252079-viewing-query-results/#findComment-1293318 Share on other sites More sharing options...
btherl Posted December 6, 2011 Share Posted December 6, 2011 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? Quote Link to comment https://forums.phpfreaks.com/topic/252079-viewing-query-results/#findComment-1295071 Share on other sites More sharing options...
cmb Posted December 7, 2011 Author Share Posted December 7, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/252079-viewing-query-results/#findComment-1295150 Share on other sites More sharing options...
xyph Posted December 7, 2011 Share Posted December 7, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/252079-viewing-query-results/#findComment-1295153 Share on other sites More sharing options...
cmb Posted December 7, 2011 Author Share Posted December 7, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/252079-viewing-query-results/#findComment-1295154 Share on other sites More sharing options...
btherl Posted December 8, 2011 Share Posted December 8, 2011 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? Quote Link to comment https://forums.phpfreaks.com/topic/252079-viewing-query-results/#findComment-1295585 Share on other sites More sharing options...
cmb Posted December 9, 2011 Author Share Posted December 9, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/252079-viewing-query-results/#findComment-1296039 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.