Ummm help? mysql_fetch_assoc doesn't return all results ...

TylerNZ

Hi, I have a problem and it must be kinda common, yet I don't know how to search for the answer. I have a mysql query and then I show the results with:

while ($myrow = mysql_fetch_assoc($rsEvents2)) {
	echo $myrow['theDate'];
	echo "<br>";
	echo "<br>";
}

for example. And that prints out 7 results. However there should be 8 results. When I just do the query in MySql the query returns 8. When I fetch the number of rows: mysql_num_rows it gives me 8!!! Why oh WHY does the while statement not bring the FIRST variable? Can anyone help? PLEASE ...

mrhappiness

please show the code from mysql_query to this mysql_fetch_assoc

TylerNZ


$query_rsEvents2 = "SELECT EventID, Description, Pic, Date, DATE_FORMAT(Date, '%a %D, %l:%i%p') AS theDate,
DATE_FORMAT(Date, '%M %Y') AS theMonth FROM events 
WHERE MONTH(Date) = MONTH(CURRENT_DATE)+1 AND Date >= CURRENT_DATE
ORDER BY Date";
$rsEvents2 = mysql_query($query_rsEvents2, $conn) or die(mysql_error());
$row_rsEvents2 = mysql_fetch_assoc($rsEvents2);
$totalRows_rsEvents2 = mysql_num_rows($rsEvents2);

mrhappiness

yee see that

$row_rsEvents2 = mysql_fetch_assoc($rsEvents2);

there your 1st record get's lost

you want to remove this line or have a look at mysql_data_seek in the manual

TylerNZ

So that statement/query is getting all the records except the record at 0. ? And when it echo's the result, it echo's from the 1 record. Not the 0 record?

Is that right? Is there an easy way to get around this? And why do ppl use this method to get results when it doesn't return all the results?

I had a look at mysql_data_seek ... but .. I might have to find a tutorial to explain it a bit better.

what I find weird is that, this method of preforming a sql statement is widely used, but it doesn't return a true set of results!

laserlight

No, read what mrhappiness wrote carefully.

You are calling mysql_fetch_assoc() once before your loop.
Try calling it twice before the loop.

You will then see that the first 2 result sets are not processed by the loop.

And rightly so, since the result pointer to the rows has moved down by 2.

To reset the pointer, one can use [man]mysql_data_seek/man

Better yet, if you can avoid calling mysql_fetch_assoc() before the loop, then only call it within the loop.

TylerNZ

Laserlight, thank you SOOO much for the explaination!!! Now I understand!!! 🙂 THANK YOU THANK YOU THANK YOU!!!

Can I ask one question ... how would I use the mysql_data_seek ... in the above coding?

mrhappiness

mysql_data_seek($rsEvents2, 0);

TylerNZ

thanks MrHappiness ... you've made me happy! hehe.

🙂