Why are my JOINs failing?

Question

I have a function to retrieve a single row of data representing a sales report for a store on a specific date. I'm using similar methodology to retrieve data throughout the same class and application and have never run into this problem. My function (and fetch()) are returning false, while the execute() is returning true.

When I run the same query in TOAD from the SQL editor, I'm getting a row of data back as expected.

Why is fetch() failing? Note: I also tried fetchAll() which is returning an empty set. I've also tried not using bound parameters, but that doesn't work either.

Here's the code:

    public function getFullReport($store_id, $date)
    {
        $pdo = $this->application->database()->PDO();
        $user_id = $this->application->session()->user_id();

        $query = <<<SQL
SELECT sales_reports.*,
       labor2.hours AS labor_am,
       labor3.hours AS labor_wa,
       labor4.hours AS labor_associate,
       labor5.hours AS labor_kitchen,
       labor6.hours AS labor_training
  FROM sales_reports
       JOIN labor_reports AS labor2
          ON     sales_reports.store_id = labor2.store_id
             AND sales_reports.date = labor2.date
             AND labor2.labor_type_id = 2
       JOIN labor_reports AS labor3
          ON     sales_reports.store_id = labor3.store_id
             AND sales_reports.date = labor3.date
             AND labor3.labor_type_id = 3
       JOIN labor_reports AS labor4
          ON     sales_reports.store_id = labor4.store_id
             AND sales_reports.date = labor4.date
             AND labor4.labor_type_id = 4
       JOIN labor_reports AS labor5
          ON     sales_reports.store_id = labor5.store_id
             AND sales_reports.date = labor5.date
             AND labor5.labor_type_id = 5
       JOIN labor_reports AS labor6
          ON     sales_reports.store_id = labor6.store_id
             AND sales_reports.date = labor6.date
             AND labor6.labor_type_id = 6
       JOIN user_store_permissions
          ON sales_reports.store_id = user_store_permissions.store_id
 WHERE     sales_reports.store_id = :store_id
       AND sales_reports.date = :date
       AND user_store_permissions.user_id = :user_id
 LIMIT 1
SQL;

        $statement = $pdo->prepare($query);
        $statement->bindParam(':store_id', $store_id);
        $statement->bindParam(':date', $date);
        $statement->bindParam(':user_id', $user_id);

        $statement->execute();

        return $statement->fetch(PDO::FETCH_ASSOC);
    }

UPDATE

It seems that the labor JOINs are failing for some reason. When I use LEFT JOIN it returns a row - with all of the labor values as null. However, I'm not seeing this result in Toad for MySQL, which performs both the LEFT JOIN and JOIN version of the query properly and returns the full row.

So the new question is, why are my JOINs not working in my PHP PDO code but they are working on the same data, with the same parameters when running the SQL in Toad?

Answer 1

It looks like it possibly has to do with the limit statement. I have the same issue using PDO with a very simple join as follows.

select e.id,e.name,c.id2 
from espempdata e, espempclasses c 
where e.empClass=c.id2 limit 50

It fails with the limit statement, and passes without it. Running it from the command line passes. Weird.

Answer 2

Sorry - I resolved this quite a long time ago and forgot to answer my own question as Relequestual recommended.

I believe the issue was that I was running the Toad SQL queries on my remote MySQL server whereas the PHP PDO statements were running off of my local MySQL server. A silly, typical headscratcher when you've been staring at the screen too long!