multiple db connections

kante

Hello,

I have a medium size web portal and I'd like to share this I guess common isssue:

having data stored in multiple databases,
(not really for a technical need but just for mental and design order)

may cause you a problem if in performing queries... just because u select the db in use and dont specify the link to the db on which you would exec the query.

What I found is to use a class and connect on every part of the php page i need to ask to MySQL a query perform the query and close the connection.
Instead of open the connections to the needed DBs at the top of the page and then query along the script referring to the correct DB link....

What do you think? Will it cause low performance opening and closing connection on each query or group of query in a page?

Actually it should not increase the number of connection.. apart some cases where i ask DB1 then DB2 and again to DB1... so i have one or two more...

Thanks a lot
I await for some smart comment..

bradgrafelman

Why would you need multiple connections?

Use only one connection, and instead specify the target DB names in your SQL queries.

kante

so you mean to say to perform each query

as SELECT * FROM dbname.dbtable

and not as SELECT * FROM dbtable with dbname previously selected?

bradgrafelman

Yep, that's about it. That just seems cleaner and a lot easier to manage IMHO.

NogDog

Also allows you to do joins, sub-queries, and unions across multiple DB's. 🙂

sneakyimp

I agree that separate DB's might not be appropriate for clean thinking, but I've encountered situations wherein one must use separate DBs due to permissions issues or integration with some third-party data. For this reason, I think it's a good idea if you always specify which db connection you are talking about. This is more easily handled when you define a db class because the class can keep track of which data base connection you are talking about with you having to type and retype it for every mysql or mysqli function you ever encounter.

Perhaps more importantly, wrapping your db access inside a class makes it easier to change from one DBMS to another. I.e., you can change all of your mysql functions to mysqli in one place rather than every single time you run a query. Or you might be able to switch from MySQL to PGSQL, depending on your queries and db design.