SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2001
    Location
    LA, California
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Accessing mysql result set

    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:

    Code:
      $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

  2. #2
    SitePoint Wizard silver trophy KLB's Avatar
    Join Date
    Nov 2003
    Location
    Maine USA
    Posts
    3,781
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The following is probably what you are asking for:
    Code:
    	$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);
    Ken Barbalace: EnvironmentalChemistry.com (Blog, Careers)
    InternetSAR.org
    Volunteers Assist Search and Rescue via Internet
    My Firefox Theme: Classic Compact
    Based onFirefox's default theme but uses much less window space

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2001
    Location
    LA, California
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SitePoint Wizard silver trophy KLB's Avatar
    Join Date
    Nov 2003
    Location
    Maine USA
    Posts
    3,781
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Ken Barbalace: EnvironmentalChemistry.com (Blog, Careers)
    InternetSAR.org
    Volunteers Assist Search and Rescue via Internet
    My Firefox Theme: Classic Compact
    Based onFirefox's default theme but uses much less window space

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2001
    Location
    LA, California
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •