Can I improve my PDO method (just started)

2019-09-23 04:06发布

问题:

I just switched to PDO from mySQLi (from mySQL) and it's so far good and easy, especially regarding prepared statements

This is what I have for a select with prepared statement

Main DB file (included in all pages):

class DBi {
    public static $conn;
    // this I need to make the connection "global"
}

try {

    DBi::$conn = new PDO("mysql:host=$dbhost;dbname=$dbname;charset=utf8", $dbuname, $dbpass);
    DBi::$conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    DBi::$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

}

catch(PDOException $e) {

    echo '<p class="error">Database error!</p>';

}

And in my page:

try {

    $sql = 'SELECT pagetitle, pagecontent FROM mypages WHERE pageid = ? LIMIT 1';

    $STH = DBi::$conn->prepare($sql);

    $STH->execute(array($thispageid));  // $thispageid is from a GET var

}

catch(PDOException $e) {
    echo '<p class="error">Database query error!</p>';
}

    if ($STH) {  // does this really need an if clause for it self?

        $row = $STH->fetch();

        if (!empty($row)) {  // was there found a row with content?

            echo '<h1>'.$row['pagetitle'].'</h1>
            <p>'.$row['pagecontent'].'</p>';

        }

    }

It all works. But am I doing it right? Or can I make it more simple some places?

Is using if (!empty($row)) {} an ok solution to check if there was a result row with content? Can't find other decent way to check for numrows on a prepared narrowed select

回答1:

catch(PDOException $e) {
    echo '<p class="error">Database query error!</p>';
}

I would use the opportunity to log which database query error occurred.

See example here: http://php.net/manual/en/pdostatement.errorinfo.php

Also if you catch an error, you should probably return from the function or the script.

if ($STH) {  // does this really need an if clause for it self?

If $STH isn't valid, then it should have generated an exception and been caught previously. And if you had returned from the function in that catch block, then you wouldn't get to this point in the code, so there's no need to test $STH for being non-null again. Just start fetching from it.

    $row = $STH->fetch();

    if (!empty($row)) {  // was there found a row with content?

I would write it this way:

$found_one = false;
while ($row = $STH->fetch()) {
    $found_one = true;
    . . . do other stuff with data . . .
}
if (!$found_one) { 
    echo "Sorry! Nothing found. Here's some default info:";
    . . . output default info here . . .
}

No need to test if it's empty, because if it were, the loop would exit.