Connect and close connection to database

Hi,

I have a question about connecting to the myslq database.

I now only do for easy page make a connection.
I never close a connection.
So far i didnt run into any problem.

What are the problems you can expect if you do not use the command to close connection and if you do not do this will php do it automaticaly for you? or not

It will close itself at the end for you.

Php manual says:
“The link to the server will be closed as soon as the execution of the script ends, unless it’s closed earlier by explicitly calling mysql_close().” –php.net

Allright, so it closes automaticly. No need to use the command as you might use the connection later again in your code.

That’s why on every page you have to reuse the code mysql_connect. The connection is terminated each time. But if you were needing to close it earlier than the end of the script you could. Also, if the script was a long loading one (like if you pulled information out of a database to add to a video file, or mp3 file) and the page took a long time to open, but the connection really only needed to be open for the first part you could close it early. That might have seemed confusing so I’ll illustrate it below :slight_smile:

//connect to database
mysql_connect…

//get user name

//render audio file with user name
//30 second process

You may not want to keep that database connection open that whole time. Audio work can take awhile. Or even complicated graphic edits. In this example, I don’t need the connection to the database open any longer after I grab the user name. So I could close it there. The benefit being that, if during that 30 seconds of rendering, 100 more people come to visit the site, I’m not blocking database access by keeping all these connections open when I know I’m not going to move anymore information back or forth.

Even if I were to update the database at the end of the script - I could easily close the connection at the top, and reopen it later. But keep in account too, your database restrictions of your host and the resources required to open and close connections. It requires more work for a database to open and close connections. So you wouldn’t do something like this:

//connect to database
//get username and age

//close database
//add 1 to the age

//open database connection
//update age
//close database connection

Much more resource intensive (and slow!) than to just do the calculation and update the table.

It’s bad practice to depend on PHP’s garbage collector to clean up your code. A connection to a database should be closed immediately after it is no longer needed and all memory associated with it released back to the server. Aside from just good practice there is a very real performance hit to the server where when you deal with more and more concurrent connections which all have open database connections eating up resources but not doing anything useful while it just waits for the script to terminate. That memory and resources need to be released back to the server asap so that other connections are not effected by the fact that you are dedicating memory to holding open a connection that is not needed simply because you prefer to have PHP clean up after you.

If the concern is that “you dont’ know if you will need the connection again” then I would suggest taking a look at how your application is structured, clearly there is room for improvement.

Ok thanks, thenn i understand its wise to just close the connection yourself when your done.

Most scripts run so quickly that the connection is closed again almost immediately by the script ending without your even needing to add the close command explicitly.

Only if your script is going to run for several milliseconds and all the database accesses are at the start would it actually make any significant difference to manually close the connection after the last database call rather than letting the termination of the script close it for you.

The only scripts you should ever have that would run long enough to need to manually run the close would be run on a scheduled basis rather than as a response to visitors to your page. Any scripts generating web pages should run so quickly that the script ends almost before the database realises that the connection has been opened.

I agree that when you’re dealing with low volumes of users then its less of an issue. The problem is akin to having a hole in your car’s gas tank that is about 3/4 of the way up the tank from the bottom. You dont’ realize you have a problem unless you put more then 3/4 of a tank of gas in it. But when you do…it becomes a pretty high priority issue quickly. The issue really isn’t about how quickly a script runs, it’s about how many instances of that script can be processed at the same time.

To simplify (really simplify) it, lets assume you have a server that has 100k of memory total, and a script that require 1k to run to completion that means you can serve 100 users at the same time. The time it takes for that script to run is irrelevant when you have 101 users hit that script in unison. Your server simply runs out of resources and drops requests ( there are ways to handle this but it is beyond the scope of this thread ). Believe me when I say that when your organization plops down a 6 figure number for a single box they become very interested in how many requests it can handle for their investment. If you are decreasing the value of the investment because of sloppy/lazy coding practices…it’s simply unprofessional. It would be very silly to sit in a meeting with the bean counters and tell them that a server that should handle 50,000 concurrent users can only handle 40,000 because you don’t feel the need to close a connection. Believe me, 20% drop in efficiency for a pricey box will get you in a lot of trouble, especially when it can make you more vulnerable to a DDOS attack.

Again this is an over simplification of the issue, but you get the idea. Better to start good coding practices now, before you get to the point where you are dealing with high volumes of traffic.

Activeseven, while all good and what not. But PHP reuses the same mysql connection for every script instance. So it becomes a none issue with higher volumes. Even with 50,000 simatanious instance of the script running, it only uses the one mysql connection.

Without totally derailing this thread I will say “depends on how the server and infrastructure are setup”. Is it possible that you will have 50,000 concurrent users with 50,000 distinct connections…yes! Is it likely? No! I feel that I stated that it was over simplification enough times in my original post.

My original statement still stands however, close all database connection and unset any variables the second they no longer serve a purpose.

Then you’ll spend more time cleaning then doing anything useful. There is no reason to go about cleaning and closing (files, networking functions are an exception) everything when PHP does all of that for you the moment your script is finished. These micro-optimizations you are talking about are not even true problems that have any benefit to being optimized. Unsetting variables the second you don’t need them saves nothing, there is more overhead in unsetting them.

Logic I believe that you’ve missed my general point and have gotten lost in the specifics, for that I apologize. There really isn’t anything you can say that will convince me that proper resource management isn’t a good practice for young coders to develop early in their career, irregardless of what language your using. So we will have to agree to disagree.

Sure, resource management is important for a language the requires manual resource management like C. However, PHP is not C and does not have any resource management outside of opening files or sockets (Even then those will be cleaned up as well). PHP itself handles all the resource management behind the scenes. What you are describing is a kin to cleaning the house before the maid arrives.

Is it good practice to close your connection? Sure. Do you need to do it? No, not really. Do I do it? No, not really.
The only time I would close a database connection during the execution of a script is if I was completely done with the first and wanted to open a second to a different server. Have never run across such and instance, though.

While I agree that PHP is unique in some ways as opposed to other languages I still disagree with the premise of your statement. Each PHP script is allowed x amount of memory to perform its intended purpose as can be seen in the memory_limit setting. The entire reason reason developers are given tools like [URL=“http://us2.php.net/manual/en/function.memory-get-peak-usage.php”]memory_get_peak_usage and [URL=“http://us2.php.net/manual/en/function.memory-get-usage.php”]memory_get_usage is so that they can monitor this at run-time.

Whether we like it or not, ever php script will have a memory limit imposed on it ( unless of course it’s set to -1, but that is unlikely ) and it is responsibility of the developer to work within those confines. Not unsetting variables after they are no longer needed serves no other purpose except to bring you closer to this memory limit and is imo sloppy programming, even in PHP. But I really feel that statements like this:

I feel are very misleading. Is there an overhead associated with performing an unset? Yes. But whether or not its of value is related to the contents of said variable. Unsetting a 50mb variable obviously has more of an impact then unsetting a 1k one. But these kind of blanket statements are dangerous.

I acknowledge the fact that PHP has a garbage collector ( a maid ) but it’s been my experience that maids only serve to make you more of a slob. I also acknowledge the fact that this isn’t something most developers here have to worry about but my statement is about establishing good habits now, not later.

It is my hope, that more developers acknowledge that memory is a shared resource on the server and should be used responsibly. I’m not really sure what it is your trying to state here, are you saying that me advising programmers to develop good coding habits by observing memory management is a bad thing?

and that’s all I’m saying :slight_smile:

Yep and it most cases the best way to close your connections is to exit your script. Worrying about closing your database connections when you are actually done with them is akin to worrying about using “” vs ‘’ in strings. Fun to debate but not applicable to 99.999% of the scripts out there.