CODEWITHSUNDEEP
phpmysqldatabase
samceena
samceena

Reputation: 266

How do i implement a persistent connection to a MySQL Database using PHP?

I need some help with PHP code architecture and best practice. I need to run update on 4000 rows in a MySQL Database.

Here's the function/code i use to connect to the Database:

    function connectToDB(){
      $uname = "USERNAME";
      $pword = "PASSWORD";
      try {
        $db_conn = new PDO('mysql:host=SERVERHOSTNAME;dbname=DATABASENAME;port=PORTNUMBER', $uname, $pword);
        $db_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
      } catch (PDOException $pdoe){
        //fandle exception
      }
      return $db_conn;
    }

Now, when i do the actual work, here's the code i use in the loop,

    $all_array = array("1", "2", ......, "4000");
    foreach ($all_array as $key => $value) {
      $sqlcode = "INSERT INTO table .....";
      $conn_db = connectToDB();
      $conn_db_prepare = $conn_db->prepare($sqlcode);
      $conn_db_prepare->execute();
    }

With this, the code will run the connectToMitsubishiComfortDB for each key in the lop, which will be 4000 times, that's 4000 different connections. I'm not sure if this best practice. Is there a way for me to connect to the database once, and not run the 4000 loops so i don't have to connect every time?

Is there a way for me to improve the code?

This is an application that runs everyday and insert into 4000 rows in a table. The Database is MySQL, and code used is PHP.

Upvotes: 0

Views: 464

Answers (3)

Dave
Dave

Reputation: 5191

Move both the connection and the prepare outside of the loop. You only need to connect once and you only need to prepare once.

$conn_db = connectToDB();
$conn_db_prepare = $conn_db->prepare('INSERT INTO table .....');
$all_array = array("1", "2", ......, "4000");
foreach ($all_array as $key => $value) {
    $conn_db_prepare->execute();
}

Upvotes: 1

Mayank Dudakiya
Mayank Dudakiya

Reputation: 3869

Best way to enable persistent connection in PDO by passing array(PDO::ATTR_PERSISTENT => true) into the connection string.

$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass, array(PDO::ATTR_PERSISTENT => true));

https://www.php.net/manual/en/pdo.connections.php

Note:

If you're using the PDO ODBC driver and your ODBC libraries support ODBC Connection Pooling (unixODBC and Windows are two that do; there may be more), then it's recommended that you don't use persistent PDO connections, and instead leave the connection caching to the ODBC Connection Pooling layer. The ODBC Connection Pool is shared with other modules in the process; if PDO is told to cache the connection, then that connection would never be returned to the ODBC connection pool, resulting in additional connections being created to service those other modules.

Upvotes: 0

jtylerm
jtylerm

Reputation: 472

you need to move the line $conn_db = connectToDB(); out of your loop

Upvotes: 1

Related Questions