Dynamic access of DB values

[sapphiremagus]

I'm new to PHP, so please bear with me, heh. I've been programming for a while in C++ and I'm wondering if that's affecting what I'm doing. In learning PHP+MySql I've tried to set up a simple PHP file to loop generate an HTML version of a table's information. The header would contain the field names and each row would be a result set from the DB. Here's what I've been working with (in various incarnations):

<?PHP$con = mysql_connect("myhost","mylogin","mypassword");if (!$con){	die('Could Not Connect: ' . mysql_error());}$db = mysql_select_db("cbn_localgaming",$con);if (!$db){	die('Could not open connection to DB: ' . mysql_error());}$sql = "SELECT * FROM tblUsers";$result = mysql_query($sql,$con);echo "<table>";echo "<thead>";while($property = mysql_fetch_field($result)){	echo "<th>" . $property->name . "</th>";}echo "</thead>";while($row = mysql_fetch_array($result)){	echo "<tr>";	while($property = mysql_fetch_field($result))	{		echo "<td>" . $row[$property->name] . "</td>";	}	echo "</tr>";}echo "</table>";mysql_close($con);?>

I get the header output just fine. I thought it might be choking on taking the variable for an array ID, but then I looked at the source of the page. It's outputting the table row tags perfectly, meaning the first loop works fine. However, there are no cell tags at all, which tells me that the second loop isn't firing.Help?

[smartalco]

I believe it is because mysql_fetch_array will not go back to the beginning of your results on its own, you will either have to copy the query and find the results again right before the second loop, or return mysql_fetch_array to the beginning before the second loop (I don't know how to do this though :/)

[justsomeguy]

I think the reason is because mysql_fetch_field is advancing the internal pointer for the result, so that when you try to loop through all rows the result pointer is at the end. You can use the mysql_data_seek function to move the internal pointer. This would reset the result:mysql_data_seek($result, 0);If this is the case, that would mean that using mysql_fetch_field inside the loop for mysql_fetch_array would cause problems. So, it would be better to have an array of field names that you can reuse in the loop later on.

$fields = array();while($property = mysql_fetch_field($result)){	echo "<th>" . $property->name . "</th>";	$fields[] = $property->name;}echo "</thead>";while($row = mysql_fetch_array($result)){	echo "<tr>";	for ($i = 0; $i < count($fields); $i++)	{		echo "<td>" . $row[$fields[$i]] . "</td>";	}	echo "</tr>";}

[sapphiremagus]

I think the reason is because mysql_fetch_field is advancing the internal pointer for the result, so that when you try to loop through all rows the result pointer is at the end. You can use the mysql_data_seek function to move the internal pointer. This would reset the result:mysql_data_seek($result, 0);If this is the case, that would mean that using mysql_fetch_field inside the loop for mysql_fetch_array would cause problems. So, it would be better to have an array of field names that you can reuse in the loop later on.

$fields = array();while($property = mysql_fetch_field($result)){	echo "<th>" . $property->name . "</th>";	$fields[] = $property->name;}echo "</thead>";while($row = mysql_fetch_array($result)){	echo "<tr>";	for ($i = 0; $i < count($fields); $i++)	{		echo "<td>" . $row[$fields[$i]] . "</td>";	}	echo "</tr>";}

That worked perfectly, thanks. I had been considering using a pre-generated array of headers like that. Sadly I opted for the 'slick' method that didn't work, heh. Thanks again

[justsomeguy]

I just noticed I left out mysql_data_seek in that code, does it work without it? The documentation was not clear if the mysql_fetch_field function advances the internal pointer or not. If you don't need mysql_data_seek then clearly mysql_fetch_field does not advance the pointer, but in that case the reason the original code didn't work was probably because of some undocumented side effect with either mysql_fetch_field or mysql_fetch_array.

[sapphiremagus]

I just noticed I left out mysql_data_seek in that code, does it work without it? The documentation was not clear if the mysql_fetch_field function advances the internal pointer or not. If you don't need mysql_data_seek then clearly mysql_fetch_field does not advance the pointer, but in that case the reason the original code didn't work was probably because of some undocumented side effect with either mysql_fetch_field or mysql_fetch_array.

It does work as-is. After going over it to make sure I understood what was going on I basically plugged it into my file at the appropriate point, replacing my own code. Works fine

[justsomeguy]

Hmm.. I guess mysql_fetch_field has some undocumented side effect that causes the original code to fail then.