I have tried personally MySQL a great deal, however i always wondered just how do you use it - after i get an optimistic result, where's the information saved exactly? For instance, I write such as this:

$sql = "SELECT * FROM TABLE"; 
$result = mysql_query($sql);
while ($row = mysql_fetch_object($result)) {
    echo $row->column_name;
}

Whenever a outcome is came back, I'm presuming it's holding all of the data results or will it return inside a fragment and just returns where it's requested, like $row->column_title?

Or will it really return each and every row of information even when you simply wanted one column in $result?

Also, basically paginate using LIMIT, will it hold THAT original (old) result even when the database is up-to-date?

The particulars are implementation dependent but generally speaking, answers are buffered. Performing a question against a database will return some result set. Whether it's sufficiently little all of the results might be came back using the initial call or some may be and much more answers are came back while you iterate within the result object.

Think about the succession by doing this:

  1. You open an association towards the database
  2. There's possibly another call to choose a database or it may be done included in (1)
  3. That authentication and connection step is (a minimum of) one round visit to the server (disregarding persistent connections)
  4. You perform query around the client
  5. That question is distributed towards the server
  6. The server needs to figure out how to complete the query
  7. When the server has formerly performed the query the execution plan can always maintain the query cache. Otherwise a brand new plan should be produced
  8. The server executes the query as given and returns an effect towards the client
  9. That result will contain some buffer of rows that's implementation dependent. It may be 100 rows or pretty much. All posts are came back for every row
  10. While you fetch more rows eventually the customer will request the server for additional rows. This might be once the client expires or it might be done preemptively. Again this really is implementation dependent.

The thought of all of this would be to minimize roundtrips towards the server without delivering back an excessive amount of unnecessary data, and that's why should you request for any million rows you will not have them all back at the same time.

LIMIT clauses--or any clause actually--will customize the result set.

Lastly, (7) is essential because SELECT * FROM table WHERE a = 'foo' and SELECT * FROM table WHERE a = 'bar' are two different queries so far as the database optimizer is worried so an execution plan should be determined for every individually. But a parameterized query (SELECT * FROM table WHERE a = :param) with various parameters is a query and just must be planned once (a minimum of until it falls from the query cache).

I believe you're confusing the two kinds of variables you are coping with, nor answer really clarifies that to date.

$result is really a MySQL result object. It doesn't "contain any rows." Whenever you say $result = mysql_query($sql), MySQL executes the query, and knows what rows will match, however the data is not moved to the PHP side. $result could be regarded as a pointer to some query that you simply requested MySQL to complete.

Whenever you say $row = mysql_fetch_object($result), this is when PHP's MySQL interface retrieves a row for you personally. That row is defined into $row (like a common PHP object, but use a different fetch function to request to have an associative array, or specific column(s) from each row.)

Rows might be buffered with the aspiration that you'll be locating all the rows inside a tight loop (which is often the situation), however in general, rows are retrieved whenever you request on their behalf and among the mysql_fetch_* functions.

Should you only have to have one column in the database, then you definitely should SELECT that_column FROM .... Utilizing a LIMIT clause is another wise decision whenever you can, because MySQL usually can perform significant optimizations whether it recognizes that you simply desire a certain number of rows.

The initial question could be clarified by reading through on resources

As you are Choosing "*", every column is came back for every mysql_fetch_object call. Just take a look at print_r($row) to determine.