Accessing mysql result set

[DannyTCOTW]

Hi All

This shouldn't be too hard, but I can't seem to dig up the answer.

I have quite a complex SQL query that I would like to use in different ways. The main objective is to loop through the results and display them nicely in a page.

One of the results of the query is the category that the db entry is assigned to (seperate table). I'd like to add the categories to a form which is outside of my while() loop. The reason for this would be so that I could have a pulldown menu that would allow people to limit the results based on the category.

I'm using pretty standard code to display the results. Obviously, I can only do the while loop on the results set once. I'm assuming php either clears it from memory after it's finished, or it has something to do with the array pointer being at the end of the array.


Lets say that this is my code for retrieving and displaying the results:

  $sql = mysql_query("SELECT * FROM foo_table");
  
  while($result = mysql_fetch_array($sql)){
  
  	echo $result['id'];
  }

How would I go about accessing $sql in another loop without doing another mysql_query()?

Can I?
Does this make sense?

Thanks
Marc

[KLB]

The following is probably what you are asking for:

	$strSQL="SELECT * FROM SomeTable";
	$result=mysql_query($sql,$dbConnection);
	$num = mysql_num_rows($result);
	$intCur=1;
	while ($intCur<=$num){
		$row = mysql_fetch_array($result);
		echo $row['SomeField'];
		++$intCur;
		}
	mysql_free_result($result);

[DannyTCOTW]

hey klb

That would still only allow me to print out the results once though, right? So, if I wanted a second while() loop after the first one, and still be able to access the data inside $result without doing another mysql_query(), is that possible?

Or do I just do another mysql_fetch_array inside the second while loop?

Thanks
Marc

[KLB]

If you want to run through the loop multiple times, personally, I'd fetch the results into an array and then run through the array as needed. This would allow you to more quickly close your database connection. You could also build multiple strings at once and then echo them out after you have closed the connection. For instance:

$FirstString .= $row['SomeField']." some text ".$row['SomeField1'];
$SecondString .= $row['SomeField2']." some text ".$row['SomeField3'];

If you are asking how to get multiple fields from the same dataset at the same time, then simply request each field. For example:

echo $row['SomeField'];
echo $row['SomeField1'];
echo $row['SomeField2'];
echo $row['SomeField3'];

Unlike ASP or VBA, there is no need for an equivelant to the MoveNext statement as this is done automatically.

[DannyTCOTW]

ah yes...

I tried putting them into an array at one point and then doing a foreach on the array, but I was having issues accessing the values I needed in the foreach.

Having tried again this morning however, I managed to get it right. Obviously wasn't thinking too well yesterday when I tried it .

Thanks for all your help.
Marc