php sql issue

[ded]

I have a page showing a list of events from tournaments/leagues (http://www.americandartsdatabase.com/player-rankings/included-leagues-tournaments/)

 

If you click on the CLICK HERE link, it should come up with a page that shows the stats for that particular event.  Some events it works and some it does not.

 

11th Annual ABDA Nationals works fine

North Side Dart League does not work

 

Below is the list of tournaments and leagues that are included in the rankings.  If you wish to have your tournaments and/or leagues included in the rankings, please register and send me the stats.

<?php
$mynewdate = strftime("%Y-%m-%d", strtotime("-730 days")); 
  echo "<font color=red><b>Included are leagues and tournaments from " . $mynewdate . " to today.</b></font>";
  $dbh=mysql_connect ("localhost", "database", "password") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("database"); 

  $query = "SELECT `event`, `date` FROM `stats` WHERE `date` >= '$mynewdate' GROUP BY `event` ,`date` ORDER BY `date`";  
  $rank = 0;
  $rankh = 0;
  $result = mysql_query($query,$dbh) or die(mysql_error());
?>
<table align=center width=90%>
    <tr>
      <th align="left">League/Tournament</th>
      <th align="left">Date</th>
      <th align="left">View Stats</th>
    </tr>
<?php
  while($row = mysql_fetch_array($result))
  {
        $event = $row['event'];
        $year = $row['date'];
        $url = "<a href=\"http://www.americandartsdatabase.com/player-rankings/included-leagues-tournaments/stats/?event=" . $event . "&year=" . $year . "\">Click Here</a>";
echo "<tr>";
  	echo "<td>" . $event . "</td>";
echo "<td>" . $year . "</td>";
echo "<td>" . $url . "</td>";
  	echo "</tr>";
  } 
?>
</table>

 

 

Here is the STATS code

<?php
  $event = $_GET['event'];
  $date= $_GET['year'];
  $rank = 0; 
  echo "<h1>Event: " . $event . "</h1><br>";
  echo "<h1>Date: " . $date . "</h1><br>";
  $dbh=mysql_connect ("localhost", "database", "password") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("database"); 
  $query = "SELECT `firstname`, `lastname`, `games`, `points`, `event`, `date`, (`points`/`games`) FROM `stats` WHERE `event` = '$event' and `date` = '$date' ORDER BY (`points`/`games`) DESC";
$result = mysql_query($query,$dbh) or die(mysql_error());
?> 
  <table align=center width=100%>
    <tr>
      <th align="left">Rank</th>
      <th align="left">Player</th>
      <th align="left">Games</th>
      <th align="left">Points</th>
      <th align="left">Average</th>
    </tr>

<?php
  while($row = mysql_fetch_array($result))
  {
        $rank = $rank + 1;
echo "<tr>";
  	echo "<td>" . $rank . "</td>";
  	echo "<td>" . $row['firstname'] . " " . $row['lastname'] . "</td>";
  	echo "<td>" . $row['games'] . "</td>";
        echo "<td>" . $row['points'] . "</td>";
  	echo "<td>" . number_format(($row['points']/$row['games']),4) . "</td>";
  	echo "</tr>";
  } 
?> 
</table>

[kicken]

On your stats page, echo you your generated sql query and then run it in mysql directly (using something like phpMyAdmin/mysql workbench/mysql cli tool).  That will let you test to make sure your query is correct and returns results.

 

A couple other things I notice, though probably not a cause of your current problem:

1) You should run your variables through urlencode before putting them into a url, and htmlentities before outputting them to your html.

2) You should run your variables through mysql_Real_escape_string before using them in a query.

 

 

[ded]

I changed the coding but still nothing.....weird.  Ths first 6 listed on http://www.americandartsdatabase.com/player-rankings/included-leagues-tournaments/ does not work, but the remaining 13 works fine.  Could it be a loop issue of some sort?

 

[kicken]

It seems to me your problem has to do with the &year= variable in your URL.  It's conflicting with your wordpress setup causing WP to error with no posts found.

 

Use a different variable name

 

[ded]

That was it!!!  I better check all code within the entire site for year.  Thanks!