PHP PDO Select query returns double values

Question

For my inhome temparature sensor i'm using a raspberry pi with php, sqlite, PDO, and HTML.

I've create a table in sqlite

Using

BEGIN;
CREATE TABLE waarden (datum TEXT, tijd TEXT, zone TEXT, lucht REAL, temperatuur REAL);
COMMIT;

My rpi with DHT22 records everything fine so no i've created a webpage accessing the data via AJAX using the following php.file

<?php
function datumConversie($datum){    
    $delen = explode('/',$datum,3);
    $geconverteerd  = $delen[2].$delen[0].$delen[1];
    return $geconverteerd;
}
ini_set('display_errors', 'On');
error_reporting(E_ALL | E_STRICT);

$db = new PDO("sqlite:/home/pi/sensor.db");
$result_array = array();
$date = $_POST["datepicker"];
$waarde = datumConversie($date);
$tijd=""; 
$temperatuur="";
$query = "SELECT datum, tijd, zone,lucht, temperatuur FROM waarden WHERE datum = $waarde";
$result = $db->query($query);

foreach($result as $row)
{
    array_push($result_array, $row);
}
echo json_encode($result_array);
$db = null;
?>

The problem is that when I look in the browser response it seems that values are returned twice. Once with their appropriate field-name and once with their column index. (0 being datum, 1 being tijd etc). See below

{"datum":"20170601","0":"20170601","tijd":"00:01","1":"00:01","zone":"kelder","2":"kelder","lucht":"53.0","3":"53.0","temperatuur":"24.3","4":"24.3"}, {"datum":"20170601","0":"20170601","tijd":"00:06","1":"00:06","zone":"kelder","2":"kelder","lucht":"53.1","3":"53.1","temperatuur":"24.3","4":"24.3"}, {"datum":"20170601","0":"20170601","tijd":"00:11","1":"00:11","zone":"kelder","2":"kelder","lucht":"53.1","3":"53.1","temperatuur":"24.2","4":"24.2"},

How can I avoid this. I've tried several conversions which can work in the end but that is just patching some flaw without addressing the cause?

Any suggestions

RiggsFolly · Accepted Answer · 2017-06-01T15:28:31.590

6

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.

edited Jun 01 '17 at 15:28

answered Jun 01 '17 at 12:07

RiggsFolly

93,638
21
103
149

That was fast, thank you. Added $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ); All is perfect now! Thank You – MaPo Jun 01 '17 at 12:17
It Worth a mention that the example above for to OP code is vulnerable to SQL Injection. Its always better to use prepared statements even if you know/trust where the variable `$waarde` comes from. – ecarrizo Jun 01 '17 at 12:40
1

@ecarrizo True, I was just being lazy. Amended – RiggsFolly Jun 01 '17 at 15:29

score 1 · Answer 2 · answered Jun 01 '17 at 12:08

Try this sample code...

$sth = $db->prepare("SELECT datum, tijd, zone,lucht, temperatuur FROM waarden WHERE datum = :datum");
$sth->execute(array('datum' => $waarde));
$result = $sth->fetchAll(PDO::FETCH_ASSOC);

echo '<pre>';
print_r($result);
echo '</pre>';

PHP PDO Select query returns double values

2 Answers2

Linked

Related