Why is my SQL query returning an array?

Question

I've got a table of users each with a unique ID. When I try and fetch their ID for the session it returns an array of two IDs for some reason.

So in the following code, $_SESSION['userid'] becomes an array containing two instances of the same ID.

I can't figure out why though...

$_SESSION['userid'] = getUserID($_POST['username']);


function getUserID($username)
{
    include 'db.inc.php';
    try {
        $sql = "SELECT id FROM user WHERE username = '". $username. "'";
        $s   = $pdo->prepare($sql);
        $s->bindValue(':username', $username);
        $s->execute();
    }
    catch (PDOException $e) {
        $error = 'Error getting userid for '.$username . '....error: '.$e;
        include $_SERVER['DOCUMENT_ROOT']."/database/includes/pages/error.html.php";
        exit();
    }
    $row = $s->fetch();
    return $row;

}

did you check your db? maybe its actually contains the same object twice? — Dima
– Dima, Commented Oct 2, 2014 at 15:16
Not a solution, but your SQL statement is incorrect. In order to bind the parameter with PDO, it should look like SELECT id FROM user WHERE username = :username — Jeremiah Winsley
– Jeremiah Winsley, Commented Oct 2, 2014 at 15:17
To clarify, getUserID() is returning an associative array? Can you post the contents of that array (do print_r($row))? What are you expecting it to return? — Dan Blows
– Dan Blows, Commented Oct 2, 2014 at 15:18
@DimaGoltsman, the database is in testing phase so just contains 2 users with unique ids(primary key) and unique usernames. — dlofrodloh
– dlofrodloh, Commented Oct 2, 2014 at 15:20
@Blowski Array ( [id] => 2 [0] => 2 ) .... expecting it to just return the id e.g. 1 or 2 not an array — dlofrodloh
– dlofrodloh, Commented Oct 2, 2014 at 15:22

Jeremiah Winsley · Accepted Answer · 2014-10-02 15:24:08Z

2

Your problem is in your $s->fetch(). By default PDO fetches an array indexed by both field and number, e.g. $row['id'] and $row[0] Try this:

$row = $s->fetch(PDO::FETCH_ASSOC);
return $row['id'];

See http://php.net/manual/en/pdostatement.fetch.php for more information.

answered Oct 2, 2014 at 15:24

Jeremiah Winsley

2,55720 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dan Blows · Accepted Answer · 2014-10-02 15:32:52Z

2

PDOStatement::fetch() defaults to returning PDO::FETCH_BOTH:

returns an array indexed by both column name and 0-indexed column number as returned in your result set

(Taken from http://php.net/manual/en/pdostatement.fetch.php)

You need to do:

$row = $s->fetch(PDO::FETCH_ASSOC);

edited Oct 2, 2014 at 15:32

answered Oct 2, 2014 at 15:25

Dan Blows

21.2k10 gold badges68 silver badges99 bronze badges

Comments

Jay Blanchard · Accepted Answer · 2014-10-02 15:24:36Z

1

The problem is that $s->fetch() is returning an object. You need to modify it to return an associative array:

$s->fetch(PDO::FETCH_ASSOC)

answered Oct 2, 2014 at 15:24

Jay Blanchard

34.5k17 gold badges82 silver badges130 bronze badges

Comments

Avinash Babu · Accepted Answer · 2014-10-02 15:30:10Z

0

You are doing wrong with $result = $sth->fetch(); You have missed PDO::FETCH_ASSOC which returns an array indexed by column name as returned in your result set.

So you must use the code like

$row = $s->fetch(PDO::FETCH_ASSOC);

answered Oct 2, 2014 at 15:30

Avinash Babu

6,2534 gold badges23 silver badges26 bronze badges

Collectives™ on Stack Overflow

Why is my SQL query returning an array?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related