CODEWITHSUNDEEP
phpsqlmysqliprepared-statement
Alex Broadwin
Alex Broadwin

Reputation: 1338

No results from prepared statement query

I'm new to mysqli and using PHP in an object-oriented fashion, and I'm having a problem with using a prepared statement to retrieve a value. I have a PHP class which has a variable:

var $getUsernameStatement;

During construction I prepare the statement:

$this->getUsernameStatement = $this->db->prepare("SELECT username FROM users WHERE id = ?;");

Then, later, I retrieve a value with it:

function getUsername($userID) {
    $this->getUsernameStatement->bind_param("i", $userID);
    $this->getUsernameStatement->execute();
    $this->getUsernameStatement->bind_result($username);
    if($this->getUsernameStatement->fetch()) {
        echo("Retrieved username " . $username);
    } else {
        echo("Nope!");
    }
    return $username;
}

At least that's the plan. I don't seem to get any values back when I pass in a known good ID like so:

$user->getUsername(2); // There exists an entry with id 2 in the table

I'm sure I'm doing something wrong (nobody to blame but one's self in programming), but I can't seem to spot it. Any help would be greatly appreciated.

For reference, here is the SQL used to create the users table:

$sql = <<<SQL
    CREATE TABLE IF NOT EXISTS `users` (
    `id` INT NOT NULL AUTO_INCREMENT ,
    `username` VARCHAR(64) NOT NULL ,
    `email` VARCHAR(128) NOT NULL ,
    `password_hash` VARCHAR(128) NOT NULL ,
    PRIMARY KEY (`id`) ,
    UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
    UNIQUE INDEX `email_UNIQUE` (`email` ASC) ,
    UNIQUE INDEX `username_UNIQUE` (`username` ASC) );
SQL;

Any help would be greatly appreciated.

Upvotes: 0

Views: 225

Answers (1)

UrGuardian4ngel
UrGuardian4ngel

Reputation: 568

Well, for me your code seems to be working...

I did exactly like you said. On construct, creating the statement. Though you never said something about the $db variable, I assume you have defined and initialised the database connection it in your class, before using it?

class TheClass
{
    private $db;
    private $getUsernameStatement;

    function __construct()
    {
        // Initialise database variable
        $this->db = mysqli_connect("host", "username", "password", "dbname");

        // Prepare the statement
        $this->getUsernameStatement = $this->db->prepare("SELECT username FROM users WHERE id = ?;");
    }

    // Your function, without changes
    public function getUsername($userID) {
        $this->getUsernameStatement->bind_param("i", $userID);
        $this->getUsernameStatement->execute();
        $this->getUsernameStatement->bind_result($username);
        if($this->getUsernameStatement->fetch()) {
            echo("Retrieved username " . $username);
        } else {
            echo("Nope!");
        }
        return $username;
    }
}

and then test it by instantiating your class, and calling the method:

$c = new TheClass();
$username = $c->getUsername(2);

successfully prints Retrieved username MyUsername on the screen, and $username equals MyUsername (the username in your table with ID=2).

Seems like your code is working?

Upvotes: 1

Related Questions