Pdo Prepared Statement Fetch() Returning Double Results

PDO prepared statement fetch() returning double results

You should say to PDO, that you want only an associative array or a numbered array:

while ($rows_get_rows = $result_get_rows->fetch(PDO::FETCH_ASSOC)) 

to get an associative array or

while ($rows_get_rows = $result_get_rows->fetch(PDO::FETCH_NUM)) 

to get an array indexed by the column number

from PDOStatement::fetch

fetch_style

Controls how the next row will be returned to the caller.
This value must be one of the PDO::FETCH_* constants, defaulting to
value of PDO::ATTR_DEFAULT_FETCH_MODE (which defaults to
PDO::FETCH_BOTH).

PDO::FETCH_ASSOC: returns an array indexed by column name as returned
in your result set

PDO::FETCH_BOTH (default): returns an array indexed by both column
name and 0-indexed column number as returned in your result set

PHP PDO execute() returning duplicate results

Solved by checking with someone better at databases than myself...

I foolishly named the columns as integer values.

So, what appeared as "duplicate values" was just the fact that the column number and the column name were the same!

In conclusion, I am bad at database structure.

PHP PDO Select query returns double values

Set the default fetch mode like so

$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ); 

Or

$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); 

before running the fetch

$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
foreach($result as $row) {
    array_push($result_array, $row);
}

Or replace with a one liners using fetchAll() and use prepared and parameterized statements as well to mitigate against SQL Injection Attack

$query = "SELECT datum, tijd, zone,lucht, temperatuur 
            FROM waarden 
            WHERE datum = :datum";
$result->prepare($query);

$result->execute([':datum'=>$waarde]);

$result_array = $result->fetchAll(PDO::FETCH_ASSOC);

The parameter to fetchAll(PDO::FETCH_ASSOC) controls how results will be returned.

Why am I getting my column values in my result set twice?

Because PDO is returning an array like this:

[0] => Array
        (
            [name] => apple
            [0] => apple
            [colour] => red
            [1] => red
        )

And when you loop all the rows, you have the result by index and associative.

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

Try $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

PDO returning incorrect, but duplicate, data. Key's not in database.

It's not duplicates, it's just the current FETCH_MODE you're using. To get as associative keys only you need to specify as such; by default it fetches as both.

Use like so:

$query->fetchAll(PDO::FETCH_NUM); // to fetch with numeric indexes
$query->fetchAll(PDO::FETCH_ASSOC); // to fetch with associative indexes

fetchAll docs

fetch docs

fetch' in PDO gets only one result

Fetch should be used to display the next row from the database result.

To get all rows, you should use fetchAll();

• PDOStatement::fetch — Fetches the next row from a result set
• PDOStatement::fetchAll() — Returns an array containing all of the result set rows

Change your example to:

<?php
    $sql = new PDO('mysql:host=localhost;dbname=b', 'root', 'root');
    $f = $sql->query('select * from user');
    $f->setFetchMode(PDO::FETCH_ASSOC);
    print_r($f->fetchAll());
?>

or if you want use PDOStatement::fetch to

<?php
    $sql = new PDO('mysql:host=localhost;dbname=b', 'root', 'root');
    $sth = $sql->query('select * from user');
    while($row = $sth->fetch(PDO::FETCH_ASSOC))
    {
      print_r($row);
    }
?>

PDO multiple fetching of same query

You can't fetch from the DB multiple times like this. Do this instead:

$orders = $ordersQuery->fetchAll(PDO::FETCH_ASSOC);

...

foreach ($orders as $val) {
    // stuff 1
}

...

foreach ($orders as $val) {
    // stuff 2
}

My PDO Prepared Statement is not working with a fetchALL and Limit

replace the below line

$stmt->execute([$somequantity]);

with

$stmt->bindParam(1, $somequantity, PDO::PARAM_INT);
$stmt->execute();

---

Related Topics