please explain how fetching (eg mysql_fetch_row) works

hello-world · #1 (**permalink**) 01-22-12, 05:17

If I do:

Code:

$result = mysql_query("SELECT id FROM $table WHERE age=20");
$row = mysql_fetch_row($result);
$row = mysql_fetch_row($result);

This will select the the first two IDs of users who are 20 years old. And if I add more $row=... then it will keep fetching the IDs until no more rows are left.

My question is how does this actually work -- is the variable "$result" automatically being diminished by one row each time mysql_fetch_row is called? I'm just a bit confused because I'm not manually modifying $result. Is this behaviour due to the $result variable being a special "resource" variable?

healdem · #2 (**permalink**) 01-22-12, 09:43

as per PHP: mysql_query - Manual

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

Quote:

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

The returned result resource should be passed to mysql_fetch_array(), and other functions for dealing with result tables, to access the returned data.

$result is a variable that refers to whatever is returned by the call to the database object.
you'd iterate through the result to retrieve all the values (rows) of say a select.

usually that takes the form or

PHP Code:


			
while ($row=mysql_fetch_array($sqlr))
{  //do somethign with the row retrieved from the db
    //  eg echo $row['columnname'];
}

hello-world · #3 (**permalink**) 01-22-12, 20:33

Quote:

Originally Posted by healdem

$result is a variable that refers to whatever is returned by the call to the database object.
you'd iterate through the result to retrieve all the values (rows) of say a select.

usually that takes the form or

PHP Code:


			
while ($row=mysql_fetch_array($sqlr))

{  //do somethign with the row retrieved from the db

    //  eg echo $row['columnname'];

}

Yeah that's what I'm wondering: every time you do mysql_fetch_array, you iterate through the array and get the next row, which seems like fewer rows remain in sqlr. So it appears that $sqlr, the variable containing the SQL query results, is itself modified by the fetch command -- I mean, how else can $row get the next row each time? Is that how it works?

healdem · #4 (**permalink**) 01-23-12, 03:26

as you are returning an associative array from the SQL query you should be able to reset the array pointer. retrieving the data from the array doesn't remove it, but merely moves an internal pointer

PHP: Array Functions - Manual

Internet Services

Web Development

Digital Marketing

Consumer Tech