Best Practise?

[blueman378]

hi guys, well me and my mate are having a debate and i need your help to win it so:

 

what is the best practise when it comes to mysql,

 

have a file database.php which contains the mysql database/username/password info ect and also opens the connection and include that on every page, and dont clsoe the connection,

 

or every time you need to run a mysql query enter then information and close the connection as soon as the query is finished?

 

 

please state your answer and reasons why

[haku]

There are some good comments on the topic on php.net. Not to say we shouldn't discuss it here as well though!

[Daniel0]

In my opinion, neither, but if I must choose one then it would be the former.

 

I would create a number of classes for dealing with the database and pass the object around.

[blueman378]

ok thanks for your opinion, although how necessary is it to close your connections, i mean with something like google its obvious that there would be rather a need for it however, when it comes to small sites is it really necessary?

[Daniel0]

You only need to call the close function if you need to close the connection before the execution of the script ends. It's automatically called at the end of script execution. I didn't check, but I think the manual says it as well.

[blueman378]

Thats what i thought but ive been told that closing it as soon as the query is finished can save server load?

[dbo]

While the connection does get closed when the script stops executing, I think it's a poor practice to expect this to happen. I guess for me it's sorta like the whole magic_quotes thing. Just because the server can attempt to escape poor data... doesn't mean it's doing it right.

 

But yes, neither would be my answer. I would definitely create a class to manage it and pass the object around. One reason is for simple abstraction. Say you're using the native mysql functions and suddenly they change to mysqli... whoops! Now you have 15,000 occurences of mysql that have to be replaced in 65 different files. That's the suck. If you manage this through a class you make that change in one location. There are lots of reasons that make this method beneficial, this is just one example.

[Xeoncross]

 

Build a $DB object like dbo and blueman378 said - you'll love it. Or download one of the 20+ already out there.

 

Second, closing a DB connection hurts your performance - a lot! Try to do it as little as possible. (like <= 1 times)

[dbo]

Yes database connections are expensive. I'm not suggesting that you open/close any more than you have to, but you should still be closing the connection when it's no longer needed.

[Xeoncross]

 

Personally, I recommend persistent MySQL connections for simple scripts - or those that only have to deal with the same server/db. If your next page request is going to use the same database; there is no need to close the connection at all...