CODEWITHSUNDEEP
phpmysqli
Paolo Rossi
Paolo Rossi

Reputation: 2510

PHP - Open one or more connection in a mysql class DB?

I'm creating a"DB" class for mysql. The DB class will be aggregated with other classes in my project.

index.php

$db = new DB();
$fo = new Foo($db);

print_r($db->query("SELECT * FROM tbl"));

print_r($fo->testSelect());

Foo class

class Foo {

    /**
    * Constructor
    * Aggregate class db object
    */
    public function __construct( DB $db ) {
       $this->db = $db;
    }

    public function test() {
       return $this->db->query("select * from tbl");
    }
}

My doubt is how to use the connection to the db to make best use of resources. In the DB class It is better to open the connection whenever I run queries (updates, selects,..) or open the connection once in the constructor ?

DB Class (1)

if ( !class_exists( 'DB' ) ) {

    class DB {

        private function connect() {
            $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME, DB_PORT, DB_SOCKET);
            if ( $mysqli->connect_error ) {
                die( "Connection failed: " . $mysqli->connect_errno . ' ' . $mysqli->connect_error );
            } else {
                $mysqli->set_charset(DB_CHARSET);
            }
            return $mysqli;
        }

        public function query( $query ) {
            $db = $this->connect();
            $result = $db->query( $query );
            while ( $row = $result->fetch_array(MYSQLI_ASSOC) ) {
                $results[] = $row;
            }
            $result->free();
            $db->close();
            return $results;
        }

        ..insert, update, delete function..
    }
}

DB Class (2)

if ( !class_exists( 'DB' ) ) {

    class DB {

        private mysqli;

        public function __construct() {
            $this->mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME, DB_PORT, DB_SOCKET);
            if ( $this->mysqli->connect_error ) {
                die( "Connection failed: " . $this->mysqli->connect_errno . ' ' . $this->mysqli->connect_error );
            } else {
                $this->mysqli->set_charset(DB_CHARSET);
            }
            return $this->mysqli;
        }

        public function query($query) {
            $result = $this->mysqli->query( $query );
            while ( $row = $result->fetch_array(MYSQLI_ASSOC) ) {
                $results[] = $row;
            }
            $result->free();
            return $results;
        }

        ..insert, update, delete function..

        public function __destruct() {
            $this->mysqli->close();
        }
    }
}

Which is the correct/better solution? Thank you

Upvotes: 1

Views: 119

Answers (1)

Barmar
Barmar

Reputation: 780724

In general it's best to minimize the number of calls to the database. Opening and closing the database connection for every query is wasteful overhead and will slow down the application if there are lots of queries.

Also, every time you close the connection you lose any database session state. So if you set user variables in one query and then try to use them in another query, this won't work. Or if you set session variables like SET TIME_ZONE = ..., you'll lose this. And functions like LAST_INSERT_ID() and FOUND_ROWS() won't work.

Upvotes: 1

Related Questions