MySQL and SESSIONs

Hi all,

is it somehow possible to store the connection reference obtained from mysql_connect() (note the absence of the "i") in a $_SESSION element?

--
Stefano Esposito


--
Email.it, the professional e-mail, gratis per te: http://www.email.it/f

Sponsor:
Problemi di Liquidità? Con Logos Finanziaria 30.000 € in 24 ore a dipendenti e lavoratori autonomi con rimborsi fino a 120 mesi clicca qui
*
Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=2907&d=5-10

Stefano Esposito wrote:
> is it somehow possible to store the connection reference obtained from mysql_connect() (note the absence of the "i") in a $_SESSION element?

No. Why would you want to? You'd end up holding on to a database
connection even when nothing is using it. If you want to optimise things
look at http://php.net/mysql_pconnect but bear in mind that this starts
to suck if you scale up to multiple web servers.

-Stut

--
http://stut.net/

Stut
What's good for multiple webservers? thanks

-----Original Message-----
From: Stut [mailto:stuttle@gmail.com]
Sent: Friday, October 05, 2007 4:31 PM
To: Stefano Esposito
Cc: php-general@lists.php.net
Subject: Re: [php] MySQL and SESSIONs


Stefano Esposito wrote:
> is it somehow possible to store the connection reference obtained from mysql_connect() (note the absence of the "i") in a $_SESSION element?

No. Why would you want to? You'd end up holding on to a database
connection even when nothing is using it. If you want to optimise things
look at http://php.net/mysql_pconnect but bear in mind that this starts
to suck if you scale up to multiple web servers.

-Stut

--
http://stut.net/

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Vo, Lance wrote:
> What's good for multiple webservers? thanks

* DO NOT USE PERSISTANT CONNECTIONS

* Minimise the amount of time you keep a database connection open during
a request. Good logic/presentation separation helps a lot here.

* Cache the crap out of everything - don't hit the DB unless you really
need to.

-Stut

--
http://stut.net/

> -----Original Message-----
> From: Stut [mailto:stuttle@gmail.com]
> Sent: Friday, October 05, 2007 4:31 PM
> To: Stefano Esposito
> Cc: php-general@lists.php.net
> Subject: Re: [php] MySQL and SESSIONs
>
>
> Stefano Esposito wrote:
>> is it somehow possible to store the connection reference obtained from mysql_connect() (note the absence of the "i") in a $_SESSION element?
>
> No. Why would you want to? You'd end up holding on to a database
> connection even when nothing is using it. If you want to optimise things
> look at http://php.net/mysql_pconnect but bear in mind that this starts
> to suck if you scale up to multiple web servers.
>
> -Stut
>

On 10/5/07, Stut <stuttle@gmail.com> wrote:
>
> Vo, Lance wrote:
> > What's good for multiple webservers? thanks
>
> * DO NOT USE PERSISTANT CONNECTIONS
>
> * Minimise the amount of time you keep a database connection open during
> a request. Good logic/presentation separation helps a lot here.
>
> * Cache the crap out of everything - don't hit the DB unless you really
> need to.
>
> -Stut
>
> --
> http://stut.net/
>
> > -----Original Message-----
> > From: Stut [mailto:stuttle@gmail.com]
> > Sent: Friday, October 05, 2007 4:31 PM
> > To: Stefano Esposito
> > Cc: php-general@lists.php.net
> > Subject: Re: [php] MySQL and SESSIONs
> >
> >
> > Stefano Esposito wrote:
> >> is it somehow possible to store the connection reference obtained from
> mysql_connect() (note the absence of the "i") in a $_SESSION element?
> >
> > No. Why would you want to? You'd end up holding on to a database
> > connection even when nothing is using it. If you want to optimise things
> > look at http://php.net/mysql_pconnect but bear in mind that this starts
> > to suck if you scale up to multiple web servers.
> >
> > -Stut



Hi. This may not be best practice, but I think it's great to use -
especially since I can use multiple functions with the same database
connection w/o having to send the db link/resource. Store the connection in
the GLOBALS variable - this way, it's accessible by each function AND is
destroyed when the script completes (e.g., when the processing is complete
for the current page).

connect.php:
$_GLOBALS['dbLink'] = mysql_connect (DATABASE, USER, PASS);
....

functions.php:
function someFunction () {
....
$result = mysql_query ($query, $_GLOBALS['dbLink']);
....
// Don't close the connection
}

somePage.php:
$something = someFunction();
....
mysql_close ($_GLOBALS['dbLink']); // End of page - close connection

That's the general gist of it. BTW, if this is bad practice or there is
something more efficient, please let me know. =P

~Philip

On 10/8/07, Philip Thompson <philthathril@gmail.com> wrote:
> Hi. This may not be best practice, but I think it's great to use -
> especially since I can use multiple functions with the same database
> connection w/o having to send the db link/resource. Store the connection in
> the GLOBALS variable - this way, it's accessible by each function AND is
> destroyed when the script completes (e.g., when the processing is complete
> for the current page).
>
>

> connect.php:
> $_GLOBALS['dbLink'] = mysql_connect (DATABASE, USER, PASS);
> ...
>
> functions.php:
> function someFunction () {
>    ....
>    $result = mysql_query ($query, $_GLOBALS['dbLink']);
>    ....
>    // Don't close the connection
> }
>
> somePage.php:
> $something = someFunction();
> ...
> mysql_close ($_GLOBALS['dbLink']); // End of page - close connection
>

>
> That's the general gist of it. BTW, if this is bad practice or there is
> something more efficient, please let me know. =P
>
> ~Philip
>

I do not use persistent connections, nor store resources in session
variables. I do the same thing with a global variable.

However, I don't automatically open a db link. I only open it if a db
function is requested. I check for the presence of $GLOBALS['dbh'], if
it exists, then I have an existing connection. If not, then it will
make one.

It seems to perform extremely well. It also guarantees only one
connection per page *(per datasource, I just added in support for
multiple) *if needed*, and is very little PHP code. All the DB
routines I need are under 75 lines of code (indented/nested and even a
small error handling function) - there are no other routines I need. I
don't even remember the specific mysqli_* functions anymore, because I
never call them directly.

I haven't really had to change it besides for adding the multiple
datasources in years, and it was seamless to change from mysql to
mysqli, etc.

I did run in to a performance issue though, I was having ~ 60 clients
waiting for data on my mysql server all the time. Turns out it was
100% the database - a few key tables were being slammed with reads and
writes. Changed them to InnoDB and now I can do a "show processlist"
and usually don't see any active threads - each page loads, connects
to the database if needed (which all pages do) and does all the work
so quickly I can't even catch it with my own eyes. It's amazing. At
one point it was doing over 1,000 queries per second over a 60 day
average - and it's just a dual-core processor with 4 gig of ram and a
single SATA disk.

Simplicity is always best!