Searchstring missing out a row on each set of records

[MargateSteve]

I have a table which contains a list of football seasons. A season runs from around July until around May and is named after the two years it covers. For example the season that ran from July 2008 until May 2009 is the 2008/09 season.

I have a table named "Seasons" table and in the column 'season_name' I have a list of the seasons named as above.

 

I have set up a list of links that take the first three characters of 'season_name' to create some ad-hoc decade links.

 <?php
//Query for the DECADES INDEX at the top
$links = mysql_query("SELECT DISTINCT SUBSTRING(season_name,1,3) as decade FROM seasons ORDER BY 1") or die(mysql_error());

// Store first three numbers of SEASON NAME into $linkrow
$linkrow = mysql_fetch_array($links );

// Fetch the records of the DECADE $row
while($linkrow = mysql_fetch_array($links))
{

// Show links to DECADE queries and add "0's" to the result
echo '<a href="seasonslistdecades.php?searchstring=';
echo $linkrow['decade']. '">'.$linkrow['decade'].'0\'s</a> ';
}
?>

 

In the receiving page (seasonslistdecades.php) I have the following

<?php

//Show which DECADE the table is showing by adding "0's" to searchstring results	
echo $_GET["searchstring"].'0\'s';

// Retrieve the SEASONS for the correct DECADE
$result = mysql_query("SELECT * FROM seasons WHERE season_name LIKE '".$_GET["searchstring"]."%' ")
or die(mysql_error());  

// Fetch the records of the SEASON $row
$row = mysql_fetch_array( $result );

// Print the TABLE HEADERS
  echo '<table width="95%" border="1" cellspacing="0" cellpadding="0">
  <tr>
    <th>ID</th>
    <th>Name</th>
    <th>From</th>
    <th>To</th>
  </tr>';

// Fetch the SEASONS
  while($row = mysql_fetch_array($result))
{

// Print the SEASONS
echo '<tr>
    <td>'.$row['season_id'] .'</td>
    <td>'.$row['season_name'] .'</td>
    <td>'.$row['season_start'] .'</td>
    <td>'.$row['season_end'] .'</td>
  </tr>
' ;
}
echo '</table> ';


?>

 

It works almost correctly but it misses out the first season of each decade. For example the 1990's  show up 1991/92, 1992/93 all the way up to 1999/00 for the season that crosses over into 2000.

It does not, however, show 1990/91. This is the same for all the decades - the 1980's has everything except 1980/81 and the 2000's has everything except 2000/01.

 

It seems to be passing everything correctly but anything that has 0 as it's fourth digit is being ignored and I am, yet again, stumped by something that is probably very simple to sort out!

 

As always, any suggestions, including a complete rewrite, would be gratefully received.

 

Thanks in advance

Steve

[PFMaBiSmAd]

$linkrow = mysql_fetch_array($links );

 

^^^ Why do you have that line of code in your code, between where you execute your query and where you actually loop over the results set?

[MargateSteve]

It was something I picked up in a previous script and assumed it was a required part. However I can now see that exactly the same thing in the 'WHILE' statement so removed it.

I also removed the similarly superfluous line from the receiving page and everything now works fine.

 

As part of my learning curve, can anyone please explain why the extra line was causing the first record to not appear? I really do want to learn how to figure these problems out for myself!!!

 

Thanks, as always

Steve

[DavidAM]

// Store first three numbers of SEASON NAME into $linkrow
$linkrow = mysql_fetch_array($links );

// Fetch the records of the DECADE $row
while($linkrow = mysql_fetch_array($links))
{

 

When you call mysql_fetch_array() you retrieve a row from the query result and move the pointer to the next row. So your first call gets the first row and puts it in $linkrow. When you enter the while loop for the first time, you fetch another row (it is the second row in the result set). You never did anything with the first row so it was effectively thrown away.

[MargateSteve]

That explains it perfectly. Thanks David.

Steve