is a mysql result reference to records? or all records ready to iterate through?

[arianhojat]

I assume number 2 just wanted clarification.

 

$sql = "SELECT * FROM sometable";

$result = mysql_query($sql); // At this point is all data sent to client ready to be looped through in $result?

while ($row = mysql_fetch_assoc($result))  //or is data gotten from mysql now

{

    echo $row["userid"];

    echo $row["fullname"];

    echo $row["userstatus"];

}

 

is there anyway where you dont get all data sent to you at once, so you can go along as needed (kinda like goings though xml with SAX versus DOM )?

[cooldude832]

yeah its called limit and where read the sql hadnbook on it

[PHP_PhREEEk]

#1 is just a resource identifier. If it exists, then it is pointing to the first record it found per your criteria.

 

You can limit how many records MySQL returns, but if you want all the records per your criteria, you can load them all into an array for use at any time later.

 

<?php
$sql = "SELECT * FROM sometable";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result))
{
    $data[] = $row;
}

echo "<pre>"
print_r($data);
echo "</pre>";

 

PhREEEk

[arianhojat]

PhREEEk, So as the while loop is looping, data is being brought over from MYSQL?

[PHP_PhREEEk]

Yes, every iteration of the loop asks the resource for the next record. Once the resource is out of records, the loop condition evaluates to FALSE and exits.

 

PhREEEk

[revraz]

The Resource should be a temporary table that is created on the DB Server, so when you pull info from it, it doesn't have to re-query everything all over again, it only pulls from that temp table (Resource ID).

 

Its all done behind the scenes.

[arianhojat]

just wondering on page 87 of the book Object Oriented PHP,

it says the result set returned by mysql_query, all rows, are buffered in memory.

"..Your result set is buffered. it was created using the function mysql_query. Because a buffered result set stores all roews in memory, the record pointer can be repositioned [by mysql_data_seek]"

 

So is it just a resource pointer to 1st record? if so, why does the above seem to contradict that.

 

 

[PHP_PhREEEk]

We do try and keep the concepts very basic on this board... but yes, a resource pointer can be relocated just like an array index inside of PHP can be manipulated. The book does not lie, but rather presents an advanced understanding of the MySQL resource. 99% of those back here would never need to grasp that concept just to loop over their items.

 

PhREEEk

[arianhojat]

Sorry to bang out any more answers from ya PhREEEk...

So just to clarify for my own curiousity...

 

The resource identifier is pointing to the first record like stated above,

but also all data is passed in memory to the variable also on that one mysql_query line (for the while loop to loop over).

 

Because when i asked if the loop [w/ mysql_fetch_assoc] if its bringing data over from mySQL, you said yes, which i intrepreted as it is asking the resource for data from mySQL versus asking for data that is already brought over from mySQL in memory.

 

PhREEEk, So as the while loop is looping, data is being brought over from MYSQL (from a temp table as someone stated)

or everything has already been brought over (from a temp table) in the mysql_query line (the latter is what i assume, aka buffered)?

 

Thanks again for clarification, PhREEEk

 

Just curious what happens behind the scenes as it is interesting

[cooldude832]

Let me explain resources a bit for you.

 

A resource is any type of collection of data that on its own doesn't have any usefulness, it needs to be worked so to speak to get a useful chunk out of it.  Another explain outside of mysql is fopen  this file that is opened is a resource.  A similar thing occurs in ODBC connections, IMAP, cURL and other socket or database type connections, the resource is just that a path way so to speak to your data.

 

[arianhojat]

so mysql_fetch_assoc is like a cursor then going through the data straight from the database resource. But then shouldnt this be called unbuffered? instead of buffered result as it seems people agree on that it is?

[cooldude832]

the buffering is in the query

http://us3.php.net/manual/en/function.mysql-unbuffered-query.php

 

[PHP_PhREEEk]

Sorry to bang out any more answers from ya PhREEEk...

So just to clarify for my own curiousity...

 

The resource identifier is pointing to the first record like stated above,

but also all data is passed in memory to the variable also on that one mysql_query line (for the while loop to loop over).

 

Because when i asked if the loop [w/ mysql_fetch_assoc] if its bringing data over from mySQL, you said yes, which i intrepreted as it is asking the resource for data from mySQL versus asking for data that is already brought over from mySQL in memory.

 

PhREEEk, So as the while loop is looping, data is being brought over from MYSQL (from a temp table as someone stated)

or everything has already been brought over (from a temp table) in the mysql_query line (the latter is what i assume, aka buffered)?

 

Thanks again for clarification, PhREEEk

 

Just curious what happens behind the scenes as it is interesting

 

There still appears to be confusion...

 

Because when i asked if the loop [w/ mysql_fetch_assoc] if its bringing data over from mySQL, you said yes, which i intrepreted as it is asking the resource for data from mySQL versus asking for data that is already brought over from mySQL in memory.

 

That is what's basically going on... If you, for instance, assign $result to the result set, and then echo or print_r $result, you won't receive any data. That's because the resource isn't the data, it's just a pointer to the data, an ID. It's no different if you went to a library and asked the librarian for all books on SQL. She prints out a list of books and hands it to you. That's a resource. She's made your request into a nice list, but that list is not the actual books themselves.

 

Now, she gave you that list in a particular order. For most intents and purposes, you'd most likely just follow that list verbatim, since you were pretty specific on what you asked her to get for you. But, if you really wanted to, you could go grab book #4 on the list first, no harm no foul...

 

so, when you say...

 

but also all data is passed in memory to the variable also on that one mysql_query line (for the while loop to loop over).

 

That is not accurate. The variable only holds the resource ID, and no actual records themselves that you could access directly.

 

As far as buffering goes, there's no voodoo going on there. A mysql table is just a formatted array for all intents and purposes, with the ability for slightly more sophisticated relationships. And of course, those arrays are permanently stored for recall or modification at any time.

 

So, 'buffering' isn't much different from me taking an array in PHP with some complicated keying. I copy that array into a new array, leaving the original intact. I then slice, dice, re-order or whatever I want with the new array, and eventually format it for a browser session. This would take a huge effort on a large array, but MySQL is designed to deliver you the exact elements of your larger array all ready to go, no slicing or dicing needed (just a concise query).

 

PhREEEk

[cooldude832]

the non buffered querry has some advantages and disadvantages, for 99% of the world a buffer query works 1000 times better