CODEWITHSUNDEEP
phpmysqlpdo
kaboom
kaboom

Reputation: 833

values are repeated for array returned by PDO

I connect to MySQL database using PDO and fetch all the data. When I print out the array, the values are repeated. How to fix it? Thank you

connect to DB:

$db = new PDO("mysql:host=localhost:3306;dbname=$dbName", $user, $pass,
            array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

fetch data and print out the result:

$result = $db->query("select UserBirthday, UserAddress, UserZipCode, UserPhone, UserFirstName, 
                UserLastName, UserPassword, UserSecurityQuestion
                from USER_PROFILE
                where UserID=$userID;")->fetchAll();

    print_r($result);

What it prints out:

Array ( [UserBirthday] => 1999-01-01 [0] => 1999-01-01 
[UserAddress] => 1 Infinite Loop Seattle [1] => 1 Infinite Loop Seattle 
[UserZipCode] => 98125 [2] => 98125 
[UserPhone] => 2068874596 [3] => 2068874596 
[UserFirstName] => abc [4] => abc 
[UserLastName] => cdf [5] => cdf 
[UserPassword] => 5271593ca406362d7a2701e331408ab77d5b5b88 [6] => 5271593ca406362d7a2701e331408ab77d5b5b88 [UserSecurityQuestion] => null [7] => null)

Upvotes: 4

Views: 656

Answers (2)

jwueller
jwueller

Reputation: 30996

By default PDO will fetch columns by name AND index.

Use the PDO::FETCH_ASSOC fetch mode to just fetch by name:

$result = $db->query("select UserBirthday, UserAddress, UserZipCode, UserPhone, UserFirstName, 
            UserLastName, UserPassword, UserSecurityQuestion
            from USER_PROFILE
            where UserID=$userID;")->fetchAll(PDO::FETCH_ASSOC);

Also check out alternative fetch modes.

Upvotes: 4

Your Common Sense
Your Common Sense

Reputation: 157870

the right way

$dsn = "mysql:host=localhost;dbname=$dbName;charset=utf8";
$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$db  = new PDO($dsn,$user, $pass, $opt);

$stm = $db->prepare("select * FROM USER_PROFILE where UserID=?");
$stm->execute(array($userID));
$data = $stm->fetch(); 
print_r($data);

Upvotes: 0

Related Questions