Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder bacterozoid's Avatar
    Join Date
    Jun 2002
    Location
    USA
    Posts
    490
    Thanks
    24
    Thanked 35 Times in 35 Posts

    Proper way to do databases

    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:

    PHP Code:
        $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!

  • #2
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    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.
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • Users who have thanked _Aerospace_Eng_ for this post:

    bacterozoid (08-12-2008)

  • #3
    New Coder
    Join Date
    Mar 2008
    Location
    Finland
    Posts
    21
    Thanks
    0
    Thanked 1 Time in 1 Post
    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 ?

  • #4
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    Quote Originally Posted by Mikroz View Post
    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.

  • Users who have thanked Inigoesdr for this post:

    bacterozoid (08-13-2008)

  • #5
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Quote Originally Posted by Inigoesdr View Post
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • Users who have thanked Fou-Lu for this post:

    bacterozoid (08-13-2008)

  • #6
    Regular Coder bacterozoid's Avatar
    Join Date
    Jun 2002
    Location
    USA
    Posts
    490
    Thanks
    24
    Thanked 35 Times in 35 Posts
    Makes sense to me. I'll be sure to correct my mistakes. Appreciate the response.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •