First record in mysql_fetch_array is skipped?!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Donny Bahama
Forum Newbie
Posts: 18
Joined: Wed Mar 22, 2006 2:48 pm

First record in mysql_fetch_array is skipped?!

Post 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;
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You have two mysql_fetch_array() calls. Remove the first one.
mentor
Forum Contributor
Posts: 100
Joined: Sun Mar 11, 2007 11:10 am
Location: Pakistan

Post 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));
Last edited by mentor on Thu Mar 15, 2007 7:05 am, edited 1 time in total.
mentor
Forum Contributor
Posts: 100
Joined: Sun Mar 11, 2007 11:10 am
Location: Pakistan

Post 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>"; 
}
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post 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.
Donny Bahama
Forum Newbie
Posts: 18
Joined: Wed Mar 22, 2006 2:48 pm

Post 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! :)
technod
Forum Newbie
Posts: 2
Joined: Thu Mar 22, 2007 12:04 pm

Post 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]
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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'];
} 
?>
technod
Forum Newbie
Posts: 2
Joined: Thu Mar 22, 2007 12:04 pm

Post by technod »

Thanks very much Everah! that solution worked! :D
Post Reply