php - 将 MySQL 连接器转换为 PDO

Question

在之前的帖子中听取了人们的一些建议后，我尝试将 MySQL 转换为 PDO，但遇到了一些问题。

这是我原来的 MySQL 连接类:

class DbConnector {

    public static function getInstance() {
        static $instance = null;
        if ($instance === null) {
            $instance = new DbConnector();
        }

        return $instance;
    }

    protected $theQuery;
    private $link;

    function DbConnector() {

        $host = 'localhost';
        $db = '';
        $user = '';
        $pass = '';

        // connect to the db
        $this->link = mysql_connect($host, $user, $pass);
        mysql_select_db($db);
        register_shutdown_function(array(&$this, 'close'));
    }

    public function find($query) {
        $ret = mysql_query($query, $this->link);
        if (mysql_num_rows($ret) == 0)
            return array();
        $retArray = array();

        while ($row = mysql_fetch_array($ret))
            $retArray[] = $row;

        return $retArray;
    }

    public function insert($query) {
        $ret = mysql_query($query, $this->link);

        if (mysql_affected_rows() < 1)
            return false;
        return true;
    }

    public function query($query) {
        $this->theQuery = $query;
        return mysql_query($query, $this->link);
    }

    public function fetchArray($result) {
        return mysql_fetch_array($result);
    }

    public function close() {
        mysql_close($this->link);
    }

    public function exists($query) {
        $ret = mysql_query($query, $this->link);
        if (mysql_num_rows($ret) == 0)
            return false;
    }

    public function last_id($query) {
        return mysql_insert_id($query);
    }

}

这是我正在编写的函数:

function getRandomSubmission() {
    global $db;

    if(!empty($_GET['id'])){
        $submission_id = $_GET['id'];

        $query = $db->find("
            SELECT
                *
            FROM
                `submissions`
            WHERE id = '{$submission_id}'
            LIMIT 1
        ");

    }
    else {
        $query = $db->find("
            SELECT
                *
            FROM
                `submissions`
            ORDER BY RAND()
            LIMIT 1
        ");
    }


    if($query) {
        return $query[0];
    }
    else {
        $query = $db->find("
            SELECT
                *
            FROM
                `submissions`
            ORDER BY RAND()
            LIMIT 1
        ");
    }
}

这是 PDO 连接器:

$host = 'localhost';
$username = '';
$pass = '';
$db = '';

try {
    $dbh = new PDO("mysql:host=$host;dbname=$db", $username, $pass);
} catch (PDOException $e) {
    echo $e->getMessage();
}

这是我尝试将其转换为的内容，但这完全是错误的。我想我需要在第二个 if 语句中返回一个 PDO 关联数组，但我不确定。

function getRandomSubmission() {
    global $dbh;

    if(!empty($_GET['id'])){
        $submission_id = $_GET['id'];

    $stmt = $dbh->prepare('
            SELECT
                *
            FROM
                `submissions`
            WHERE
                `id` = ?
            LIMIT 1
            ');

    $stmt->bindParam(1, $submission_id, PDO::PARAM_INT);
    $stmt->execute();
    }
    else {
    $stmt = $dbh->prepare('
            SELECT
                *
            FROM
                `submissions`
            ORDER BY RAND()
            LIMIT 1
        ');

    $stmt->execute();
    }


    if($stmt) {
        return $stmt[0];
    }
    else {
    $stmt = $dbh->prepare('
            SELECT
                *
            FROM
                `submissions`
            ORDER BY RAND()
            LIMIT 1
        ');

    $stmt->execute();
    }
}

然而，最初的版本按预期工作(我意识到我将连接详细信息留空)。

Answer 1

您需要调用PDOStatement对象的fetch方法:

return $stmt->fetch()

了解获取样式，实际上您不需要 FETCH_BOTH ;-)