How to reference DB connection inside each method without being redundant-CodePudding

New to OOP PHP & PDO. This isn't production code, just for practice locally.

I have a class where I want to put all my CRUD operations. That class extends the database connection class and also requires that file.

Here is the first method I've built inside the CRUD class, which inserts a user.

protected function insertUser(){

        $connectionvar = new PDO('mysql:host='. $this->host .';dbname='.$this->db, $this->user, $this->password);
        //all remaining code for the method
        }

The above method words just fine and inserts a user as expected, but I don't feel like I should have to be writing that $connectionvar variable for each of these methods, it's redundant. Just not sure how to access the database connection without doing so. For additional context, the database connection class that this file requires auto-creates a db connection when the file is required (as I've done).

There must be a way I can do this without repeating that code, any ideas?

CodePudding user response：

Firstly, it's good to use terms correctly so you don't confuse yourself and others: $connectionvar isn't a property, it's a local variable; $this->host is a property.

Secondly, every time you call new PDO, you are creating a new connection to the database. You say you have a file that "auto-creates a db connection", but if you're not storing that connection somewhere to use later, that's useless - you're just wasting resources with an extra connection.

The solution is to run the new PDO line once, and store the result in a property. Normally rather than when you include the file, this would be when you create an object - so, you'd have something like this:

class DB {
    protected PDO $connection;

    public function __construct(string $host, string $db, string $user, string $password) {
        $this->connection = new PDO('mysql:host='. $host .';dbname='.$db, $user, $password);
   }
}

Then inside class DB, and anything that extends DB, you can access $this->connection to get your existing database connection, without creating a new one.

Note that each class that extends DB will still open its own connection, since it will run its own inherited copy of the constructor when you create it. The best structure is therefore to have only one class that actually talks to the database, and has public methods to run queries, e.g.:

public function runQueryAndGetResult(string $sql, array $parameters): array {
    $statement = $this->connection->prepare($sql);
    $statement->execute($parameters);
    return $statement->fetchAll(PDO::FETCH_ASSOC);
}

CodePudding user response：

Ideally you'll have it in the constructor (even more ideally: in a class dedicated to working with the DB, but let's focus on your current setup) or in a method of your class.

Example:

public function __construct() { 
   $this->connection = new PDO(....)
}

then you'll have it in all class method in $this->connection.

OK, let's remove the other two examples as they could give someone a bad idea.