Retrieving Query Results

[JoJoJohnson]

Im trying to print the results from a query (in this example there should be 2 results. but the while loop I am using skips the first result, and only prints the second. can anyone help?

mysql_select_db($database_sql_connection, $sql_connection);
$query_collection = "SELECT * FROM tblCDCollection ORDER BY artist, year_released, title";
$check_collection = mysql_query($query_collection, $sql_connection) or die(mysql_error());
$row_collection = mysql_fetch_assoc($check_collection);
$totalRows_collection = mysql_num_rows($check_collection);

while ($row_collection = mysql_fetch_assoc($check_collection))
{

echo $row_collection['ID'];
echo ("<br>");

}

[jatar_k]

I assume there are only 2 rows

you need to reset the internal pointer after calling mysql_num_rows

...
$row_collection = mysql_fetch_assoc($check_collection); // whats this for?
$totalRows_collection = mysql_num_rows($check_collection);
mysql_data_seek($check_collection,0);

while ($row_collection = mysql_fetch_assoc($check_collection)) {
echo $row_collection['ID'];
echo ("<br>");
}

[JoJoJohnson]

works perfect. thank you very much

[coopster]

you need to reset the internal pointer after calling mysql_num_rows

When I first read this I thought jatar_k meant that whenever you use mysql_num_rows() you need to reset the internal row pointer with a data_seek. Then I thought, "hey, wait a minute, I never do that".

Then I realized that it is only because of the extra fetch caught there by jatar_k that the mysql_data_seek is required. mysql_fetch_assoc() will advance the internal row pointer within the result set, but mysql_num_rows() does not. This statement made solely on testing, I cannot find any definitive documentation that supports the theory. Anyone else have any input here?

[mek2600]

coopster is right. completely removing the first instance of:

$row_collection = mysql_fetch_assoc($check_collection);

will net the user what they want. you're pulling one set of results, then immediately doing that again in the while statement.

the alternative of that (in case you need to examine a first result) is to use a do... while loop. for example:

mysql_select_db($database_sql_connection, $sql_connection);
$query_collection = "SELECT * FROM tblCDCollection ORDER BY artist, year_released, title";
$check_collection = mysql_query($query_collection, $sql_connection) or die(mysql_error());
$row_collection = mysql_fetch_assoc($check_collection);
$totalRows_collection = mysql_num_rows($check_collection);

do {

echo $row_collection['ID'];
echo ("<br>");

}while($row_collection = mysql_fetch_assoc($check_collection))

but in this case, that's kinda silly unless there's more to the code than what's being shown here. do..while loops are only really wise unless you know for a fact that either you have data to use, or you're positive that you dont care and want at least 1 cycle through the loop.

[jatar_k]

maybe I am just old but you used to have to either re query after num_rows or use data_seek to reset the pointer. The change to reset the pointer automatically has obviously changed with out me noticing.

makes life easier ;)

[coopster]

Possibly. I never considered resetting the pointer and that's the part that is a bit concerning ... is it necessary?

From MySQL [dev.mysql.com]:

Once you have called mysql_store_result() and got a result back that isn't a null pointer, you may call mysql_num_rows() to find out how many rows are in the result set.

Nothing in the APIs regarding resetting the internal row pointer before doing so, at least not that I can find...

[jatar_k]

mysql_num_rows used to act like you ran through the full result set and left the pointer at the end, which never really made any sense. You could call seek afterwards to reset the pointer. Long ago I used to have to kill that pointer and requery, that might be showing my age though. ;)