CODEWITHSUNDEEP
phpmysqlpdo
Callum
Callum

Reputation: 725

How to retrieve items from an PDO database into an Array to retrieve a string

I am trying to retrieve a user's (1 - there will only be one with the session token) username from a session token stored in a database. The row formatted as id, code, username. which i'm trying retrieve the row that contains that then store the row into an array which i can retrieve the username.

The Table SQL

CREATE TABLE `signup` (

    `id` int(11) NOT NULL AUTO_INCREMENT,

    `code` varchar(255) COLLATE utf8_unicode_ci NOT NULL,

    `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,

    PRIMARY KEY (`id`),

    UNIQUE KEY `code` (`code`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

The Table Information

id  code            username
1   123-145-546-48  CallumCarmicheal

The Current Code i've been testing

$db2 = new PDO("mysql:host={$mysql_host};dbname={$mysql_database};charset=utf8", $mysql_user, $mysql_password, $options);

$token = "123-145-546-48";

$query = "
    SELECT
        id,
        code,
        username
    FROM signup
    WHERE
        code = $token
    ";

try {
    $stmt   = $db2->prepare($query);
    $stmt->execute();
} catch (PDOException $ex) {
    die("Failed to run query: " . $ex->getMessage());
}


// return the Username from getting the token
return ...

I cannot figure this out, it probably is simple but i made an signup system that requires a Admin generate an Session Code and Set the username, In the register form it would show the username and the user can enter his email and password. This would retrieve the username but every one i've tried would return an blank string or just wouldn't return anything. After the user registration the session token would be deleted.

Upvotes: 1

Views: 144

Answers (3)

Kevin
Kevin

Reputation: 41885

First off, if $code thats is going to be used in code is VARCHAR/TEXT, you should have wrapped it with quotes.

But since you're preparing, just bind it:

$token = "callumc";

// add a named placeholder for preparation
$query = "
    SELECT
        id,
        code,
        username
    FROM signup
    WHERE
        code = :code
    ";

try {
    $stmt = $db2->prepare($query);
    // bind it
    $stmt->execute(array(':code' => $token));
} catch (PDOException $ex) {
    die("Failed to run query: " . $ex->getMessage());
}

Then, if you're expecting to get something out of this, you need to fetch results:

$results = $stmt->fetch(PDO::FETCH_ASSOC);
echo $results['username']; // and others

Upvotes: 2

outlyer
outlyer

Reputation: 3943

You aren't doing anything with the result of the SELECT.

After

$stmt->execute();

You can retrieve the obtained fields with PDOStatement::fetch()

$row = $stmt->fetch();

Where $row will have id, code and username keys.

Also, setting your SQL query that way is not safe, instead use something like

$query = "
    SELECT
        id,
        code,
        username
    FROM signup
    WHERE
        code = ?
    ";
// ...
$stmt->execute(array($token));

Upvotes: 2

chris85
chris85

Reputation: 23892

You need to fetch the result after running the query.

http://php.net/manual/en/pdostatement.fetch.php

$result = $stmt->fetch(PDO::FETCH_ASSOC);
echo $result['username']; // or do whatever you want with the username

I also prefer to parameterize my SQL.

$query = "SELECT id, code, username
          FROM signup
          WHERE
          code = ?";
try {
   $stmt   = $db2->prepare($query);
   $stmt->execute(array($token));

Upvotes: 3

Related Questions