PHP MySql Fetch produces "false" instead of a dataset

Question

I'm attempting to create an aggregated data set for graphing (min and max values of every X rows) and I'm not getting the results I expect. I can run the query from PHPMyAdmin and it works perfectly, I get a single row with two columns. However, when I use PDO it simply produces false instead of an array as I expect.

// Get an min/max aggregated dataset
$aggregatedData = [];
echo (string)$count . "<br>"; // DEBUG

$query = "SELECT MIN(channel1) AS MinVal, MAX(channel1) AS MaxVal FROM "
        . "(SELECT channel1 FROM datacache WHERE fileId = ? ORDER BY time LIMIT ?,?) subset"; 

$sql = $conn->prepare($query);

while ($offset < $count)
{
    $sql->execute([$chart, $offset, $aggregate]);
    echo $chart . ", " . $offset . ", " . $aggregate . "<br>"; // DEBUG

    $data = $sql->fetch(PDO::FETCH_ASSOC);
    echo json_encode($data)."<br>"; // DEBUG

    array_push($aggregatedData, $data["MinVal"], $data["MaxVal"]);
    $offset += $aggregate;
}

echo json_encode($aggregatedData); //AJAX Export

My debug output is as follows (excerpt for brevity)

1000004
37, 0, 50
false
37, 50, 50
false
37, 100, 50
false
37, 150, 50
false
37, 200, 50
false

Note: this was not a duplicate as suggested.

$count is another SQL query a bit earlier in the script that simply pulls the total rows for the fileId. The debug shows that is working correctly. — , Feb 08 '18 at 18:16
@AbraCadaver var_dump($sql->errorInfo()); produces the following. However I can execute this query manually (with appropriate values) and it works, so I am confused by that. array(3) { [0]=> string(5) "00000" [1]=> int(1064) [2]=> string(164) "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''0','50') subset' at line 1" } — , Feb 08 '18 at 18:31
Possible duplicate of [When to use single quotes, double quotes, and back ticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql) — Brian, Feb 08 '18 at 22:47

AbraCadaver · Accepted Answer · 2018-02-08T18:55:27.453

1

PDOStatement::execute() treats all array values passed as strings PDO::PARAM_STR, which is not the case for your LIMIT ?,? values which need to be integers PDO::PARAM_INT.

You probably need use PDOStatement::bindParam() with PDO::PARAM_INT instead. Also, you can do this outside of the loop once since the variables are bound by reference:

$sql->bindParam(1, $chart, PDO::PARAM_INT);  // or whatever it is
$sql->bindParam(2, $offset, PDO::PARAM_INT);
$sql->bindParam(3, $aggregate, PDO::PARAM_INT);

while ($offset < $count)
{
    $sql->execute();
    $data = $sql->fetch(PDO::FETCH_ASSOC);
    array_push($aggregatedData, $data["MinVal"], $data["MaxVal"]);
    $offset += $aggregate;
}

edited Feb 08 '18 at 18:55

answered Feb 08 '18 at 18:33

AbraCadaver

78,200
7
66
87

This was correct, thank you. I edited to add parameter indexes to the bindParam. – Feb 08 '18 at 18:48
Thanks, forgot that. Also, a note about not needing it in the loop. – AbraCadaver Feb 08 '18 at 18:55

PHP MySql Fetch produces "false" instead of a dataset

1 Answers1