No results from prepared statement query

Question

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.

UrGuardian4ngel · Accepted Answer · 2013-07-14 01:22:36Z

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?

Turns out that, yes, that code in particular works fine. What I'd been missing was the reset between statement uses, so on the first call I would retrieve the ID and the subsequent calls would fail. Thanks for giving me an isolated, working chunk of my code to help me direct my search properly :)

Collectives™ on Stack Overflow

No results from prepared statement query

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related