[MySQL & PHP] Returning records always off by one

[tatlar]

Hi there,

I have some PHP code that pulls from a MySQL table. I pull both the total number of matching records and then the values of 3 fields of each record. In my HTML output I get the correct number of matching records, but the list of values is always short by one record.

For example, I send a query that should return 4 records. I get four records with my
$total_num_refs = mysql_num_rows ( $result ) ;
variable, but when I try and print out the field values in a table there is always one record missing. Here is the code (its part of an if/else statement that checks to see some form info has been submitted):

if ( $_POST['authors'] ) {
$k = $_POST['authors'] ;
$q = "SELECT Authors,Year,Title,ID FROM $tablename2 WHERE Authors LIKE '%k%' ORDER BY Year DESC" ;

$link_id = db_connect ( $default_dbname ) ;
if ( !$link_id ) error_message ( sql_error () ) ;

$result = mysql_query ( $q, $link_id ) ;
if ( !$result ) error_message ( sql_error () ) ;

$query_data = mysql_fetch_row ( $result ) ;
$total_num_refs = mysql_num_rows ( $result ) ;

if (!$total_num_refs) {
print "<p align=\"center\"><strong>No results found with those authors</strong></p>" ;
}

else {
echo "<center><strong>Your search for authors \"$k\" returned $total_num_refs references.</strong></center>\n" ;
echo "<br/><br/>\n" ;
echo "<table width=90% border=0 align=center cellpadding=4 cellspacing=0>\n" ;
echo "<tr>\n" ;
echo "<th width=40% NOWRAP class=forumHeader>Authors</th>\n" ;
echo "<th width=10% NOWRAP class=forumHeader>Year</th>\n" ;
echo "<th width=50% NOWRAP class=forumHeader>Title</th>\n" ;
echo "</tr>\n" ;
for ( $x=0; $x<$total_num_refs; $x++) {
$row = mysql_fetch_row( $result ) ;
$Authors = $row['Authors'];
$Year = $row['Year'];
$Title = $row['Title'];
$ID = $row['ID'];
echo "<tr>\n" ;
echo "<td width=\"40%\" class=\"forumTableLeft\"><a href=\"ref_detail.php?ID=$ID\">$Authors</a></td>\n" ;
echo "<td width=\"10%\" class=\"forumTable\" align=\"center\">$Year</td>\n" ;
echo "<td width=\"50%\" class=\"forumTableRight\">$Title</td>\n" ;
echo "</tr>\n" ;
}
echo "<tr>\n" ;
echo "<th width=40% NOWRAP class=forumHeader>Authors</th>\n" ;
echo "<th width=10% NOWRAP class=forumHeader>Year</th>\n" ;
echo "<th width=50% NOWRAP class=forumHeader>Title</th>\n" ;
echo "</tr>\n</table>\n" ;

}
}

Thanks in advance,

- Tatlar

[tatlar]

I got it to work. I took out this line:

$query_data = mysql_fetch_row ( $result ) ;

which I think somehow screwed the whole query up. I then inserted the query directly with the start of the output and called an array instead of using mysql_fetch_row, thus:

echo "<tr>\n" ;
echo "<th width=40% NOWRAP class=forumHeader>Authors</th>\n" ;
echo "<th width=10% NOWRAP class=forumHeader>Year</th>\n" ;
echo "<th width=50% NOWRAP class=forumHeader>Title</th>\n" ;
echo "</tr>\n" ;
while ( $resultset = mysql_fetch_array( $result) ) {
$Authors = $resultset['Authors'];
$Year = $resultset['Year'];
$Title = $resultset['Title'];
$ID = $resultset['ID'];
echo "<tr>\n" ;
echo "<td width=\"40%\" class=\"forumTableLeft\"><a href=\"ref_detail.php?
ID=$ID\">$Authors</a></td>\n" ;
echo "<td width=\"10%\" class=\"forumTable\" align=\"center\">$Year</td>\n
" ;
echo "<td width=\"50%\" class=\"forumTableRight\">$Title</td>\n" ;
echo "</tr>\n" ;

Cheers,

- Tatlar

[torrent]

It's not screwing the query up, it's doing exactly what you've asked it to. That is to pull back a row from the result set.

Therefore, if you had 4 rows returned, then that statement pulls back the first one and leaves you with three. You have done the right thing to remove the statement from there as it didn't appear to be contributing to anything you were doing further in the code.

Btw, you don't have to do mysql_fetch_* before you do a mysql_num_rows(). Just in case that was what you thought.

Anyhoo, glad you got it working. :)