...

View Full Version : Proper way to do databases



bacterozoid
08-11-2008, 12:20 PM
I'd like to know what some of the proper methods are for using a database in a PHP application.

I'm thinking of things like when to connect, when to disconnect, how to properly perform queries, etc.

Overall, I wrote a script called databaseFunctions.php. I put all of my database calls in there. It does nothing more than perform queries and return the results.

The form of my queries is the following:


$sql = "QUERY TEXT HERE";
$result = mysql_query($sql)
or die("Invalid query: " . mysql_error());


Now, the web application itself is only one page: index.php. I manage content by using URL parameters to change content on the page. Related or not, how should I maintain a database connection?

Right now, before every query, I just connect to the database. I never sever a connection. Should I keep one open, should I open a new one for every query?

I'd like to be sure to do all of this right so it's easy to maintain.

Thanks!

_Aerospace_Eng_
08-11-2008, 12:33 PM
Open a connection at the beginning, close it at the end. Opening and closing a connection EACH time you run a query can cause a lot of overhead.

Mikroz
08-12-2008, 12:16 PM
If I remember correctly there is no need to close mysql connections anymore (if there is only queries with the same connection etc), aren't they closed automatically after PHP parses reaches the end of the file ?

Inigoesdr
08-13-2008, 12:22 AM
If I remember correctly there is no need to close mysql connections anymore (if there is only queries with the same connection etc), aren't they closed automatically after PHP parses reaches the end of the file ?
Yes, but that doesn't mean there isn't a reason to close them explicitly. For instance, if your script hangs for whatever reason the connection will be left open. Most of the time you'll be fine letting PHP close it, though.

Fou-Lu
08-13-2008, 01:21 AM
Yes, but that doesn't mean there isn't a reason to close them explicitly. For instance, if your script hangs for whatever reason the connection will be left open. Most of the time you'll be fine letting PHP close it, though.

Exactly right. Same goes for other resources like file handling. Just because PHP is supposed to close it for you, doesn't mean you should expect that it always will. I'm thinking of something like a deadlock between two php files with no max_execution_time. To stop them you'd have to reset your apache or IIS server, and I'm thinking the locks may still be there given that PHP was terminated unexpectedly. This is why you should always close you're resources, regardless of the language.
I'm with Aerospace, open once, close at the end. I generally write my storage objects to emulate other languages (like C#), where if the connection is currently closed, it opens, queries, and then closes. If its open, it uses the connection and leaves it open. On destruct, I have the object close itself if its still open. Nice, clean and easy. I recommend that open should be performed, but will still operate without it.

bacterozoid
08-13-2008, 01:44 AM
Makes sense to me. I'll be sure to correct my mistakes. Appreciate the response. :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum