Storing database connection in a session variable [duplicate]

Question

Possible Duplicate:
Can't pass mysqli connection in session in php

Many of us have written PHP applications that require databases; mostly MySQL, but I have often used very small MS Access databases for people less technically capable so they can download an tweak them/save backups/etc. on their own (whether this is correct or not, I have no idea).

What I notice, is a lot of time is spent connecting and running some of the same queries. Because of this I had an interesting thought: Storing the connection and possible result sets that are mostly static in a $_SESSION variable to reduce the burden as the user navigates the site.

Obviously doing so requires a lot of consideration. Things like closing the connection when the session is destroyed is just the start.

My question boils down to: Is this really something possible? And if so, what things should I be aware of (besides session fixation, as it is its own problem that applies to all sessions)?

Answer 1

You can't store database connections or result sets in the session, since those are resources, and:

Some types of data can not be serialized thus stored in sessions. It includes resource variables or objects with circular references (i.e. objects which passes a reference to itself to another object).

http://php.net/manual/en/intro.session.php

You can extract a result set into a normal array and store that in the session like any other variable. That would be a fairly typical use case for sessions anyway. Just be careful not to store too much data in the session, as that can become more taxing than fetching it from the database.

Answer 2

Even if you could do this (resource vs. data), this is a bad idea. You'll wind up with lots of concurrent open connections, which will blow your max connections very quickly... especially if its lifecycle is expanded beyond sub 100ms (depending on your queries) to 20 minutes or more. With open connections, something like MySQL also won't be able to reset its memory allocations properly, and the whole system sort of goes to hell. In short, this is not what DBs are for unless the only consumer of your code will be a single user.

As an alternative, I'd highly recommend caching technologies which are designed specifically to reduce database load and obviate connection times. Using something like, at its simplest, memcached will dramatically improve performance all the way around, and you'll be able to specify exactly how many system resources go into the cache -- while letting the database do its job of getting data when it needs to.