問題描述

我完全被 mySQLi 弄糊涂了.盡管我多年來一直在使用過程 mysql 調用，但我想習慣于為它提供的 db security/mySQL 注入保護制作準備好的語句.我正在嘗試編寫一個簡單的選擇語句(是的，我知道為此進行程序調用可提供性能增強).運行時，我得到所有回聲，直到我點擊 $result = $stmt->get_result(); 組件.這一切對我來說似乎相當簡單，但在閱讀了有關 mySQLi 的手冊數小時后，我不知所措.任何想法為什么會失敗?

I am totally confused by mySQLi. Although I have been using procedural mysql calls for years, I want to get used to making prepared statements for the db security/mySQL injection protection it offers. I am trying to write a simple select statement (yes I know making a procedural call for this offers performance enhancement). When run, I get all the echoes until I hit the $result = $stmt->get_result(); component. It all seems fairly straightforward to me, but I am at a loss after hours of reading manuals on mySQLi. Any ideas why this would be failing?

*注意:這是一個測試環境，雖然沒有進行字符清理/轉義，但我只是將有效內容傳遞到變量 $username 和 $email 中.而且，我已經查看了所有內容以找到我的問題的解決方案.

*note: this is a test environment and while no sanitizing/escaping of characters is taking place, I am only passing valid content into the variables $username and $email. And also, I have looked all over SO to find the solution to my issue.

function checkUsernameEmailAvailability($username, $email) {
    //Instantiate mysqli connection
    @$mysqli = new mysqli(C_HOST,C_USER,C_PASS,C_BASE) or die("Failed to connect to MySQL database...");
    if (!$mysqli)
    {
        echo 'Error: Could not connect to database.  Please try again later...';
        exit;
    } else {
        echo 'mysqli created';
    }

    /* Create a prepared statement */   
    if($stmt = $mysqli -> prepare("SELECT username,email FROM tb_users WHERE username=? OR email=?")) {
        echo '<br />MYSQLi: ';

        /* Bind parameters s - string, b - boolean, i - int, etc */
        $stmt -> bind_param("ss", $username, $email);
        echo '<br />paramsBound...';

        /* Execute it */
        $stmt -> execute();     
        echo '<br />Executed';

        $result = $stmt->get_result();
        echo '<br />Result acquired';

        /* now you can fetch the results into an array - NICE */
        $myrow = $result->fetch_assoc();
        echo '<br />Fetched';


        /* Close statement */
        /$stmt -> close();
        echo '<br />Done mysqli';
    }
}    

另外，每次調用函數時都必須實例化一個 mysqli 嗎?我假設它們不是像程序 mysql 中那樣的持久數據庫連接.是的，我知道這是一個范圍問題，不，我無法理解此類變量的范圍.我在函數外聲明的時候，進入函數時不可用.

Also, do I have to instantiate a mysqli every time I call a function? I'm assuming they're not persistent db connects like in procedural mysql. Yes, I am aware this is a scope issue, and no I have not been able to understand the scoping of this class variable. When I declared it outside of the function, it was not available when I came into the function.

更新如果我將第 12 行更改為:

UPDATE if I change line 12 from:

if($stmt = $mysqli -> prepare("SELECT username,email FROM tb_users WHERE username=? OR email=?")) {

到:

    $stmt = $mysqli->stmt_init();
    if($stmt = $mysqli -> prepare("SELECT username,email FROM tb_users WHERE username=? OR email=?")) {
        if(!stmt) echo 'Statement prepared'; else echo 'Statement NOT prepared';

我沒有準備好聲明.現在我更糊涂了....

I get Statement NOT prepared. Now I'm even more confused....

更新:我聯系了我的托管服務提供商，顯然支持 mySQLi，并且存在 mysqlnd 驅動程序.也許有一種方法可以簡單地測試這個?盡管過去他們通常會給我非常有見地的答案.

UPDATE: I contacted my hosting provider and apparently mySQLi is supported, and the mysqlnd driver is present. Perhaps there is a way to simply test this? Although they usually have given me pretty knowledgeable answers in the past.

更新...再次:我自己檢查了我的服務器功能并發現，雖然存在 mysqli 和 PDO，但 mysqlnd 不存在.因此，我明白為什么 get_result() 不起作用(我認為需要 mysqlnd)，我仍然不明白為什么準備好的語句本身不起作用.

UPDATE...AGAIN: I checked my server capabilities myself and discovered, while mysqli and PDO are present, mysqlnd is not. Thusly, I understand why get_result() will not work (mysqlnd required I think), I still don't understand why the prepared statement itself will not work.

推薦答案

通過一些檢查，盡管我的主機上安裝了 mysqli，但顯然 mysqlnd 驅動程序不存在.因此，無法使用 get_result()(php 手冊將 get_result 定義為依賴于 mysqlnd)，而是需要進行額外的編碼以按照我想要的方式處理我的結果.

Through some checking, even though mysqli is installed on my host, apparently the mysqlnd driver is not present. Therefore, get_result() can not be used(php manual defines get_result as mysqlnd dependent), and instead extra coding would need to be done to handle my result the way I would like.

因此，我決定嘗試了解 PDO 的工作原理，幾分鐘之內，瞧！！！

Therefore, I decided to try and learn how PDO works, and within minutes, voila!!!

用這個替換上面的代碼:

Replaced the above code with this:

function checkUsernameEmailAvailability($username, $email) {

echo 'pdo about to be created';

$dsn = 'mysql:dbname='.C_BASE.';host='.C_HOST;
$user = C_USER;
$password = C_PASS;

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$params = array(':username' => $username, ':email' => $email);

try{
$sth = $dbh->prepare('SELECT username,email FROM tb_users WHERE username = :username OR email = :email ');
} catch(PDOException $e) {
    echo 'Prepare failed: ' . $e->getMessage();
}

try{
$sth->execute($params);
} catch(PDOException $e) {
    echo 'Execute failed: ' . $e->getMessage();
}
$result = $sth->fetch(PDO::FETCH_ASSOC);
print_r($result);
}

我能想到的盡可能多的錯誤檢查，首先破解它根本沒有問題！

As much error checking as I could think of, and no problems at all first crack at it!

最終代碼

function checkUsernameEmailAvailability($username, $email) {
    $dsn = 'mysql:dbname='.C_BASE.';host='.C_HOST;
    $user = C_USER;
    $password = C_PASS;
    new PDO($dsn, $user, $password);

    $params = array(':username' => $username, ':email' => $email);

    $sth = $dbh->prepare('SELECT username,email FROM tb_users WHERE username = :username OR email = :email ');
    $sth->execute($params);
    $result = $sth->fetch(PDO::FETCH_ASSOC);

    print_r($result);
}

這篇關于mySQLi 準備好的語句無法 get_result()的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！