Forum Moderators: coopster
My PHP script below displays all fields listed in a MySQL table column, but it always leaves off the first record->
here's my code:
*************************
//create array from DB query
$result = mysql_query ("SELECT category_name FROM categories");
$row = mysql_fetch_array ($result);
// Loop through array and print the records.
while ($row = mysql_fetch_array($result)) {
echo $row['category_name'];
}
*************************
The solution in the other post says to put a line of code before the while statement to display the first entry->
**
mysql_data_seek($result, 0);
**
Is this the only way to solve the problem? (It seems more like a hack then a proper method to print an array...)
Thanks in advance for your advice
//create array from DB query
$result = mysql_query ("SELECT category_name FROM categories");// Loop through array and print the records.
while ($row = mysql_fetch_assoc($result)) {
echo $row['category_name'];
}
The problem in that other post is that the OP was reading the first row with:
mysql_fetch_row
then proceeding down into a while loop. If s/he had rearranged the code to eliminate the mysql_fetch_row line, the recordset wouldn't have needed to be rewound. When you fetch a row from a query, mysql moves to the next record so the row is, in effect, 'used up'.
Unless you've left some stuff out in your code snippet, you don't have that problem, and actually I don't see a problem at all. Are you sure it's really leaving the first record out? mysql doesn't always keep things in the order you might expect, if you've deleted records and such. Count the rows in the table and count the rows in your output - I'm betting that all the records are actually there. You could also explicitly order the output:
SELECT category_name FROM categories ORDER BY category_name
I tested the new code i.e. I just dropped the line:
**** $row = mysql_fetch_array ($result); ****
and it worked perfect- Thanks very much!
Justgowithit is correct. Each time mysql_fetch_array is called, the internal data pointer is being advanced by one. So by calling it before the while loop, which prints out the results, you have already advanced the pointer. So when you do get to the while loop, you are starting at the second row, and not the first.
[edit]
Glad you got it :)
Also, try using assoc instead of array sine you're calling by column name
Just asking out of general interest, how much of a performance saving is this?
I did a little reading up, and my understanding is that all arrays in php are associate arrays, but that mysql_fetch_array generates two sets of keys, one with the column names as keys, and one as sequential integers. mysql_fetch_assoc will only generate the column names.
Am I right? And is the saving in the query only, or also when you're using the array? Is the saving significant?
Just curious really - I'm trying to improve my database skills at the moment.
Just asking out of general interest, how much of a performance saving is this?
Taken from the documentation:
Performance: An important thing to note is that using mysql_fetch_array() is not significantly slower than using mysql_fetch_row(), while it provides a significant added value.
I haven't personally done performance tests myself, however, it does make sense that the performance difference would be very small, if anything at all.
Probably good practice to not generate redundant keys if you don't need them is all, I suppose.
When I was young we had static arrays. If we wanted anything dynamic we had to make a linked list. And do you know what? We were happy! (with apologies to Monty Python)
