Is this a good idea?

[sam.s.kong@gmail.com]

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

[Andy Hassall]

Re: Is this a good idea?

On 17 Jan 2006 09:56:09 -0800, sam.s.kong@gmai l.com wrote:
[color=blue]
>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?[/color]

Rather than re-invent the wheel, look at:

http://adodb.sourceforge.net/

http://phplens.com/adodb/reference.functions.getarray.html

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

[Pedro Graca]

Re: Is this a good idea?

sam.s.kong@gmai l.com wrote:[color=blue]
> 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[/color]

You can easily access the returned resource as a kind of array with

$result = mysql_query("se lect * from table1");
$var = mysql_result($r esult, 3, 2);

If you choose to put all returned data into a "proper" array, don't
forget to

mysql_free_resu lt($result);

--
If you're posting through Google read <http://cfaj.freeshell. org/google>

[sam.s.kong@gmail.com]

Re: Is this a good idea?


Pedro Graca wrote:[color=blue]
> sam.s.kong@gmai l.com wrote:[color=green]
> > 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[/color]
>
> You can easily access the returned resource as a kind of array with
>
> $result = mysql_query("se lect * from table1");
> $var = mysql_result($r esult, 3, 2);
>
> If you choose to put all returned data into a "proper" array, don't
> forget to
>
> mysql_free_resu lt($result);[/color]

Thanks.
This is the answer I needed.
The reason I want to dump the recordset to an array is to avoid
cleaning up (disconnection) so that I can forget about db-related jobs.

function get_data($sql){
//connect to dbms
//select db
//select data and dump to an array
//close db (free db resource)
}

$data = get_data("selec t * from table1");

I'll make the function in a library file, and just call the function
and forget about connecting/disconnecting/freeing db resources.
One benefit is that I won't make mistake to forget about cleaning up.
Array will be automatically GCed, right?

To summarize, my goals are:
1. Put db-related routines in one function (or class) to avoid
repeating same codes.
2. Avoid mistakes like forgetting mysql_free_resu lt
3. You can improve the function later and it will affect everywhere.
4. Make db-accessing code simple. (Just throw an sql and get the
result)

What do you think?

Sam

[sam.s.kong@gmail.com]

Re: Is this a good idea?


Andy Hassall wrote:[color=blue]
> On 17 Jan 2006 09:56:09 -0800, sam.s.kong@gmai l.com wrote:
>[color=green]
> >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?[/color]
>
> Rather than re-invent the wheel, look at:
>
> http://adodb.sourceforge.net/
> http://phplens.com/adodb/reference.f....getarray.html[/color]

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?

Regards,
Sam

[sam.s.kong@gmail.com]

Re: Is this a good idea?


sam.s.kong@gmai l.com wrote:[color=blue]
> 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?
>[/color]

I found a code that suits my intention.

<?php

class mysql_array
{

public function __construct ( $s_host , $s_user , $s_pass ,
$s_db )
{
$this -> r_conn = mysql_connect ( $s_host , $s_user ,
$s_pass ) or die ( mysql_error ( ) ) ;
mysql_select_db ( $s_db ) ;
}

private function array_make ( $s_sql , $i_type )
{
$r_rs = mysql_query ( $s_sql , $this -> r_conn ) or die (
mysql_error ( ) ) ;
while ( $a_col = mysql_fetch_arr ay ( $r_rs , $i_type ) )
{
$a_rs [ ] = $a_col ;
}
mysql_free_resu lt ( $r_rs ) ;
return ( $a_rs ) ;
}

public function array_logic ( $s_sql )
{
$a_rs = $this -> array_make ( $s_sql , MYSQL_NUM ) ;
return ( $a_rs ) ;
}

public function array_assoc ( $s_sql )
{
$a_rs = $this -> array_make ( $s_sql , MYSQL_ASSOC ) ;
return ( $a_rs ) ;
}

public function array_both ( $s_sql )
{
$a_rs = $this -> array_make ( $s_sql , MYSQL_BOTH ) ;
return ( $a_rs ) ;
}

}

$o_mysql = new mysql_array ( 'localhost' , 'user' , 'pass' , 'db' )
;
$s_sql = "SHOW TABLES" ;
$a_rs = $o_mysql -> array_assoc ( $s_sql ) ;

echo '<pre>' ;
print_r ( $a_rs ) ;

?>

It's from http://us2.php.net/manual/en/ref.mysql.php .


Sam

[sam.s.kong@gmail.com]

Re: Is this a good idea?

> I found a code that suits my intention.[color=blue]
>
> <?php
>
> class mysql_array
> {
>
> public function __construct ( $s_host , $s_user , $s_pass ,
> $s_db )
> {
> $this -> r_conn = mysql_connect ( $s_host , $s_user ,
> $s_pass ) or die ( mysql_error ( ) ) ;
> mysql_select_db ( $s_db ) ;
> }
>
> private function array_make ( $s_sql , $i_type )
> {
> $r_rs = mysql_query ( $s_sql , $this -> r_conn ) or die (
> mysql_error ( ) ) ;
> while ( $a_col = mysql_fetch_arr ay ( $r_rs , $i_type ) )
> {
> $a_rs [ ] = $a_col ;
> }
> mysql_free_resu lt ( $r_rs ) ;
> return ( $a_rs ) ;
> }
>
> public function array_logic ( $s_sql )
> {
> $a_rs = $this -> array_make ( $s_sql , MYSQL_NUM ) ;
> return ( $a_rs ) ;
> }
>
> public function array_assoc ( $s_sql )
> {
> $a_rs = $this -> array_make ( $s_sql , MYSQL_ASSOC ) ;
> return ( $a_rs ) ;
> }
>
> public function array_both ( $s_sql )
> {
> $a_rs = $this -> array_make ( $s_sql , MYSQL_BOTH ) ;
> return ( $a_rs ) ;
> }
>
> }
>
> $o_mysql = new mysql_array ( 'localhost' , 'user' , 'pass' , 'db' )
> ;
> $s_sql = "SHOW TABLES" ;
> $a_rs = $o_mysql -> array_assoc ( $s_sql ) ;
>
> echo '<pre>' ;
> print_r ( $a_rs ) ;
>
> ?>
>
> It's from http://us2.php.net/manual/en/ref.mysql.php .[/color]

I forgot to ask "Do you think the above code is ok in terms of
performance and memory usage?"

Sam

[Peter Fox]

Re: Is this a good idea?

>To summarize, my goals are:[color=blue]
>1. Put db-related routines in one function (or class) to avoid
>repeating same codes.
>2. Avoid mistakes like forgetting mysql_free_resu lt
>3. You can improve the function later and it will affect everywhere.
>4. Make db-accessing code simple. (Just throw an sql and get the
>result)[/color]

Use my code if you like. Amongst other things a database object and
helper classes.

<http://www.eminent.dem on.co.uk/phplibrary.htm>

--
PETER FOX Not the same since the poster business went to the wall
peterfox@eminen t.demon.co.uk.n ot.this.bit.no. html
2 Tees Close, Witham, Essex.
Gravity beer in Essex <http://www.eminent.dem on.co.uk>

[sam.s.kong@gmail.com]

Re: Is this a good idea?


Peter Fox wrote:[color=blue][color=green]
> >To summarize, my goals are:
> >1. Put db-related routines in one function (or class) to avoid
> >repeating same codes.
> >2. Avoid mistakes like forgetting mysql_free_resu lt
> >3. You can improve the function later and it will affect everywhere.
> >4. Make db-accessing code simple. (Just throw an sql and get the
> >result)[/color]
>
> Use my code if you like. Amongst other things a database object and
> helper classes.
>
> <http://www.eminent.dem on.co.uk/phplibrary.htm>[/color]

Thank you so much.
I'll try that.

Sam

[Pedro Graca]

Re: Is this a good idea?

sam.s.kong@gmai l.com wrote:[color=blue]
> I found a code that suits my intention.
>
> <?php
>
> class mysql_array
> {
>
> public function __construct ( $s_host , $s_user , $s_pass ,
> $s_db )
> {
> $this -> r_conn = mysql_connect ( $s_host , $s_user ,
> $s_pass ) or die ( mysql_error ( ) ) ;
> mysql_select_db ( $s_db ) ;
> }
>
> private function array_make ( $s_sql , $i_type )
> {
> $r_rs = mysql_query ( $s_sql , $this -> r_conn ) or die (
> mysql_error ( ) ) ;[/color]
<snip>

You might want to replace the mysql_query call with
mysql_unbuffere d_query ... never tested it though, not sure if there is
any performance gain ...

--
If you're posting through Google read <http://cfaj.freeshell. org/google>

[Chung Leong]

Re: Is this a good idea?

sam.s.kong@gmai l.com wrote:[color=blue]
> 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?[/color]

It's a pretty normal practice. I do it all the time. I typically have a
number of functions, for retrieving a single row, multiple rows, a
single column, and a single value.

I believe that's also something that the MySQL manual recommends, to
alleviate lock issues.

[Andy Hassall]

Re: Is this a good idea?

On 17 Jan 2006 13:56:02 -0800, "Chung Leong" <chernyshevsky@ hotmail.com> wrote:
[color=blue]
>sam.s.kong@gma il.com wrote:[color=green]
>> 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?[/color]
>
>It's a pretty normal practice. I do it all the time. I typically have a
>number of functions, for retrieving a single row, multiple rows, a
>single column, and a single value.
>
>I believe that's also something that the MySQL manual recommends, to
>alleviate lock issues.[/color]

Isn't the default mode of operation for MySQL to send the entire result set
over to the client, then release locks, and only then actually make the results
available to the client application - so you can be as slow as you like reading
them from the client buffer, it doesn't matter?

If you're using unbuffered queries, _then_ it would matter how quickly you
consume the data, but you don't tend to see as many examples of that.

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

[Andy Hassall]

Re: Is this a good idea?

On 17 Jan 2006 11:34:37 -0800, sam.s.kong@gmai l.com wrote:
[color=blue][color=green]
>> Rather than re-invent the wheel, look at:
>>
>> http://adodb.sourceforge.net/
>> http://phplens.com/adodb/reference.f....getarray.html[/color]
>
>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?[/color]

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 :: andy@andyh.co.u k :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[Iván Sánchez Ortega]

Re: Is this a good idea?

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

sam.s.kong@gmai l.com wrote:
[color=blue]
> 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.[/color]
[...][color=blue]
> Is the idea workable?[/color]

Yes, it is possible.

No, it is not a good idea.

If you return all the data from a query to a 2-dimensional array, then you
will be putting a excessive overhead into your code: more variables, more
used memory, more wasted CPU cycles. If you have very large tables, you
will run out of memory, everything will start failing, and you won't know
the reason.

Carefully plan your SQL query so you don't get more results than expected.
Return the DB results row by row. Parse them one by one. Use persistent DB
connections. Don't ever work on the entire results of a query: it's a
complete waste of time, and your code will became more complex, and less
mainteable. Know what you're doing, experiment yourself, don't be blinded
by the "recommende d ways in ASP".

Also, it's the job of the DB engine to keep track of the query results, not
PHP's. And modern, stable DB engines do support multiple queries at once.
You just have to be a bit careful about concurrent programming.

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

http://acm.asoc.fi.upm.es/~mr/ ; http://acm.asoc.fi.upm.es/~ivan/
MSN:i_eat_s_p_a _m_for_breakfas t@hotmail.com
Jabber:ivansanc hez@jabber.org ; ivansanchez@kde talk.net
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQFDzYU13jc Q2mg3Pc8RAiWaAJ 4oR3uFj19oipF7F soU3yVWpYdpYACf V8PV
BCZoQyPEOl4+BdO x2tbyxSE=
=wGB6
-----END PGP SIGNATURE-----

[R. Rajesh Jeba Anbiah]

Re: Is this a good idea?

sam.s.kong@gmai l.com wrote:[color=blue]
> 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.[/color]
<snip>

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.

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