0

Can someone explain why do we need to do java: while rs.next() , php: while ($result = mysql_fetch_array) ? How does programming languages retrieve data from database. What happens ? Why doesn't reveice the whole response ?

I don't know but I really can't understant this , is way to nasty .

I need a documentation not just "because they do that"

PS :

$result = mysql_query($query) won't return the query results to the $result ? and then : while ($row = mysql_fetch_array($result)) { do stuff } ??

Thanks

pufos
  • 2,890
  • 8
  • 34
  • 38
  • 1
    "Why doesn't reveice the whole response ?" Eeeeeeeeek. Performance maybe? – PeeHaa Mar 28 '12 at 14:23
  • But the question is how does it do it ? How is php/java telling database to retrieve one row at a time ? – pufos Mar 28 '12 at 14:24
  • 1
    Sorry I misread. I thought I saw a why instead of a how. Anyway: http://lxr.php.net/search?q=mysql_fetch_array&project=PHP_5_4&defs=&refs=&path=&hist= – PeeHaa Mar 28 '12 at 14:26
  • Or nowadays also [on GitHub](https://github.com/php/php-src/tree/master/ext/mysql). – PeeHaa Mar 28 '12 at 14:30
  • the `$result = mysql_query($query)` won;t fill the `$result` with all the rows returned ?? – pufos Mar 28 '12 at 14:34
  • I have linked the PHP source so you can checkout how it is done. – PeeHaa Mar 28 '12 at 14:40
  • I saw that but do you expect me to really understand what's happening in that 4000 lines of code ? – pufos Mar 28 '12 at 14:44
  • Well you want to understand how they do it right? – PeeHaa Mar 28 '12 at 15:21
  • Also ircmaxell and nikic are writing posts about how to use the source: http://blog.ircmaxell.com/2012/03/phps-source-code-for-php-developers.html – PeeHaa Mar 28 '12 at 15:22

3 Answers3

1

It looks like that particular approach uses a reader. The reader approach has some advantages over just bringing the entire resultset into memory at the same time:

  • You can sometimes start processing before the query is complete
  • You can usually start processing before transmitting all of the result set.
  • You don't have to keep the large dataset in memory all at the same time (could be a big performance issue if you don't need to have all of the data at once).
  • It's a pretty standard, well understood approach to getting data from a database.
RQDQ
  • 15,461
  • 2
  • 32
  • 59
1

Because they do that.
Or, if you like it - how it works.

In our life, sometimes, we just can't get the whole response.
Say, if you want to fill a barrel from the well, you have to fill it by means of the emptying the bucket several times. There is just no other way. And pump won't make it much different - you have to wait anyway, you can't get the "whole response" at once. Due to the stream nature of the source.

The only thing you can do is to disguise the whole process with some higher-level substitution, which will do the same process inside. You can buy a filled barrel from someone who care to fill it for you.

Database reads the rows from the table one by one. You can't get the "whole response". But you can write (or use) a function, which will do all the filling inside and give you desired response.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • but the $result = mysql_query($query) won;t fill the $result array with all the rows returned ?? – pufos Mar 28 '12 at 14:33
  • you can write a function. which will do it for you. See http://stackoverflow.com/a/9888225/285587 for the example which will bring you desired result – Your Common Sense Mar 28 '12 at 14:49
  • It's not about writing functions do to that .. is about how it works .. how it fetch only one row to $result ? what mysql_query returns actually ?] – pufos Mar 28 '12 at 14:51
  • it IS about writing functions. just the level whre it could be done. You are asking for the API level. You can have it, but don't fool yourself - there will be the same loop inside. – Your Common Sense Mar 28 '12 at 14:53
  • mysql_query doesn't directly return any result. It basically returns something like an iterator into the results, which you then use with mysql_fetch_array/assoc/etc. – KernelM Mar 28 '12 at 14:53
  • Ok, and how is used that iterator with mysql_fetch_array ? What is mysql_fetch_array do with that iterator ? – pufos Mar 28 '12 at 14:55
  • @pufos again just go through the [php source](http://lxr.php.net/search?q=mysql_fetch_array&project=PHP_5_4&defs=&refs=&path=&hist=). – PeeHaa Mar 28 '12 at 15:18
0

At the basic level most databases are always returning results row by row. It's a natural way to retrieve data, and not loading everything at once means that, for example, you can process a million row result one row at a time without using excessive memory (assuming it's something that's amenable to being processed one row at a time). If you want, there's plenty of database wrapper libraries out there that will have variations on query returns that already have everything inside the variable. Or write such a wrapper function yourself, it's pretty simple. In the past I had a variety of wrapper functions that would put everything into a multidimensional array (with optional array indexing), just grab one row, just grab one field, etc.

KernelM
  • 8,776
  • 2
  • 23
  • 16
  • man , i understand that but `$result = mysql_query($query)` won't return the query results to the $result ? and then while ($row = mysql_fetch_array($result)) { } ?? – pufos Mar 28 '12 at 14:43