mySQLi prepared statement unable to get_result()

Question

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.

I use PDO with exceptions myself (and can definitely recommend it...) so I don't know that much about mysqli but it seems you can get a description of your error using `$stmt->error`, see http://www.php.net/manual/en/mysqli.error.php. What does that give you? — jeroen, May 05 '12 at 23:08
I am totally unfamiliar with PDO, does it offer the same benefit as mysqli prepared statements? I will check the $stmt->error function right away. Also, even though my provider clains mysqlnd is present, running phpinfo() shows no reference to it at all... — MaurerPower, May 05 '12 at 23:35
enable error reporting, display / logging and tell us which error message you get. With that information it's a no-brainer to tell you what's happening here. Everything else is just stabbing in the dark and making assumptions. That cost you nerves and time. — hakre, May 05 '12 at 23:52
Like I said, I know nothing about mysqli. In fact I probably would have done better to research mysqli more before trying that route. Checked out PDO (didn't even know it was comparable to mysqli), and it has a LOT of benefits. minor performance hit, but all things equal, named params and direct to object results are incredible! not to mention db abstraction. Looking more into this, will update soon... — MaurerPower, May 06 '12 at 00:05
Had no trouble with PDO! Final code is the following, can't answer my own question, cause reputation isn't high enough yet. `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); }` — MaurerPower, May 06 '12 at 00:32
Great! Now you just need to add error checking (using exceptions is easy) and you're all set... — jeroen, May 06 '12 at 01:35

score 12 · Accepted Answer · answered May 06 '12 at 06:38

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);
}

score 2 · Answer 2 · answered Dec 04 '13 at 15:36

2

mysqli_stmt :: get_result is Available only with mysqlnd package. remove php5-mysql package and install php5-mysqlnd instead

answered Dec 04 '13 at 15:36

Aboozar

51
2

And how can you do this ? – Bernhard Pointner May 04 '15 at 09:09

mySQLi prepared statement unable to get_result()

2 Answers2

Linked

Related