所有数据库查询PHP的一个函数

Question

i'm working with PHP for some weeks now and with the time i am asking myself, if it is possible to handle all database-queries (SELECT, UPDATE, INSERT, DELETE) with a single function.

At the moment i have round about 30x of these "try-catch" PDO's to communicate with my database:

//DB connection info already set...

    try {

        $exc = $this->dbh->prepare("SELECT uid FROM users WHERE email = :mail");

        $exc->bindParam(':mail', $mail, PDO::PARAM_STR);

        $exc->execute();
        $exc->setFetchMode(PDO::FETCH_ASSOC);

        while ($row = $exc->fetch()) {
            // do something with $row['uid']
        }

        $this->dbh = null;
        $exc = null;

    } catch (PDOException $e) {           
        echo $e;
        $this->dbh = null;
        $exc = null;
    }

Is there a way to minify this code by using a single function and do i or the server get any profit of this ?

Thank you, Louis

Answer 1

Like I said in my comment, your code is already quite good, but there are a couple places where you could reduce the amount of boilerplate you're writing.

// You can set the default fetch mode in the constructor
$this->dbh = new PDO($cstr, $user, $pass, [PDO::DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);

try {
    $exc = $this->dbh->prepare("SELECT uid FROM users WHERE email = :mail");

    // you don't have to explicitly bind your parameters
    // and you can feed them directly to execute()
    $exc->execute([':mail' => $mail]);

    while ($row = $exc->fetch()) {
        // do something with $row['uid']
    }
} catch (PDOException $e) {           
    echo $e;
} finally {
    // 'finally' blocks are always executed
    $this->dbh = null;
    $exc = null;
}

However:

1. I'm not sure why you're setting $this->dbh and $exc to null at the end.
   • $exc will be dereferenced when it goes out of scope, eg: at the end of the enclosing function, and then garbage-collected later.
   • The same goes for $this->dbh when its containing object goes out of scope, and what if you want/need to use it elsewhere in the class? Please don't tell me that you reinitialize it repeatedly.
   • Unless you're ludicrously low on RAM and need to immediately recover the literal handful of bytes from these objects there's no reason to explicitly unset them.
2. Don't catch an Exception that you're not doing anything with.
   • By only echoing the error message and letting execution continue in a broken/unexpected state you're setting yourself up for more trouble down the line.
   • Let the Exception bubble up to higher, more general layer, or just don't catch it at all and let it cause execution to halt. You can use set_exception_handler() to define how your app deals with uncaught exceptions, eg: safely logging the details and/or showing a user-friendly error message.