PHP Returns Empty Array and MySQL Returns Collection

Question

I have a discrepancy between running my SQL code directly in phpMyAdmin versus using PHP's PDO. This is my PHP code:

<?php
    $configs_database = include(dirname( __DIR__, 2) . '/php/config_database.php');

    // Database variables
    $dbHost = $configs_database['host'];
    $dbUsername = $configs_database['username'];
    $dbPassword = $configs_database['password'];
    $dbName = $configs_database['name'];

    /* Create connection */ 
    $dsn = "mysql:dbname=$dbName;host=$dbHost;charset=utf8mb4";
    $db = new PDO($dsn, $dbUsername, $dbPassword);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

    // Get the business information where the user_id equals the session value
    $stmt = $db->prepare("
        SELECT
            customer.*
        FROM
            customer
        WHERE
            customer.business_id = (SELECT business_id FROM `user` WHERE user_id = :userId)
        LIMIT :limit
        OFFSET :offset;");

    // Parameterize the query
    $userId = ($_POST["userId"] ? $_POST["userId"] : $_SESSION["id"]);
    $limit = ($_POST["rowCount"] ? $_POST["rowCount"] : 99999);
    $offset = ($_POST["current"] && $_POST["rowCount"] ? intval($_POST["current"]) * $_POST["rowCount"] : 0);
    $stmt->bindValue(':userId', $userId, PDO::PARAM_INT);
    $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);

    // Execute the query
    $stmt->execute();

    // Explicitly close the connection
    $db = null;

    // Get the results
    $customers = $stmt->fetchAll(PDO::FETCH_ASSOC);

    echo json_encode($customers);
    return json_encode($customers);
?>

If I submit the request in insomnia with the following JSON:

{
    "userId": 2,
    "current": 1,
    "rowCount": 100
}

It prints an empty array. However, if I run the SQL directly in phpMyAdmin it returns the expected collection. I have no idea how to even debug this because if I echo the values of $userId, $limit, and $offset (the values I pass in the parameters) they are all the expected values (2, 1, and 100). What could be causing this discrepancy?

You can make a typo in sql and when you run it will print the query which it was executing and you can try the same query on phpmyadmin. — danish-khan-I, Jan 17 '20 at 06:14
Thank you @danish-khan-I the issue turned out to be that even though I was specifying the parameter as a PARAM_INT, it was converting it to a string for some reason. The solution was to call intval() on the variables. — David, Jan 17 '20 at 06:24

PHP Returns Empty Array and MySQL Returns Collection

0 Answers0