Hi,
I'm kind of... well am... a beginner to php and mysql although I've had some experience programming and other various computer related fields.
Right now I'm trying to utilize both and was wondering if someone might clear a few things up for me.
In this code (I've added the line numbers for reference):
01 $result = mysql_query("SELECT id, name FROM mytable");
02
03 while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
04 printf ("ID: %s Name: %s", $row["id"], $row["name"]);
05 }
06 mysql_free_result($result);
In line 01 I understand that the database query's mytable and returns the columns id and name for all rows and sets $result to it.
What I'm a little unsure of is exactly how line 03 works. Basically I understand that mysql_fetch_array() checks to see if there is a row existing in from $result if so it takes that row and set's an array called $row to it if it doesn't it returns false and the while loop ends.
I don't, however, understand exactly how mysql _fetch_array traverses $result. At a basic level I see that it pulls the first row out, then moves to the next one, and so on and so forth, but what exactly is going on here? How does it determine what row it is currently on or is it modifying $result by removing the top row?
Is $result simply a multidimensional array or is something else going on here?
Also while it's on my mind (and can't check the obvious at the moment) is there a way to traverse through $result in such an multidimensional manner? eg. $result(1,3)
I've been able to program ok without knowing these answers so far, but I'd like to know exactly what kind of objects and functions I'm dealing with and how they work so I can avoid future problems and code more efficiently.
Thanks
Traversing a query result - mysql_fetch_array etc.
Moderator: General Moderators
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
Code: Select all
while ($row = mysql_fetch_assoc($result)) {Basically on SELECTs, SHOWs, EXPLAINs or DESCRIBEs, mysql_query() returns a result identifier that you can grab the top row of by using mysql_fetch_assoc() (or mysql_fetch_row(), or mysql_fetch_array() depending on what you want). By putting this into a while loop each row of the result set is gone through in turn until there aren't any left. On each iteration a pointer is probably moved to the next row down.
Although $row is an array $result isn't, as stated before it's a result identifier which means that you can't access it as a multidimensional array. You can grab an individual cell from a result set by using mysql_result() but it's not as efficient as the mysql_fetch_xxx() functions.
It's also generally not necessary to use mysql_free_result() as the memory will automatically be freed at the end of the script's execution.
Mac
My biggest mistake is and was that I sometimes do things in php that could be done (relatively easily) in sql. Sql is quite powerful after you get around it and certainly is faster than php, so what ever you do my suggestion would be that do everything that you can in sql statements (functions,views, queries etc.) and rest in php. Ofcourse both extremes should be avoided. ie. only take clean, organized information only as much as you need from your db.
You might also want to take a gander at this, as it might help you.
http://www.phpcomplete.com/content.php?id=3
http://www.phpcomplete.com/content.php?id=3
cool
Thanks for the info every one.
Though I'm still kind of curious about the structure of each function and object.
Does anyone have a good source for descriptions of data types and functions? (somthing that explains, how they do what they do)
If not, thanks for your help! (and when I have a chance and the inclination, I'll browse through the source, where it's avaliable)
Though I'm still kind of curious about the structure of each function and object.
Does anyone have a good source for descriptions of data types and functions? (somthing that explains, how they do what they do)
If not, thanks for your help! (and when I have a chance and the inclination, I'll browse through the source, where it's avaliable)
The value returned from mysql_query() is just a resource which acts like a pointer to the actual data. You cannot do anything with it except pass it on to other MySQL functions like mysq_fetch_array(). To actually retrieve the data you must call a mysql_fetch_*() function. You can retrieve the result buffered or unbuffered depending on your specific needs. Normally you would choose buffered since this is more flexible, but if you have queries that return large amount of data use unbuffered queries instead. There are different ways to retrieve data from a resource, but all receive data row by row. If you want an associative array (i.e. "column_name" => "the data") use mysql_fetch_assoc() and if you want an indexed array use mysql_fetch_row(). If you want both use mysql_fetch_array(). Personally I always use mysql_fetch_array() but treat the result as an associative array, I don't know why (a bad habit?). So, to summarize:
mysql_query() – returns resource to buffered result.
mysql_unbuffered_query() – returns resource to unbuffered result.
mysql_fetch_assoc() – returns row as an associative array (key => value).
mysql_fetch_row() – returns row as an indexed array ($my_array[0...n] where n is the number of columns minus one).
mysql_fetch_array() – returns row as both an associative and an indexed array.
If you don't know how many columns you fetch (SELECT * FROM table) or you don't know the name of some or all columns, you can use foreach() to traverse through the fetched row like this:
In this case it's not a good idea to use mysql_fetch_array() since all columns are represented by both a key and an index which results in the data being displayed twice.
/josa
mysql_query() – returns resource to buffered result.
mysql_unbuffered_query() – returns resource to unbuffered result.
mysql_fetch_assoc() – returns row as an associative array (key => value).
mysql_fetch_row() – returns row as an indexed array ($my_array[0...n] where n is the number of columns minus one).
mysql_fetch_array() – returns row as both an associative and an indexed array.
If you don't know how many columns you fetch (SELECT * FROM table) or you don't know the name of some or all columns, you can use foreach() to traverse through the fetched row like this:
Code: Select all
while($row = mysql_fetch_assoc($result)) {
foreach($row as $column => $data) {
echo $column . ": " . $data . "<br>\n";
}
echo "<br>\n";
}/josa