Page 1 of 1

First record in mysql_fetch_array is skipped?!

Posted: Sun Mar 11, 2007 11:02 am
by Donny Bahama
I'm populating a list box with the results of mysql_query. (In the code below, I've commented out that section in favor of simply echoing the records to the screen, line by line.)

mysql_numrows returns the correct number of rows, and the query has been verified in phpmyadmin, but when the results are viewed, the first record is not echoed. If 2 records are returned, only the 2nd is echoed. If 3 records are returned, only the 2nd and 3rd are echoed. Etc.

The basic code for this is used on several pages of the site, and it works fine everywhere else. I'm really stymied as to what could be causing this. I could probably implement a workaround using mysql_data_seek, but I'd really like to know what the heck is going on here! Note in the code below that the sections for "if $num==0" and "else" ($num = 1) both work fine - it's just the elseif section (where $num (number of records returned) is > 1).
Thanks for taking the time to look at this!

Code: Select all

$lastname = $_POST['lastname'];
$query = "SELECT *
FROM alumni
WHERE Last='$lastname'";
$result = mysql_query($query,$dblink) or die(mysql_error());
$num = mysql_numrows($result);
$row = mysql_fetch_array($result);
if ($num == 0) {					// ALUMNUS NOT IN DATABASE
	echo "<h1><br /></h1><h3>DHS57.com Administration - Classmate Lookup</h3>";
	echo "<h4>Sorry, no classmates found with the last name '$lastname'.</h4>";
	include 'return.inc';
	echo "<input type='hidden' name='auth' value='$auth'></form>";
}
elseif ($num > 1){					// MULTIPLE RECORDS MATCHED - CHOOSE ONE
/*				echo "<h3>Select Classmate</h3>
			<form action='admin.php' method='post'>
			<table border='0' align='center'>
				<tr valign='middle'><td>";
					<select name='alum_select' size='$num'>";
		while($row = mysql_fetch_array($result)) {
		$userID = $row['ID'];
		$firstname = $row['First'];
		$lastname = $row['Last'];
		echo "<option value='".$userID."'>".$firstname." ".$lastname."</option>";
		echo "$userID - $firstname $lastname \n";
	}
	echo "</select><br /><br /><input class='inlinebutton' type='submit' name='submit' value='lookup'>";
	</td></tr></table>
	<input type='hidden' name='proc' value='sel_alum'>
	<input type='hidden' name='auth' value='$auth'></form>";
*/
	while($row = mysql_fetch_array($result)) {
		$userID = $row['ID'];
		$firstname = $row['First'];
		$lastname = $row['Last'];
		echo "$userID $firstname $lastname <br />";
	}
}
else {								// EDIT ALUMNUS RECORD
	$cm_id = $row['ID'];
	// echo "edit record for $first $last";
	include 'modrec.php';
	echo "<input type='hidden' name='auth' value='$auth'></form>";
}
break;

Posted: Sun Mar 11, 2007 11:11 am
by feyd
You have two mysql_fetch_array() calls. Remove the first one.

Posted: Sun Mar 11, 2007 11:14 am
by mentor
replace

Code: Select all

while($row = mysql_fetch_array($result)) { 
	$userID = $row['ID']; 
	$firstname = $row['First']; 
	$lastname = $row['Last']; 
	echo "$userID $firstname $lastname <br />"; 
}

with

Code: Select all

do { 
	$userID = $row['ID']; 
	$firstname = $row['First']; 
	$lastname = $row['Last']; 
	echo "$userID $firstname $lastname <br />"; 
} while($row = mysql_fetch_array($result));

Posted: Sun Mar 11, 2007 11:25 am
by mentor
If you remove first mysql_fetch_array(), then $row['ID'] will not have any value in your else statement

Code: Select all

else {                // EDIT ALUMNUS RECORD 
        $cm_id = $row['ID']; 
        // echo "edit record for $first $last"; 
        include 'modrec.php'; 
        echo "<input type='hidden' name='auth' value='$auth'></form>"; 
}

Posted: Sun Mar 11, 2007 12:56 pm
by AKA Panama Jack
Add the following line right before the while...

Code: Select all

mysql_data_seek($result, 0);
Your problem is you have already pulled out one record. Your code is completely skipping the first record if there are 2 or more records. You need to either process the first record before the while loop or reset the internal record pointer for the result set to 0 before the while loop processes the data.

Code: Select all

$lastname = $_POST['lastname'];
$query = "SELECT *
FROM alumni
WHERE Last='$lastname'";
$result = mysql_query($query,$dblink) or die(mysql_error());
$num = mysql_numrows($result);
$row = mysql_fetch_array($result);
if ($num == 0) {					// ALUMNUS NOT IN DATABASE
	echo "<h1><br /></h1><h3>DHS57.com Administration - Classmate Lookup</h3>";
	echo "<h4>Sorry, no classmates found with the last name '$lastname'.</h4>";
	include 'return.inc';
	echo "<input type='hidden' name='auth' value='$auth'></form>";
}
elseif ($num > 1){					// MULTIPLE RECORDS MATCHED - CHOOSE ONE
/*				echo "<h3>Select Classmate</h3>
			<form action='admin.php' method='post'>
			<table border='0' align='center'>
				<tr valign='middle'><td>";
					<select name='alum_select' size='$num'>";
		mysql_data_seek($result, 0);
		while($row = mysql_fetch_array($result)) {
		$userID = $row['ID'];
		$firstname = $row['First'];
		$lastname = $row['Last'];
		echo "<option value='".$userID."'>".$firstname." ".$lastname."</option>";
		echo "$userID - $firstname $lastname \n";
	}
	echo "</select><br /><br /><input class='inlinebutton' type='submit' name='submit' value='lookup'>";
	</td></tr></table>
	<input type='hidden' name='proc' value='sel_alum'>
	<input type='hidden' name='auth' value='$auth'></form>";
*/
	mysql_data_seek($result, 0);
	while($row = mysql_fetch_array($result)) {
		$userID = $row['ID'];
		$firstname = $row['First'];
		$lastname = $row['Last'];
		echo "$userID $firstname $lastname <br />";
	}
}
else {								// EDIT ALUMNUS RECORD
	$cm_id = $row['ID'];
	// echo "edit record for $first $last";
	include 'modrec.php';
	echo "<input type='hidden' name='auth' value='$auth'></form>";
}
break;
or

Code: Select all

$lastname = $_POST['lastname'];
$query = "SELECT *
FROM alumni
WHERE Last='$lastname'";
$result = mysql_query($query,$dblink) or die(mysql_error());
$num = mysql_numrows($result);
$row = mysql_fetch_array($result);
if ($num == 0) {					// ALUMNUS NOT IN DATABASE
	echo "<h1><br /></h1><h3>DHS57.com Administration - Classmate Lookup</h3>";
	echo "<h4>Sorry, no classmates found with the last name '$lastname'.</h4>";
	include 'return.inc';
	echo "<input type='hidden' name='auth' value='$auth'></form>";
}
elseif ($num > 1){					// MULTIPLE RECORDS MATCHED - CHOOSE ONE
/*				echo "<h3>Select Classmate</h3>
			<form action='admin.php' method='post'>
			<table border='0' align='center'>
				<tr valign='middle'><td>";
					<select name='alum_select' size='$num'>";
		echo "<option value='".$row['ID']."'>".$row['First']." ".$row['Last']."</option>";
		echo "$row[ID] - $row[First] $row[Last] \n";
		while($row = mysql_fetch_array($result)) {
		$userID = $row['ID'];
		$firstname = $row['First'];
		$lastname = $row['Last'];
		echo "<option value='".$userID."'>".$firstname." ".$lastname."</option>";
		echo "$userID - $firstname $lastname \n";
	}
	echo "</select><br /><br /><input class='inlinebutton' type='submit' name='submit' value='lookup'>";
	</td></tr></table>
	<input type='hidden' name='proc' value='sel_alum'>
	<input type='hidden' name='auth' value='$auth'></form>";
*/
	echo "$row[ID] $row[First] $row[Last] <br />";
	while($row = mysql_fetch_array($result)) {
		$userID = $row['ID'];
		$firstname = $row['First'];
		$lastname = $row['Last'];
		echo "$userID $firstname $lastname <br />";
	}
}
else {								// EDIT ALUMNUS RECORD
	$cm_id = $row['ID'];
	// echo "edit record for $first $last";
	include 'modrec.php';
	echo "<input type='hidden' name='auth' value='$auth'></form>";
}
break;

EDIT: I added the changes to your commented out code as well.

Posted: Sun Mar 11, 2007 8:43 pm
by Donny Bahama
Thanks so much for spotting the extra fetch statement, everyone! The data_seek fix worked like a dream. I really appreciate the help! :)

Posted: Thu Mar 22, 2007 12:14 pm
by technod
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hi guys, I'm having the same problem that the first element of the array is being skipped (due to using the fetch statement twice). 
This method is obviously flawed, even though the code I'm using came straight out of a book! 

here's my code:

Code: Select all

//create the array
$result = mysql_query ("SELECT category_name FROM categories");
$row = mysql_fetch_array ($result);

// Loop through and print the records.
while ($row = mysql_fetch_array($result)) {
echo $row['category_name'];
}

Please can you tell me what is the best way to print an array from a DB query without having to use the "mysql_data_seek($result, 0);" hack?

Thanks in advance for your advice


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Thu Mar 22, 2007 12:30 pm
by volka
technod wrote:This method is obviously flawed, even though the code I'm using came straight out of a book!
A 100% copy&paste? What's the name of that book?

Code: Select all

$row = mysql_fetch_array ($result);
fetches the next record from the result set.

Code: Select all

$row = mysql_fetch_array ($result);
while ($row = mysql_fetch_array($result)) {
fetches one record from the result set before fetching the remaining records in a while loop

Posted: Thu Mar 22, 2007 12:42 pm
by RobertGonzalez
Step 1. Throw that piece of junk book into the trash.
Step 2. Replace:

Code: Select all

<?php
//create the array
$result = mysql_query("SELECT category_name FROM categories");
$row = mysql_fetch_array ($result);

// Loop through and print the records.
while ($row = mysql_fetch_array($result)) {
    echo $row['category_name'];
} 
?>
With:

Code: Select all

<?php
//create the array
$result = mysql_query("SELECT category_name FROM categories") or die('Could not run the query: ' . mysql_error();

// Loop through and print the records.
while ($row = mysql_fetch_array($result)) {
    echo $row['category_name'];
} 
?>

Posted: Thu Mar 22, 2007 2:20 pm
by technod
Thanks very much Everah! that solution worked! :D