mySQLi prepared statement unable to get_result()

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?

*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';
    }
}

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.

UPDATE if I change line 12 from:

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

to:

    $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....

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.

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.

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.

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!

Final Code

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_stmt :: get_result is Available only with mysqlnd package. remove php5-mysql package and install php5-mysqlnd instead

mySQLi prepared statement unable to get_result()

Related

Recent Posts