CODEWITHSUNDEEP
phpmysqlpdoprepared-statement
Monica
Monica

Reputation: 1599

This PDO statement is returning an integer instead of a string

In a class, I have some PDO:

$userFName = 'userFName';
include('dbconnect.php');       // Normally I'd store the db connect script outside of webroot
$pdo = new PDO("mysql:host=$db_host;dbname=$db_name;", $db_user, $db_password);
$stmt = $pdo->prepare('SELECT userFName FROM Users WHERE username = :uname AND password = :pword AND roleID = 1');
$stmt->bindParam(':uname', $this->user->username);
$stmt->bindParam(':pword', $this->user->password);
$stmt->bindColumn(4, $userFName, PDO::PARAM_STR);
$stmt->execute();
$familiar = $stmt->fetch(PDO::FETCH_BOUND);
$this->user->firstName = $familiar;

It's returning the ID in the first column instead of the VARCHAR contents in the 4th column. Any idea why?

Upvotes: 0

Views: 613

Answers (2)

Your Common Sense
Your Common Sense

Reputation: 157839

There is too much code in your class. And one fault. To send a distinct query to get just one property from database, creating a distinct connection for this is a dead overkill. Connection have to be moved away unconditionally and you must think of getting ALL user data with one query.

Proper code

function __construct($pdo) {
    $this->pdo = $pdo;
    // Normally you should include somewhere in a bootstrap file
    // not in the application class
    // and instantiate PDO in that bootstrap as well
    // and only PASS already created instance to the class
}

function getUserFName() {
    $sql = 'SELECT * FROM Users WHERE username = ? AND password = ? AND roleID = 1';
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array($this->user->username,$this->user->password));
    return $stmt->fetchColumn();
}

Upvotes: 0

hek2mgl
hek2mgl

Reputation: 157947

When using PDO::FETCH_BOUND with fetch(), the method will not return a result record. Instead the value of the column should be available in the variable you have bound using $stmt->bindColumn() earlier.

So change your code to:

$stmt->bindColumn(1, $userFName, PDO::PARAM_STR);
$stmt->execute();
$stmt->fetch(PDO::FETCH_BOUND);
$this->user->firstName = $userFName; // <-- use the bound variable

However you won't need that bindColumn() call. You could simplify the code as this:

$stmt->execute();
$row = $stmt->fetch(); // uses PDO::FETCH_ASSOC by default
$this->user->firstName = $row['FName'];

Upvotes: 1

Related Questions