[php] How to read fetch(PDO::FETCH_ASSOC);

I am trying to build a web application using PHP and I am using memcached for storing user data from the DB.

For example lets say that I have this code:

 $sql    = "SELECT * FROM users WHERE user_id = :user_id";
$stmt   = $this->_db->prepare($sql);
$result = $stmt->execute(array(":user_id" => $user_id));
$user   = $stmt->fetch(PDO::FETCH_ASSOC);

I am not really sure how to read the $user variable and get the data out of it. I will need to be able to read the email and password column.

I was hoping that someone could explain to me how this works.

Thanks

To read the result you can read it like a simple php array.

For example, getting the name can be done like $user['name'], and so on. The method fetch(PDO::FETCH_ASSOC) will only return 1 tuple tho. If you want to get all tuples, you can use fetchall(PDO::FETCH_ASSOC). You can go through the multidimensional array and get the values just the same.

PDO:FETCH_ASSOC puts the results in an array where values are mapped to their field names.

You can access the name field like this: $user['name'].

I recommend using PDO::FETCH_OBJ. It fetches fields in an object and you can access like this: $user->name

Method

$user = $stmt->fetch(PDO::FETCH_ASSOC);

returns a dictionary. You can simply get email and password:

$email = $user['email'];
$password = $user['password'];

Other method

$users = $stmt->fetchall(PDO::FETCH_ASSOC);

returns a list of a dictionary

Loop through the array like any other Associative Array:

while($data = $datas->fetch( PDO::FETCH_ASSOC )){ 
     print $data['title'].'<br>'; 
}

or

$resultset = $datas->fetchALL(PDO::FETCH_ASSOC);

echo '<pre>'.$resultset.'</pre>';

/* Design Pattern "table-data gateway" */

class Gateway
{
    protected $connection = null;

    public function __construct()
    {
        $this->connection = new PDO("mysql:host=localhost; dbname=db_users", 'root', '');
    }

    public function loadAll()
    {
        $sql = 'SELECT * FROM users';
        $rows = $this->connection->query($sql);

        return $rows;
    }

    public function loadById($id)
    {
        $sql = 'SELECT * FROM users WHERE user_id = ' . (int) $id;
        $result = $this->connection->query($sql);

        return $result->fetch(PDO::FETCH_ASSOC);
        // http://php.net/manual/en/pdostatement.fetch.php //                   
    }
}

/* Print all row with column 'user_id' only */

$gateway  = new Gateway();
$users    = $gateway->loadAll();

$no = 1;
foreach ($users as $key => $value) {
    echo $no . '. ' . $key . ' => ' . $value['user_id'] . '<br />';
    $no++;
}

/* Print user_id = 1 with all column */

$user = $gateway->loadById(1);

$no = 1;
foreach ($user as $key => $value) {
    echo $no . '. ' . $key . ' => ' . $value . '<br />';
    $no++;
}

/* Print user_id = 1 with column 'email and password' */

$user = $gateway->loadById(1);

echo $user['email'];
echo $user['password'];