mySQLi prepared statement unable to get_result()

mysqli, php, prepared-statement

Solution

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

Problem

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.

Original source

Related problems