Topic: [SOLVED] Is There A Different Way To Fetch MySQL Data

[NoSalt]

Hello All

    I have a question concerning MySQL fetches. This is how I, and I assume most PHP developers, get MySQL Data:

Code: [Select]

$sql = "select * from table";
$results = mysql_query($sql);
while($rows = mysql_fetch_array($results)){
    $value_01 = (isset($eventRows['value_01'])) ? $eventRows['value_01'] : null;
    $value_02 = (isset($eventRows['value_02'])) ? $eventRows['value_02'] : null;
    $value_03 = (isset($eventRows['value_03'])) ? $eventRows['value_03'] : null;
    $value_04 = (isset($eventRows['value_04'])) ? $eventRows['value_04'] : null;
    $value_05 = (isset($eventRows['value_05'])) ? $eventRows['value_05'] : null;

    echo $value_01 . " - " . $value_02 . " - " . $value_03 . " - " . $value_04 . " - " . $value_05 . "<br>\n";
}

According to PHP.net, the "mysql_query" function "Returns an associative array that corresponds to the fetched row and moves the internal data pointer ahead."

What I was wondering is if you can store the returned results so that they can be used multiple times on the same page without having to make multiple database calls? I am designing a page where I need to us values 1-5 in one loop but only value 1 in another loop. Database calls are expensive so I would rather make the initial data fetch, and store the results for future use. Something like:

Code: [Select]

$storage_device = mysql_fetch_array($results);

As you can guess, or probably already know, that doesn't work.


Thank you all for reading. Have a nice day/night.     

[genericnumber1]

mysql_fetch_array and the related functions work on an internal pointer. You can easily reset this pointer with mysql_field_seek()/mysql_data_seek() if you feel that is the best way to reuse data from a query result.

Alternatively, you could simply save the value in a variable and use it later.

[KingPhilip]

I'd save the value to a variable, and reuse it that way.

Also, there's no need to call for mysql_fetch_array, if you're not going to use the numeric indexes... try running mysql_fetch_assoc instead, it'll cut the array size in half

[NoSalt]

After reading about mysql_field_seek I'm not quite sure if I understand what is going on. I notice that if I use a while loop on "results" in the first loop, it looks like they are used up, for lack of a better term, and not available for the second loop. I say this because the second loop prints out no data when I iterate through it. I did some Google research on the function and I used the following line immediately before the second loop:

Code: [Select]

mysql_field_seek($results,0);

I did this in the hopes that it would reset the pointer to the beginning and I could use "results" again in another while loop. No luck though, as the second loop is still printing out nothing.

[NoSalt]

Successful Update!!!

I looked at mysql_data_seek and used the following line:

Code: [Select]

mysql_data_seek($results,0);

And it worked perfectly ... just as expected. I am now able to use the "$results" as many times as I want with only a single database call.

Also, I used mysql_fetch_assoc in place of mysql_fetch_array and that looks good as well. I'm not sure what mysql_fetch_assoc does exactly so I'll do some reading on PHP.net to figure it out.

Thank you all for reading and thank you genericnumber1 and KingPhilip for replying. Have a great day/night.