CODEWITHSUNDEEP
phppdo
Enrique Moreno Tent
Enrique Moreno Tent

Reputation: 25267

PDO showing more results than expected

I have a simple table with one field "id", and when I execute this code...

$dbh = new PDO('mysql:host='.$dbhost.';dbname='.$dbname, $dbuser, $dbpass);

$sql = 'SELECT * FROM logolist';
$q = $dbh->query($sql);
while($r = $q->fetch()){ print_r($r); }

... I get this output:

Array
(
    [ID] => 2
    [0] => 2
)
Array
(
    [ID] => 4
    [0] => 4
)

As you see, there's a [0] under the field "ID". if I add more field, I keep getting more extra elements inside the array. It's like every field is outputting it's value 2 times.

Why is this?

Upvotes: 1

Views: 137

Answers (5)

N.B.
N.B.

Reputation: 14061

I'm encountering this practice of having a loop for fetching MySQL results and I'm wondering why people do it so I'll write up this answer and try to clear up a few things.

1) You do not need a loop to fetch results 2) Reason you get the results duplicated is because you're receiving an associative array and index-based one. That's the default behaviour.

What you can do is this: 

$dbh = new PDO('mysql:host='.$dbhost.';dbname='.$dbname, $dbuser, $dbpass);

// Tell PDO to throw exceptions in case of a query error
$dbh ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try
{
    $result = $dbh->query("SELECT * FROM logolist")->fetchAll(PDO::FETCH_ASSOC); 

    // Your result is now in $result array which is associative. 
    // If it's empty - no results were found. 
    // In case of an error, you'd go to catch block of the code and you'd echo out an error. 
}
catch(PDOException $e)
{
    echo "Error reported: ". $e->getMessage();
}

Upvotes: 1

Yogesh Suthar
Yogesh Suthar

Reputation: 30488

fetch gives numerical and associative array

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

you can use FETCH_ASSOC for only getting associative array

Upvotes: 1

Teena Thomas
Teena Thomas

Reputation: 5239

  while($r = $q->fetch(PDO::FETCH_ASSOC)){ print_r($r); }

PDO::FETCH_ASSOC will only get values with their associative keys, without numerical indexes.

Upvotes: 1

Mihai Iorga
Mihai Iorga

Reputation: 39704

That is normal for fetch() without any attribute (it's sets FETCH_BOTH by default). It works like old mysql_fetch_array() and 0 is the numerical index.

If you switch to Associative you will get only fields:

while($r = $q->fetch(PDO::FETCH_ASSOC)){
    print_r($r);
}

PDOStatement::fetch - for all styles.

Upvotes: 4

Erwin Moller
Erwin Moller

Reputation: 2408

You are fetching both numerical and associative.

Check the PDO documentation:

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

(You are using PDO::FETCH_BOTH (default))

Upvotes: 1

Related Questions