CODEWITHSUNDEEP
phpmysql
user784637
user784637

Reputation: 16142

Should you open/close a database connection inside of a loop or outside of a loop?

Using PDO with prepared statements (I learned from here: http://www.kitebird.com/articles/php-pdo.html) I'm using a function that opens a database connection: 

function testdb_connect ()
{
   $dbh = new PDO("mysql:host=localhost;dbname=test", "testuser", "testpass");
   return ($dbh);
}

I have a for loop that iterates and inserts rows in a table. Should I open the connection before the loop and close it after the looping construct

$dbh = testdb_connect();
for($i=0; $i<$number_of_values; $i++){
    //Insert rows
}
$dbh = NULL   ;

Or should I open and close it within the loop for each insert statement? What are the pros and cons of each method?

for($i=0; $i<$number_of_values; $i++){
    $dbh = testdb_connect();
    //Insert rows
    $dbh = NULL   ;
}

Upvotes: 1

Views: 3537

Answers (5)

CBusBus
CBusBus

Reputation: 2359

Opening and closing a connection is resource consuming and therefore it shouldn't be done in a loop, besides you only need one connection to carry out multiple queries.

There's also the insertion method: rather than running a query with every iteration it would make sense to build a single query and then run that at the end for example

create table test(
    testField VARCHAR(12)
)

function rowsToDb($rows)
{
    /*Define base of query*/
    $query =  "INSERT INTO test (testField) VALUES ";
    /*Iterate through rows concatenating new insert values*/
    foreach($rows as $row){
        $query .= "('". $row['testField']. "'),";
    }
    testdb_connect();
    /*Remove rogue "," and execute query*/
    mysql_query(substr($query, 0, strlen($query) - 1));
}

Upvotes: 1

Virendra
Virendra

Reputation: 2553

You should open and close the database connection outside your loop. Opening and closing the database connection everytime in the loop will cause lot of overhead and can potentially slow down your site.

You should ideally open the database connection just once in your page and close it once. So you can open the connection at the very start of the page/script and close it at the end.

Upvotes: 0

helloandre
helloandre

Reputation: 10721

as a general rule, open as early as possible, and close as late as possible.

to directly answer your question, outside the loop.

If you look at any framework, you will notice that a connection is opened very early in the bootstrapping process, and closed in an exiting process (if at all). Apache and PHP are smart enough to automatically terminate all open connections when a script finishes.

Upvotes: 0

deceze
deceze

Reputation: 522195

Open the connection once for the whole script. There's a certain overhead in opening a connection, and there's absolutely no advantage in re-opening it. So to be as efficient as possible, open it once at the start of the script and close it at the end. Opening and closing it inside a loop is nonsense.

Upvotes: 7

schaz
schaz

Reputation: 895

Since there is a significant overhead with any call to open or close a database or a file, for efficiency sake, I would open the database before the loop and close when the loop is completed.

Upvotes: 9

Related Questions