PDO returning empty result

Question

I have the below PHP script which generates a MySQL query and runs it, however currently the query is returning an empty result.

$sth = $dbh->prepare("SELECT TeamID FROM UserTeam WHERE UserID=? AND Deleted IS NULL");
            $sth->execute(array($_POST['userID']));
            $teams = $sth->fetchAll();

            $sql = "SELECT * FROM Event WHERE UserID=? AND Deleted IS NULL";

            if (count($teams) > 0) {
                $sql .=  " OR TeamID IN (";
                foreach ($teams as $team){
                    $sql .= $team['TeamID'] . ",";
                }

                $sql = substr($sql, 0, -1);

                $sql .=  ")";
            }

            $sql .= " ORDER BY Created DESC LIMIT ?, 10 ";

            $sth = $dbh->prepare($sql);
            $sth->execute(array($_POST['userID'], $_POST['start']));
            $events = $sth->fetchAll();

            $response["success"] = 1;
            $response["result"] = $events;
            $response["sql"] = $sql;
            $response["post"] = $_POST;
            echo json_encode($response);

Is for example giving the output JSON array:

{
  "post" : {
    "start" : "0",
    "userID" : "33"
  },
  "result" : [

  ],
  "sql" : "SELECT * FROM Event WHERE UserID=? AND Deleted IS NULL OR TeamID IN (12,13,17) ORDER BY Created DESC LIMIT ?, 10 ",
  "success" : 1
}

However if I run the generated query (replacing the ? with the right values) against the database manually I am getting the expected result (10 results returned)

Update

Even changing the SQL generation to hardcoded values instead of ? I still get no result.

i.e

{
  "post" : {
    "start" : "0",
    "userID" : "33"
  },
  "result" : [

  ],
  "sql" : "SELECT * FROM Event WHERE UserID=33 AND Deleted IS NULL OR TeamID IN (12,13,17) ORDER BY Created DESC LIMIT 0, 10 ",
  "success" : 1
}

try to pass a variable instead of `$_POST['userID'] ` and you can also enable the error mode by adding this after your connection `$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` — Amr Aly, Apr 18 '17 at 21:33

score -1 · Answer 1 · answered Apr 20 '17 at 03:30

Try to add parenthesis to your sql query. For example instead of:

SELECT * FROM Event WHERE UserID=33 AND Deleted IS NULL OR TeamID IN (12,13,17) ORDER BY Created DESC LIMIT 0, 10

try:

SELECT * FROM Event WHERE UserID=33 AND (Deleted IS NULL OR TeamID IN (12,13,17)) ORDER BY Created DESC LIMIT 0, 10

score -1 · Accepted Answer · answered Apr 20 '17 at 04:05

You can't use a placeholder in a LIMIT clause. Nor do you need to, since it's an integer that can be easily made safe. In addition, you can fetch the column from your first query to more easily build your second. I suspect this could be fetched with a single query, but can't say for sure without seeing your database schema.

Finally, you aren't catching any errors from your database code so you can't know if anything will be successful. Assuming you set up your connection properly you should be able to catch any exceptions.

<?php
try {
    $sth = $dbh->prepare("SELECT TeamID FROM UserTeam WHERE UserID=? AND Deleted IS NULL");
    $sth->execute([$_POST["userID"]]);
    $teams = $sth->fetchAll(PDO::FETCH_COLUMN, 0);
} catch (PDOException $e) {
    // do something here
}
$start = (int)$_POST["start"];

$sql = "SELECT * FROM Event WHERE UserID=? AND Deleted IS NULL";

if (count($teams) > 0) {
    $sql .=  " OR TeamID IN (" . implode(",", $teams) . ")";
}

$sql .= " ORDER BY Created DESC LIMIT $start, 10";

try {
    $sth = $dbh->prepare($sql);
    $sth->execute([$_POST["userID"]]);
    $events = $sth->fetchAll();
} catch (PDOException $e) {
    // do something here
}
$response["success"] = 1;
$response["result"] = $events;
$response["sql"] = $sql;
$response["post"] = $_POST;
header("Content-Type: application/json");
echo json_encode($response);

score -1 · Answer 3 · answered Sep 01 '21 at 04:03

-1

I'm returning results using placeholder for LIMIT like this

    public function list_stuff(int $max)
    {
        $sql = 'SELECT *
FROM mytable
LIMIT :thelimit';
        $set_sql = $this->pdo->prepare($sql);
        $set_sql->bindValue(':thelimit', $max, \PDO::PARAM_INT);
        $set_sql->execute();
        return $set_sql->fetchAll(\PDO::FETCH_ASSOC);
    }

answered Sep 01 '21 at 04:03

Jonny

15,955
18
111
232

1

BTW this is also stated in this answer: https://stackoverflow.com/a/16868108/129202 – Jonny Sep 01 '21 at 04:06

PDO returning empty result

3 Answers3