By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,399 Members | 1,653 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,399 IT Pros & Developers. It's quick & easy.

Dumb Question... mysql_fetch_array()

P: n/a
Hey there

I have a dumb question....

Let's say i have a database full of 4000 people.... I select everything
from the database by:

$result = mysql_query("SELECT * FROM People");
My question is about the results that I get... if I use:

while ($row = mysql_fetch_array($result)) {
//do stuff here
}

after "//do stuff here" is done (and let's say its a LOT of stuff), and the
next row of data is fetched, is the row of data coming from the database,
or is it coming from the php variable $result?

basically, since i'm doing a LOT of things to each row (ie: sending them
each an email), i was wondering if the following code was helpful/pointless
....

$x = 1;
while ($row = mysql_fetch_array($result)) {
$Email[$x] = $row['Email'];
$Name[$x] = $row['Name'];
$x++;
}

... and THEN do my things by iterating through an array

foreach ($Email as $key=>$value) {
/* do stuff here (send $Name[$key] a letter to her email address of
$value) */
}

Is this totally redundant? I guess it all comes down to whether or not
mysql_fetch_array is going back to the database....

Basically I'm attempting to avoid any chance of losing a mysql connection
because the "//do stuff here" is taking so long....

Thanks in advance!
Jul 17 '05 #1
Share this Question
Share on Google+
15 Replies


P: n/a
Good Man wrote:
Let's say i have a database full of 4000 people.... I select
everything from the database by:

$result = mysql_query("SELECT * FROM People");

My question is about the results that I get... if I use:

while ($row = mysql_fetch_array($result)) {
//do stuff here
}

after "//do stuff here" is done (and let's say its a LOT of stuff),
and the next row of data is fetched, is the row of data coming from
the database, or is it coming from the php variable $result?
Each call to mysql_fetch_array() will transfer a record from mysql to
php. All that $result contains is a resource identifier to the
resultset in mysql. You use that resource identifier in subsequent
calls to functions such as mysql_fetch_array() so the php code knows
which resultset to draw its data from.
basically, since i'm doing a LOT of things to each row (ie: sending
them each an email), i was wondering if the following code was
helpful/pointless ...

$x = 1;
while ($row = mysql_fetch_array($result)) {
$Email[$x] = $row['Email'];
$Name[$x] = $row['Name'];
$x++;
}
If you were to do this then you'd want to call
mysql_free_result($result) at then end to flush the resultset from the
database server. It's not really going to make too much of a difference
whichever way you do it but here's another approach so you don't need
to increment a loop counter:

$records = array();
while ($row = mysql_fetch_array($result)) {
$records[] = $row;
}

Some database servers will let you grab the whole resultset in one chunk
(eg I know you can do it with asp and mssql server) and when it's
possible to do it this way it's great as there's much less overhead
required in multiple calls for getting data.
.. and THEN do my things by iterating through an array

foreach ($Email as $key=>$value) {
/* do stuff here (send $Name[$key] a letter to her email address of
$value) */
}
With my alternative example you could then do:

foreach($records as $row) {

}
Is this totally redundant? I guess it all comes down to whether or
not mysql_fetch_array is going back to the database....

Basically I'm attempting to avoid any chance of losing a mysql
connection because the "//do stuff here" is taking so long....


This *is* a good reason to grab all the data if your processing of it is
going to take some time. You probably want to do the following:

1. Connect to the databatase
2. Run query and loop data into an array
3. Disconnect from the database yourself
4. Process the array

--
Chris Hope | www.electrictoolbox.com | www.linuxcdmall.com
Jul 17 '05 #2

P: n/a
Chris Hope <bl*******@electrictoolbox.com> wrote in news:d5efti$4sb$1
@lust.ihug.co.nz:
This *is* a good reason to grab all the data if your processing of it is
going to take some time. You probably want to do the following:

1. Connect to the databatase
2. Run query and loop data into an array
3. Disconnect from the database yourself
4. Process the array


Thanks very much for your detailed and informative answer!
Jul 17 '05 #3

P: n/a
> My question is about the results that I get... if I use:

while ($row = mysql_fetch_array($result)) {
//do stuff here
}

after "//do stuff here" is done (and let's say its a LOT of stuff), and the
next row of data is fetched, is the row of data coming from the database,
or is it coming from the php variable $result?
As far as I know, neither. PHP stores internally all the output from the
database query and $result is a pointer to that data. Function
mysql_fetch_array() fills an array with one row of this data. Actually, it
creates two elements per field, because it returns an associative array and
a regular array, check it with print_r().
$x = 1;
while ($row = mysql_fetch_array($result)) {
$Email[$x] = $row['Email'];
$Name[$x] = $row['Name'];
$x++;
}

.. and THEN do my things by iterating through an array

foreach ($Email as $key=>$value) {
/* do stuff here (send $Name[$key] a letter to her email address of
$value) */
}
I'd say this is a waste of memory. You create two arrays that contain all
the rows, while apparently you only need one row at a time.
Basically I'm attempting to avoid any chance of losing a mysql connection
because the "//do stuff here" is taking so long....


It's always a good idea to optimize queries:

* Read only the fields you need (not all of them).
* Use LIMIT if applicable.
* If using WHERE clauses, create indexes for the searched fields.
--
-- Álvaro G. Vicario - Burgos, Spain
-- http://bits.demogracia.com - Mi sitio sobre programación web
-- Don't e-mail me your questions, post them to the group
--
Jul 17 '05 #4

P: n/a
> As far as I know, neither. PHP stores internally all the output from the
database query and $result is a pointer to that data. Function
I agree, it is an overload to duplicate the $result set in another table.
mysql_fetch_array() fills an array with one row of this data. Actually, it
creates two elements per field, because it returns an associative array and a regular array, check it with print_r().


Yes, and it is one element too many !
There is a second parameter which you can add to mysql_fetch_array():
mysql_fetch_array($result, MYSQL_ASSOC) = mysql_fetch_assoc($result)
mysql_fetch_array($result, MYSQL_NUM)
mysql_fetch_array($result, MYSQL_BOTH)

Jul 17 '05 #5

P: n/a
Alvaro G Vicario <al******************@telecomputeronline.com> wrote in
news:wi*****************************@40tude.net:

after "//do stuff here" is done (and let's say its a LOT of stuff),
and the next row of data is fetched, is the row of data coming from
the database, or is it coming from the php variable $result?


As far as I know, neither. PHP stores internally all the output from
the database query and $result is a pointer to that data. Function
mysql_fetch_array() fills an array with one row of this data.
Actually, it creates two elements per field, because it returns an
associative array and a regular array, check it with print_r().


Actually, I believe you are wrong. It seems the first reply to this
post, by Chris Hope, is correct:

"Each call to mysql_fetch_array() will transfer a record from mysql to
php. All that $result contains is a resource identifier to the
resultset in mysql."

....so php *does* actually connect to the DB with each mysql_fetch_array
().
.. and THEN do my things by iterating through an array

foreach ($Email as $key=>$value) {
/* do stuff here (send $Name[$key] a letter to her email address
of
$value) */
}


I'd say this is a waste of memory. You create two arrays that contain
all the rows, while apparently you only need one row at a time.


As above, it turns out that while it might *use* more memory, it is not a
waste because my database connection is over and done with by this point,
and I can do whatever I want to each row of data, regardless of execution
time, and not worry about losing my database connection/the next row of
data. I've modified my array generation to Chris Hope's suggestion:

while ($row = mysql_fetch_array($result)) {
$records[] = $row;
}
Basically I'm attempting to avoid any chance of losing a mysql
connection because the "//do stuff here" is taking so long....


It's always a good idea to optimize queries:

* Read only the fields you need (not all of them).
* Use LIMIT if applicable.
* If using WHERE clauses, create indexes for the searched fields.


My question was really about database connections... there is nothing
wrong with my query, I *need* all the field and rows that I've called...
it's just that there is a significant amount of processing that must be
done with each row of data, and I wanted to do that processing without
losing my DB connection/next row of data.

Chris Hope's suggestions worked wonderfully.

Thanks for your help though! Cheers!



Jul 17 '05 #6

P: n/a
*** Good Man wrote/escribió (Fri, 06 May 2005 09:22:24 -0500):
Actually, I believe you are wrong. It seems the first reply to this
post, by Chris Hope, is correct:

"Each call to mysql_fetch_array() will transfer a record from mysql to
php. All that $result contains is a resource identifier to the
resultset in mysql."

...so php *does* actually connect to the DB with each mysql_fetch_array
().


In the end I decided to test myself. I wrote a script that connects to DB,
executes a query, sleeps for 30 seconds and then prints rows. I launch it
and then stop MySQL daemon. After 30 seconds the script fails. It doesn't
connect every time a row is retrieved, it just keeps a connection open (the
SHOW PROCESSLIST command shows it). But it's definitively MySQL's task to
cache the result of the query.

My excuses, I always try to apologize when I give wrong info.
--
-- Álvaro G. Vicario - Burgos, Spain
-- http://bits.demogracia.com - Mi sitio sobre programación web
-- Don't e-mail me your questions, post them to the group
--
Jul 17 '05 #7

P: n/a
Alvaro G Vicario <al******************@telecomputeronline.com> wrote in
news:bj*****************************@40tude.net:

In the end I decided to test myself. I wrote a script that connects to
DB, executes a query, sleeps for 30 seconds and then prints rows. I
launch it and then stop MySQL daemon. After 30 seconds the script
fails. It doesn't connect every time a row is retrieved, it just keeps
a connection open (the SHOW PROCESSLIST command shows it). But it's
definitively MySQL's task to cache the result of the query.

My excuses, I always try to apologize when I give wrong info.


thanks for you well-intentioned help, i wasn't trying to flame you or
anything, i just wanted to make sure you were 'in the know' for the future.

again, thanks for your posts.
Jul 17 '05 #8

P: n/a
*** Good Man wrote/escribió (Fri, 06 May 2005 09:52:05 -0500):
thanks for you well-intentioned help, i wasn't trying to flame you or
anything, i just wanted to make sure you were 'in the know' for the future.


Don't worry, it never gave me that impression.

--
-- Álvaro G. Vicario - Burgos, Spain
-- http://bits.demogracia.com - Mi sitio sobre programación web
-- Don't e-mail me your questions, post them to the group
--
Jul 17 '05 #9

P: n/a
> Actually, I believe you are wrong. It seems the first reply to this
post, by Chris Hope, is correct:


WELL, I DON'T AGREE.
I just tried this code :

include_once('setup.php');
$db = my_connect();
$query = "SELECT * FROM crdp_users";
$result = mysql_query($query) or die(mysql_error());
mysql_close(); // NO CONNECTION ANY MORE
while ($row = mysql_fetch_assoc($result)) {
echo $row['login']."<br>";
}

When I fetch the data from $result, I do not have any connection to the
mysql server. The resultset is stored in a PHP variable. Don't confuse
closing the connection and freeing the resultset : mysql_close(); and
mysql_free_result($result);

It is therefore not a good idea to duplicate the resultset putting the data
in an array.

Jul 17 '05 #10

P: n/a
*** BLob wrote/escribió (Fri, 6 May 2005 16:58:17 +0200):
When I fetch the data from $result, I do not have any connection to the
mysql server.


You don't have an open connection you can use to send queries, but you do
have a resource identifier that is a sort of link to MySQL server. Try my
own method: add sleep(30) just after mysql_close() to give you time to stop
the MySQL daemon.
--
-- Álvaro G. Vicario - Burgos, Spain
-- http://bits.demogracia.com - Mi sitio sobre programación web
-- Don't e-mail me your questions, post them to the group
--
Jul 17 '05 #11

P: n/a
BLob wrote:
Actually, I believe you are wrong. It seems the first reply to this
post, by Chris Hope, is correct:
WELL, I DON'T AGREE.
I just tried this code :

include_once('setup.php');
$db = my_connect();
$query = "SELECT * FROM crdp_users";
$result = mysql_query($query) or die(mysql_error());
mysql_close(); // NO CONNECTION ANY MORE
while ($row = mysql_fetch_assoc($result)) {
echo $row['login']."<br>";
}

When I fetch the data from $result, I do not have any connection to
the mysql server. The resultset is stored in a PHP variable.


The resultset is *not* stored in a PHP variable. It is cached in MySQL
and the data is retrived one row at a time using the mysql_fetch_row()
mysql_fetch_array() mysql_fetch_assoc() or mysql_fetch_object()
functions.

Just because you run a query on a database does not mean you are
necessarily going to fetch all the rows, some of the rows or even any
rows at all, so it would be presumptuous of PHP to decide to fetch this
data from the database automatically, especially if your dataset
contains thousands or millions of records.
Don't
confuse closing the connection and freeing the resultset :
mysql_close(); and mysql_free_result($result);
What's happening in your code is that the connection to MySQL is
maintained while your resource (ie the resource defined by your
variable $result) still exists.
It is therefore not a good idea to duplicate the resultset putting the
data in an array.


The resultset is not being duplicated in PHP by caching it into an
array. It *is* being duplicated in both MySQL and PHP, but if you call
mysql_free_result() then it clears the resultset cache in MySQL.

--
Chris Hope | www.electrictoolbox.com | www.linuxcdmall.com
Jul 17 '05 #12

P: n/a
"Good Man" <he***@letsgo.com> wrote in message
news:Xn************************@216.196.97.131...
Alvaro G Vicario <al******************@telecomputeronline.com> wrote in
news:wi*****************************@40tude.net:

after "//do stuff here" is done (and let's say its a LOT of stuff),
and the next row of data is fetched, is the row of data coming from
the database, or is it coming from the php variable $result?
As far as I know, neither. PHP stores internally all the output from
the database query and $result is a pointer to that data. Function
mysql_fetch_array() fills an array with one row of this data.
Actually, it creates two elements per field, because it returns an
associative array and a regular array, check it with print_r().


Actually, I believe you are wrong. It seems the first reply to this
post, by Chris Hope, is correct:

"Each call to mysql_fetch_array() will transfer a record from mysql to
php. All that $result contains is a resource identifier to the
resultset in mysql."

...so php *does* actually connect to the DB with each mysql_fetch_array
().


Actually, I don't agree... that would mean that internally, PHP is
submitting the query to MySQL every time you call 'mysql_fetch_array()'. To
make the point, when you submit the query you should see a certain amount of
disk activity/time lag (depending on your db size - especially since you are
requesting everything), but the code where you actually request the
results - 'mysql_fetch_array()' should run very quickly. To prove this,
simply echo some 'date/time' info before and after the query and at the
start of each loop through the result set.

Your time lag may be very small as you are only accessing 4000 rows, try a
test table of 100,000 rows...

Norm
--
FREE Avatar hosting at www.easyavatar.com

.. and THEN do my things by iterating through an array

foreach ($Email as $key=>$value) {
/* do stuff here (send $Name[$key] a letter to her email address
of
$value) */
}


I'd say this is a waste of memory. You create two arrays that contain
all the rows, while apparently you only need one row at a time.


As above, it turns out that while it might *use* more memory, it is not a
waste because my database connection is over and done with by this point,
and I can do whatever I want to each row of data, regardless of execution
time, and not worry about losing my database connection/the next row of
data. I've modified my array generation to Chris Hope's suggestion:

while ($row = mysql_fetch_array($result)) {
$records[] = $row;
}
Basically I'm attempting to avoid any chance of losing a mysql
connection because the "//do stuff here" is taking so long....


It's always a good idea to optimize queries:

* Read only the fields you need (not all of them).
* Use LIMIT if applicable.
* If using WHERE clauses, create indexes for the searched fields.


My question was really about database connections... there is nothing
wrong with my query, I *need* all the field and rows that I've called...
it's just that there is a significant amount of processing that must be
done with each row of data, and I wanted to do that processing without
losing my DB connection/next row of data.

Chris Hope's suggestions worked wonderfully.

Thanks for your help though! Cheers!


Jul 17 '05 #13

P: n/a
Alvaro G Vicario wrote:
*** Good Man wrote/escribió (Fri, 06 May 2005 09:22:24 -0500):
Actually, I believe you are wrong. It seems the first reply to this
post, by Chris Hope, is correct:

"Each call to mysql_fetch_array() will transfer a record from mysql
to php. All that $result contains is a resource identifier to the
resultset in mysql."

...so php *does* actually connect to the DB with each
mysql_fetch_array ().


In the end I decided to test myself. I wrote a script that connects to
DB, executes a query, sleeps for 30 seconds and then prints rows. I
launch it and then stop MySQL daemon. After 30 seconds the script
fails. It doesn't connect every time a row is retrieved, it just keeps
a connection open (the SHOW PROCESSLIST command shows it). But it's
definitively MySQL's task to cache the result of the query.


I just did this test too and the script kept working, so I am not so
sure now about my previous post where I said the data is buffered in
MySQL. It looks like PHP simply grabs all the data right after you've
run the query, which is really not what I expected or thought. I do
still find it odd if this is the default behaviour.

Anyway, this is what I tested:

$res = mysql_query('select foo from bar');
while($row = mysql_fetch_array($res)) {
sleep(1); // sleep for one second
if($x = mysql_error()) {
print "$x\n";
exit;
}
else {
print_r($row);
}
}

Running SHOW PROCESSLIST shows this running as expected. However, it
doesn't actually show that anything is happening. So I modified my code
to work like so:

$res = mysql_query('select foo from bar');
mysql_close();
while($row = mysql_fetch_array($res)) {
sleep(1); // sleep for one second
if($x = mysql_error()) {
print "$x\n";
exit;
}
else {
print_r($row);
}
}

Now that I have closed the connection directly after running it, I can
still fetch the data and it no longer shows up when running SHOW
PROCESSLIST.

Note that in both tests I shut down the mysql server while it was
retrieving data. In both tests it continued to output data and did not
return any errors.

Are you sure your script erroring out at 30 seconds is not because of
the default time limit of 30 seconds for PHP scripts?

--
Chris Hope | www.electrictoolbox.com | www.linuxcdmall.com
Jul 17 '05 #14

P: n/a
Norman Peelman wrote:


Actually, I don't agree... that would mean that internally, PHP is
submitting the query to MySQL every time you call 'mysql_fetch_array()'. To
make the point, when you submit the query you should see a certain amount of
disk activity/time lag (depending on your db size - especially since you are
requesting everything), but the code where you actually request the
results - 'mysql_fetch_array()' should run very quickly. To prove this,
simply echo some 'date/time' info before and after the query and at the
start of each loop through the result set.

Your time lag may be very small as you are only accessing 4000 rows, try a
test table of 100,000 rows...

Norm


Norm,

Actually, he's right.

You do not need to resubmit the query to MySQL. When you call
mysql_fetch_...(), you pass a $result. This is a MySQL construct, not a
PHP one. It points to internal information in MySQL regarding the
request, including the (already parsed) query, the current position in
the result set and other things. Not having to reparse the query and
rebuild all this information makes the following fetch requests fast.
Additionally, it may have already been in memory due to look-ahead
buffering - so disk access would not be required. And even if it wasn't
already in the buffers, it would be a very quick hit to the disk to
fetch it - no searching for files or anything.

Think about this: what if each row had, say, 10K of data. The search
returns 10K rows. That would be 100M of data (more or less) that PHP
would have to cache. But it doesn't.

Check out the C API's in the MySQL reference manual. That's where this
all ends up anyway. And you will see basically the same functions in C
- which, as a (fairly) low-level language, buffer any of the MySQL
data. All the calls go directly the MySQL.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Jul 17 '05 #15

P: n/a
*** Chris Hope wrote/escribió (Sun, 08 May 2005 07:40:38 +1200):
$res = mysql_query('select foo from bar');
while($row = mysql_fetch_array($res)) {
sleep(1); // sleep for one second
if($x = mysql_error()) {
print "$x\n";
exit;
}
else {
print_r($row);
}
}


Here's mine:

<?

$con=mysql_connect('localhost', 'test', 'test');
mysql_select_db('test', $con);
$res=mysql_query('select * from data', $con) or die(mysql_error());
mysql_close($con);

echo 'Sleeping... Please stop MySQL daemon now<br>';
sleep(30);

if($res){
while($row=mysql_fetch_assoc($res)){
var_dump($row);
echo "<br>";
}
}else{
echo '<br>Resource not available';
}

echo '<br>Finished';

?>
I can't understand anything... On Friday it didn't work, now it does: it
prints all lines. The script is exactly the same. I suppose the failure was
somewhere else.......
--
-- Álvaro G. Vicario - Burgos, Spain
-- http://bits.demogracia.com - Mi sitio sobre programación web
-- Don't e-mail me your questions, post them to the group
--
Jul 17 '05 #16

This discussion thread is closed

Replies have been disabled for this discussion.