Is this a good idea?

[sam.s.kong]

Hi!

I've been programming ASP for 5 years and am now learning PHP.
In ASP, you can use GetRows function which returns 2 by 2 array of
Recordset.
Actually, it's a recommended way in ASP when you access DB as it
disconnects the DB earlier.
Also, it's handy as you can directly access any data in the array
without looping.

As far as I know, there's no such function in PHP and I can make one.
My question is whether it's good in PHP.

pseudo-code:

$data = get_data("selec t * from table1");
$var = $data[3][2]; //value at 4th row, 3rd column

This way, I can wrap db connection, data retrieval, and error handling
with one function (or maybe a class).
Is the idea workable?

TIA.
Sam

[Iván Sánchez Ortega]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

sa********@gmai l.com wrote:

[...]

If I just let each page loop each record, some pages might forget
releasing db resources.
And? In PHP, if you let a file descriptor open, ther garbage collector will
gracefully close it when the script ends or gets killed for whatever
reason. If you let any other resource open (say, a database connection), it
will be gracefully closed when the program ends, whenever the developer
explicitly closes it or not.
And I feel uncomfortable with the idea that db is opened at the top of
a page and closed somewhere else far from the top.

In most cases, the DB connection is opened the first time a user hits a
page, and is not closed until some time after the user has hit a page.
Persistent connections save a lot of overhead, and are a lot preferable
than opening a DB conn, querying it, and closing it several times. A lot.

- --
- ----------------------------------
Iván Sánchez Ortega -i-punto-sanchez--arroba-mirame-punto-net

http://acm.asoc.fi.upm.es/~mr/
Proudly running Debian Linux with 2.6.12-1-686 kernel, KDE3.5.0, and PHP
5.1.1-1 generating this signature.
Uptime: 19:51:59 up 8 min, 1 user, load average: 0.59, 1.04, 0.64

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQFDzo8j3jc Q2mg3Pc8RAq/VAJ9MGLTzxgAhd3 LX38lOsduRbaL3Y ACcDQ+a
C0J+ttOUT2zpQmG ILWfIyX4=
=bxAi
-----END PGP SIGNATURE-----

[sam.s.kong]

Iván Sánchez Ortega wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Chung Leong wrote:

R. Rajesh Jeba Anbiah wrote:

As many people have pointed out, *never* dump the table data into
array. Fetch the record and immediately get that processed. If you have
any *valid* reason, buffer the data into a very very small (known)
sized array. If using MySQL, use the LIMIT if possible.
The reasoning being?

Code refactoring, for example.

As you may already know, refactoring is almost always a good idea, as it
reduces complexity of the algorithm, procesing time, and increases the
cache hit ratio, to name a few consecuences.

Let's suppose the following example:

<?php

mysql_pconnect( blahblahblah);
$r = mysql_query(bla hblahblah);
$db_results = array();
while ($row = mysql_fetch_arr ay($r))
{
$db_results[] = $row;
}

foreach ($db_results as $row)
{
foobar;
}

?>

Well, let's refactor that code:
<?php

mysql_pconnect( blahblahblah);
$r = mysql_query(bla hblahblah);
while ($row = mysql_fetch_arr ay($r))
{
foobar;
}

?>
Less complexity, less CPU time, less memory, less code. Any developer that
has been taught anything about algorithms knows that. You'd better have a
good reason to not refactorize your code in this way.

Well, the example is not good for the issue here.
The following code will be encapsulated.
mysql_pconnect( blahblahblah);
$r = mysql_query(bla hblahblah);
$db_results = array();
while ($row = mysql_fetch_arr ay($r))
{
$db_results[] = $row;
}

So when the practical code will be:

$result = get_result("sel ect * from table1" [, "myDB"]);
for($result as $row){
foobar();
}

Of course, it has overhead, but that's more modular and most modularity
comes with costs.

Now I understand your point and I think dumping db data to an array
should be carefully used.

Thanks.
Sam

[disappear.nz]

Iván Sánchez Ortega wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

sa********@gmai l.com wrote:

[...]

If I just let each page loop each record, some pages might forget
releasing db resources.

And? In PHP, if you let a file descriptor open, ther garbage collector will
gracefully close it when the script ends or gets killed for whatever
reason. If you let any other resource open (say, a database connection), it
will be gracefully closed when the program ends, whenever the developer
explicitly closes it or not.

And I feel uncomfortable with the idea that db is opened at the top of
a page and closed somewhere else far from the top.

In most cases, the DB connection is opened the first time a user hits a
page, and is not closed until some time after the user has hit a page.
Persistent connections save a lot of overhead, and are a lot preferable
than opening a DB conn, querying it, and closing it several times. A lot.

- --
- ----------------------------------
Iván Sánchez Ortega -i-punto-sanchez--arroba-mirame-punto-net

http://acm.asoc.fi.upm.es/~mr/
Proudly running Debian Linux with 2.6.12-1-686 kernel, KDE3.5.0, and PHP
5.1.1-1 generating this signature.
Uptime: 19:51:59 up 8 min, 1 user, load average: 0.59, 1.04, 0.64

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQFDzo8j3jc Q2mg3Pc8RAq/VAJ9MGLTzxgAhd3 LX38lOsduRbaL3Y ACcDQ+a
C0J+ttOUT2zpQmG ILWfIyX4=
=bxAi
-----END PGP SIGNATURE-----

[disappear.nz]

Persistant Connections:
The connection to the SQL server will not be closed when the execution
of the script ends. Instead, the link will remain open for future use
(mysql_close() will not close links established by mysql_pconnect( )).

If you have alot of concurrent users on the site mysql_pconnect can
cause major resource issues.

The database library that is in discussion does not close the
connection after each query ie fetch_array.

The only time the mysql connection is closed is when either the object
is removed or the execution of the page terminates.

Most of the data-type returned from a mysql recordset field is string,
Building large arrays using string data types doesn't use alot of
memory nor impact performance really.
Also if you design your SQL statements correctly and efficiently you
shouldn't have this issue anyway.

I've since written a more advanced database library that uses a
singleton pattern,
the singleton pattern is useful when you are requiring only once
instance of that object across multiple objects.

I've found these librarys extermely efficient in programming and
performance,
You can view examples at.

http://sliterous.no-ip.org/php-libs/

[disappear.nz]

Persistant Connections:
The connection to the SQL server will not be closed when the execution
of the script ends. Instead, the link will remain open for future use
(mysql_close() will not close links established by mysql_pconnect( )).

If you have alot of concurrent users on the site mysql_pconnect can
cause major resource issues.

The database library that is in discussion does not close the
connection after each query ie fetch_array.

The only time the mysql connection is closed is when either the object
is removed or the execution of the page terminates.

Most of the data-type returned from a mysql recordset field is string,
Building large arrays using string data types doesn't use alot of
memory nor impact performance really.
Also if you design your SQL statements correctly and efficiently you
shouldn't have this issue anyway.

I've since written a more advanced database library that uses a
singleton pattern,
the singleton pattern is useful when you are requiring only one
instance of that object across multiple objects.

I've found these librarys extermely efficient in programming and
performance,
You can view examples at.

http://sliterous.no-ip.org/php-libs/

[Iván Sánchez Ortega]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Jerry Stuckle wrote:

And it's *never* a good idea to give absolutes :-).
And I've told you ten million times not to exaggerate :-P
There are times when it's better to dump a table into an array - like
when you have a lot of processing to do on multiple items and want to
release mysql resources.
Batch jobs are not the most usual thing to do in PHP...
Also collection classes for abstracting the data. And that's just the
beginning.

Such a class should not relay on a complete table dump to work: it should
load data dinamically (and/or cache some of it) in order to improve
performance. And it should use the SPL functionality, to provide a clean
way to dinamically iterate over the data set.

- --
- ----------------------------------
Iván Sánchez Ortega -i-punto-sanchez--arroba-mirame-punto-net

Un ordenador no es un televisor ni un microondas, es una herramienta
compleja.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQFDzpKo3jc Q2mg3Pc8RAr/wAJ9qbK5EaFOB02 hGp6sdCvFHFaIsa wCeJddU
jlJ+IaoAfDDtk2R zId7DAuw=
=/byC
-----END PGP SIGNATURE-----

[Iván Sánchez Ortega]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

sa********@gmai l.com wrote:

Of course, it has overhead, but that's more modular and most modularity
comes with costs.

Yep, and it's up to you to decide if you want modularity or performance in
this case.

Or both, using a carefully planned SPL module :-)

- --
- ----------------------------------
Iván Sánchez Ortega -i-punto-sanchez--arroba-mirame-punto-net

http://acm.asoc.fi.upm.es/~mr/
Proudly running Debian Linux with 2.6.12-1-686 kernel, KDE3.5.0, and PHP
5.1.1-1 generating this signature.
Uptime: 23:00:55 up 3:17, 1 user, load average: 0.34, 0.36, 0.39

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQFDzrrl3jc Q2mg3Pc8RAug9AK CA3bMfFq6vCWki6 TFDQlyqZ9aRjACe JPwT
Nmycw1uSIykNqa3 mwxqWWe4=
=Ha4u
-----END PGP SIGNATURE-----

[Jerry Stuckle]

Iván Sánchez Ortega wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Jerry Stuckle wrote:

And it's *never* a good idea to give absolutes :-).

And I've told you ten million times not to exaggerate :-P

There are times when it's better to dump a table into an array - like
when you have a lot of processing to do on multiple items and want to
release mysql resources.

Batch jobs are not the most usual thing to do in PHP...

Who said anything about batch jobs? I didn't.

Also collection classes for abstracting the data. And that's just the
beginning.

Such a class should not relay on a complete table dump to work: it should
load data dinamically (and/or cache some of it) in order to improve
performance. And it should use the SPL functionality, to provide a clean
way to dinamically iterate over the data set.

Who said anything about a complete table dump? I didn't.
- --
- ----------------------------------
Iván Sánchez Ortega -i-punto-sanchez--arroba-mirame-punto-net

Un ordenador no es un televisor ni un microondas, es una herramienta
compleja.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQFDzpKo3jc Q2mg3Pc8RAr/wAJ9qbK5EaFOB02 hGp6sdCvFHFaIsa wCeJddU
jlJ+IaoAfDDtk2R zId7DAuw=
=/byC
-----END PGP SIGNATURE-----

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===

[Chung Leong]

Iván Sánchez Ortega wrote:

-----BEGIN PGP SIGNED MESSAGE-----
As you may already know, refactoring is almost always a good idea, as it
reduces complexity of the algorithm, procesing time, and increases the
cache hit ratio, to name a few consecuences.
I don't know what refactoring has to do with this, but since you
mention it, I'll give you my 2 cents. In the real world, refactoring is
always a bad idea. By definition, you are not adding new
functionality--hence value to the product. You are thus wasting
programming and QA resource. Moreover, you risk introducting bugs into
what was working before. It's a lose-lose proposition.

As every good engineer knows, if it ain't broken, don't fix it.
Less complexity, less CPU time, less memory, less code. Any developer that
has been taught anything about algorithms knows that. You'd better have a
good reason to not refactorize your code in this way.
There are plenty of good reasons. Interleaving data retrieval and
processing in the manner you describes makes it hard to properly
modularize the code. You are also fixing the direction by which the
rows can be processed--the sorting order of the query--without the
possibility of looking ahead.
That's not a bad idea for batch jobs, but is a terrible one when you have
tenths, hundreds of hits per second. A few MB of memory per script may seem
a small issue, but think about a few MB per script, 100 scripts per second.
A "short time" is not a big thing, but a "short time" hundreds of times per
second is.

That's just unrealistic. When you retrieve data from the database, it
usually goes somewhere--i.e. to the client. In you scenario you'd have
a server that output multiple gigs per-second. I don't know about you
but I certainly don't have a peta-byte bandwidth quota.

[R. Rajesh Jeba Anbiah]

Chung Leong wrote:

R. Rajesh Jeba Anbiah wrote:

As many people have pointed out, *never* dump the table data into
array. Fetch the record and immediately get that processed. If you have
any *valid* reason, buffer the data into a very very small (known)
sized array. If using MySQL, use the LIMIT if possible.

The reasoning being?

In my opinion conserving memory for the sake of conserving memory is
just silly. Hardware resources are there to be used. There's nothing
wrong with a script using a few megs of extra memory, as it'll release
them a short time later.

Since, I know that Chung Leong is heading anti-performance campaign,
I'm not going to fight with him;-)

As you already know that by dumping huge bufferred records in to PHP
array, you're just duplicating the buffer. Also, as I mentioned
earlier, one can buffer the records in PHP provided the records are
very very less (for a valid reason).

FWIW, I have came across a PHP application that was manufactured in
another corner of the world. In which the programmer has bufferred the
whole user table into an array by mapping id and then he chose the
record like $record[$_GET['id']]. Not sure, the programmer isn't aware
of WHERE clause.

Also, try benchmarking the outcome by dumping the whole table (or at
least huge record sets) into an PHP array. If you use Windows/Apache
like me, you'd immediately see the result.

<OT>It's really nice to see new comers like Iván Sánchez Ortega
and the c.l.php discussions are now getting hotter:-)</OT>

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/