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

[Jim Michaels]

I also hear it's slower (from another post) than using the direct mysql
functions.
If you want to build an array from the database, you could do this:

$a=array();
$q=mysql_query( "SELECT * FROM tablename", $link);
while($row=mysq l_fetch_array($ q)) {
$a[] = $row;
}
foreach ($a as $row) {
//do whatever with things like $row['item']
}
or you could use a SELECT to create a temporary table and grab your data
from that, and then destroy the temporary table. either way works, depends
on whether you need a snapshot or whether you are willing to work with live
rows. MySQL has row locking.

"Andy Hassall" <an**@andyh.co. uk> wrote in message
news:h3******** *************** *********@4ax.c om...

On 17 Jan 2006 11:34:37 -0800, sa********@gmai l.com wrote:

Rather than re-invent the wheel, look at:

http://adodb.sourceforge.net/
http://phplens.com/adodb/reference.f....getarray.html

Thanks for the answer.
My intention is not to use ADO db but make DB-accessing code simple and
avoid repeated codes (connecting/freeing/disconnecting db).
Is there a best practice of db-accessing in PHP?

You do realise that other than the name and a deliberate similarity in
function names, ADOdb has nothing to do with Microsoft ADO - it's just a
thin
PHP library providing the sort of access methods you're talking about on
top of
various PHP native database access functions?

IMHO, ADOdb _is_ the best practice of accessing databases in PHP.

--
Andy Hassall :: an**@andyh.co.u k :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[Jim Michaels]

"Jerry Stuckle" <js*******@attg lobal.net> wrote in message
news:Ib******** ************@co mcast.com...

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.

About those times when it's not - like when there's some other UPDATEr out
there on your rows that you just SELECTed (and you also want to do an
update, or an external UPDATE would really mess things up) - how can you
tell MySQL to lock those rows?


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
=============== ===

[Jerry Stuckle]

Jim Michaels wrote:

"Jerry Stuckle" <js*******@attg lobal.net> wrote in message
news:Ib******** ************@co mcast.com...

About those times when it's not - like when there's some other UPDATEr out
there on your rows that you just SELECTed (and you also want to do an
update, or an external UPDATE would really mess things up) - how can you
tell MySQL to lock those rows?

Jim,

I never said it was ALWAYS a good idea to do this. Rather, I argued
with the statement it is NEVER a good idea. Two entirely different things.

However, since you asked, this happens very often on heavy transaction
systems. It's quite easy.

You buffer the list. If you need to update an item, you fetch THAT ITEM
only a second time. Compare the contents of the just retrieved item to
the saved item. If there's no change, you can update it with impunity.

If there is a change, look at what's changed. If it's a field unrelated
to your current change, go ahead and update it. If it's a field which
will also be changed, you need to make an intelligent decision as to
whether you can update it or need to notify the user of an error.

Take a bank account for example. You get online and wish to move $100
from checking to savings. Meanwhile, your wife is going shopping and
tries to take $50 out of the account at an ATM.

You make the request. The system fetches your current balance and
ensures it is > $100. If it is, it displays a screen asking for you to
confirm this request (if you only have $10 in there, of course it denies
the request).

Now - your internet connection may go down, you may step away a minute
to pour yourself a cup of coffee, whatever. The point is, the system is
waiting for user response. Your account cannot be locked for that
period of time; other actions would be held up (like your wife getting
money or a check being cleared).

Let's say right now your wife makes that $50 withdrawal. Now you tell
the computer to complete your transaction.

But wait - there's less money in your account than when you started. If
the system just subtracted $100 from your earlier request, the $50
withdrawal would be lost (you'd be happy but your bank wouldn't!).

So the program again fetches your balance and compares it to the
original value. But wait - it's changed.

So the system recomputes the balance. If there are sufficient funds in
the account, it processes the withdrawal. But if there aren't, it sends
you an error message.

This is generally how almost all transactional systems work on big
systems. Yes, it's quite a bit more work. But it allows updating
without having to hold locks while someone goes for coffee.

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

[R. Rajesh Jeba Anbiah]

[top-post fixed. Please don't top-post]
Jim Michaels wrote:

"Andy Hassall" <an**@andyh.co. uk> wrote in message
news:h3******** *************** *********@4ax.c om...

On 17 Jan 2006 11:34:37 -0800, sa********@gmai l.com wrote:

Rather than re-invent the wheel, look at:

http://adodb.sourceforge.net/
http://phplens.com/adodb/reference.f....getarray.html

<snip> IMHO, ADOdb _is_ the best practice of accessing databases in PHP.

I also hear it's slower (from another post) than using the direct mysql
functions.

It's slower because of hard coded stuffs at PHP end. But, it's no
surprise that the abstraction would be slower than native functions.
IMHO, it's always better to move to abstractions 'coz switching to
other DB will be very easy if you use abstraction.

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

[Geoff Berrow]

Message-ID: <cu************ ********@comcas t.com> from Jerry Stuckle
contained the following:

So the system recomputes the balance. If there are sufficient funds in
the account, it processes the withdrawal.

Isn't a lock required for the very short period of time in between
recomputing the balance and processing the withdrawal?

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Jerry Stuckle]

Geoff Berrow wrote:

Message-ID: <cu************ ********@comcas t.com> from Jerry Stuckle
contained the following:

So the system recomputes the balance. If there are sufficient funds in
the account, it processes the withdrawal.

Isn't a lock required for the very short period of time in between
recomputing the balance and processing the withdrawal?

Yes, but that's a very short time - on a mainframe you're talking
microseconds; milliseconds at the worst.

You can never eliminate all locks. The secret is to minimize the number
and length of the locks.

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

[Iván Sánchez Ortega]

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

Jerry Stuckle wrote:

You can never eliminate all locks. The secret is to minimize the number
and length of the locks.

That is a concurrent programming technique; I know it as "make the critical
section as small and fast as possible".

(A critical section is the code that runs with the locks "on", and never
runs in paralell to another critical section)

Make sure the critical section has a very small complexity (usually O(1) ),
make sure there are no deadlocks, rely on listeners and events instead of
active waiting, and you should be fine.

- --
- ----------------------------------
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)

iD8DBQFD1Sxn3jc Q2mg3Pc8RAhJCAJ 9cfP5EuPS1wy6rH wOZacuKrYMTlACe NJBK
zKYT7bqw+irlbJo XWlkaQzs=
=XAH6
-----END PGP SIGNATURE-----

[Jerry Stuckle]

Iván Sánchez Ortega wrote:

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

Jerry Stuckle wrote:

You can never eliminate all locks. The secret is to minimize the number
and length of the locks.

That is a concurrent programming technique; I know it as "make the critical
section as small and fast as possible".

(A critical section is the code that runs with the locks "on", and never
runs in paralell to another critical section)

Make sure the critical section has a very small complexity (usually O(1) ),
make sure there are no deadlocks, rely on listeners and events instead of
active waiting, and you should be fine.

No, don't relay on listeners or events, either. Don't wait on anything
you don't absolutely have to. For instance - you may need to wait for
another database operation to finish (i.e. updating two tables).

But never turn control over to someone else (i.e. wait for an event).
What happens if an event gets lost?
- --
- ----------------------------------
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)

iD8DBQFD1Sxn3jc Q2mg3Pc8RAhJCAJ 9cfP5EuPS1wy6rH wOZacuKrYMTlACe NJBK
zKYT7bqw+irlbJo XWlkaQzs=
=XAH6
-----END PGP SIGNATURE-----

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

[Iván Sánchez Ortega]

Jerry Stuckle wrote:

No, don't relay on listeners or events, either. Don't wait on anything
you don't absolutely have to. For instance - you may need to wait for
another database operation to finish (i.e. updating two tables).

But never turn control over to someone else (i.e. wait for an event).
What happens if an event gets lost?

Well, I'm supposing here that the locking mechanism is good enough to not
let that happen...

However, what I really meant was "never ever rely on active waiting to do
important concurrent stuff".

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

Ofrecer mucho, especie es de negar.

[Jerry Stuckle]

Iván Sánchez Ortega wrote:

Jerry Stuckle wrote:

No, don't relay on listeners or events, either. Don't wait on anything
you don't absolutely have to. For instance - you may need to wait for
another database operation to finish (i.e. updating two tables).

But never turn control over to someone else (i.e. wait for an event).
What happens if an event gets lost?

Well, I'm supposing here that the locking mechanism is good enough to not
let that happen...

However, what I really meant was "never ever rely on active waiting to do
important concurrent stuff".

Locking in a database is controlled by the application. When the
application issues either a COMMIT or ROLLBACK, all locks are released.

But in the meantime, locks are held. Some databases have a lock timeout
setting. But if the timeout occurs, the only solution the database can
take is to internally issue a ROLLBACK.

In the meantime, any apps needing access to the locked resource may have
to wait (depending on a lot of things such as lock type, request type,
etc.). This could be a relatively long time (i.e. 30 sec.).

Rather, the secret to the high transaction systems is to only hold locks
for the shortest possible time. Gather all possible information you
require ahead of time. Get the locks, update the data and COMMIT.

And never, never, never wait for users while holding locks!

That's how the airlines, banks and other high-transaction systems keep
the throughput up.
--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===