Leave database connection open throughout or close everytime?

Posted on 2009-04-27
Last Modified: 2012-06-21
If I'm going to make 2 or more database connections in one php page, should I connect in the beginning of the file and close it at the end, or open and close for each occurrence?
Question by:dsrnu
    LVL 14

    Expert Comment


    You can use the multiple connections in anywhere in the page you wants.

    No need to close the connection after each one.

    Please note that you sholud connect with the different databases with different connection objects.

    LVL 14

    Expert Comment


     mysql_close() closes the non-persistent connection to the MySQL server that's associated with the specified link identifier. If link_identifier isn't specified, the last opened link is used.

    Using mysql_close() isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution
    LVL 5

    Accepted Solution


    just to make it clear, when a php script finishes it closes the connections to the db that it opened (most of the time, there are exceptions).

    The right thing to do is to open DB connections only when you need them AND reuse the connection variable for further queries. Declare the connection variable at the top of the script and when you need it first time connect to the db (maybe put the code a function for this). Usually there is an DB abstraction layer used to optimize this process (an abstraction layer in layman terms means a bunch of functions/classes that help you organize better the way you connect/use the db/db connections).

    You should NEVER open multiple connections to the same database or put db open code into loops as this is poor coding (it costs you resources and execution time) which may lead to serious problems easily.

    Your probably better off if you open the connections at the top of the script and let PHP close them at the end of the script (this is unless you know for sure you wont need your connection after some point in the script and that your script will take a long time to run). MySQL has a maximum number of connections so you need to take this in consideration when you are using the db on scripts that run often or take a long time to finish execution.
    LVL 5

    Expert Comment

    to resume and straight answer your question: dont open&close each time you do a query, open once and reuse. This changes if you know that the time between queries in the script is separated by minutes of processing (more than 3mins) in which case depending on your load on the server you might want to free up the mysql connection (this is in relation with that max number of connections on the SQL server).

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Suggested Solutions

    As this topic comes over and over again in different forms, I've finally decided to write a short (yea, right...) article / tutorial about pagination with PHP with MySQL database. There are dozens of these kind of tutorials, I know - I wanted to mak…
    Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
    Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now