Page 1 of 1

One database connection per query?

Posted: Sun Feb 22, 2009 1:06 pm
by samfinley
Hello,

is it a good idea to open a separate connection to the database for each query?

To elaborate my question: I am using PHP with MySQL. Some time ago, I wrote a class which takes care of the whole querying process for me. That is, I can instantiate the class passing a query to the constructor, and it then establishes a connection to the database, executes the query, closes the connection, and stores the result. It also contains the account details for the database as constants. So it is very convenient to use, I do not need to care about database connections anywhere else in my code.

As my PHP projects grew over time, I wondered if this is the best solution. When it comes to performance, it is probably better to keep one connection open for all queries. But I am not sure if this is really critical for performance. In terms of architecture, what would be the best option to handle a single connection for all queries? To establish the connection at the beginning of my scripts and make it a global variable? To hand it down as a parameter to the functions or objects which need it to execute queries?

Apart from that, are there any other reasons for either of these ways to handle connections? How are you organizing connections currently, and what is the state-of-the-art way to do it?

Thanks for your advice,
samfinley

Re: One database connection per query?

Posted: Sun Feb 22, 2009 5:55 pm
by josh
You can use persistent connections but I'd inject the connection object into the query object ( pass object as parameter ). I cant imagine this would be great for performance, I'd try to use 1 connection. Globals are evil

Re: One database connection per query?

Posted: Mon Feb 23, 2009 3:31 pm
by samfinley
Thanks for the advice.

Someone mentioned to me to use a singleton. This is a new concept for me, but I looked into it and it might just do what I want (1 database connection but no hassle with passing the connection as parameter) . What do you think of that solution?