查询没有给出正确的结果（PHP / MySQL）

Question

I'm getting a weird result from a quite long query, which I will simplify here:

DROP TEMPORARY TABLE IF EXISTS table1;
CREATE TEMPORARY TABLE table1 AS
(SELECT 
parent.id as parent_id,
times.a_time,
times.sequence,
FROM times
LEFT JOIN parent ON times.parent_id=parent.id
WHERE times.stop_id=10);

DROP TEMPORARY TABLE IF EXISTS table2;
CREATE TEMPORARY TABLE table2 AS
(SELECT 
parent.id as parent_id,
times.b_time,
times.sequence,
FROM times
LEFT JOIN parent ON times.parent_id=parent.id
WHERE times.stop_id=15 );

--here comes PDO->exec();

SELECT table1.*, table2.b_time
FROM table1
LEFT JOIN table2 ON table1.parent_id=table2.parent_id
WHERE table2.parent_id IS NOT NULL AND table1.sequence<table2.sequence
ORDER BY table1.a_time

I'm testing the query using EMS MySQL Manager 2007, and in PHP I'm using PDO query.

In order to get the final result, (I know that PDO doesn't support running this full query at once and giving back the result set), I run PDO->exec() after temporary tables creation (see comment in the query), and then I run PDO->query() on the last SELECT:

$db = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME, DB_USER, DB_PASS);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

$tempTablesSQL='DROP TEMPORARY TABLE IF EXISTS...'; //create temporary tables
$db->exec($tempTablesSQL);

$sql='SELECT table1.*, table2.b_time ...'; //JOIN and SELECT the results
$results=array();
foreach($db->query($sql) as $row){
  $results[]=$row;
}

print_r($results);

In MySQL Manager I run the whole query at once, and for those specific IDs I'm getting 29 rows as result (which is correct, because the records are inserted from a previously parsed file, and by comparing the results to the file I know they are good).

But in PHP, I'm getting only 25 results, and totally wrong values for b_time.

So, my questions are:

• why do I get wrong results?

• is my approach of calling this query wrong (in PHP)?

Any help is appreciated.

--EDIT--

It's not just PDO, I tried with mysqli_multi_query, I'm getting the same wrong results.

One important thing I noticed is: if I use regular tables instead of the temporary, the results are fine.

Answer 1

Let my try to put a couple of suggestions on the second part of your question which is is my approach of calling this query wrong?

First of all it looks like you don't need to create any temp tables. Based on what you showed the whole thing can be a single query like

SELECT q1.parent_id, q1.a_time, q1.sequence, q2.b_time
  FROM
(
    SELECT p.id parent_id, t.a_time, t.sequence
      FROM times t LEFT JOIN parent p
        ON t.parent_id=p.id
     WHERE t.stop_id = ?
 ) q1 LEFT JOIN
(
    SELECT p.id as parent_id, t.b_time, t.sequence
      FROM times t LEFT JOIN parent p
        ON t.parent_id=parent.id
     WHERE t.stop_id = ?
) q2 ON q2.parent_id IS NOT NULL 
    AND q1.sequence < q2.sequence
 ORDER BY q1.a_time

And execute it as a prepared statement

...
$sql = 'SELECT ...'; // the whole thing from above
$query = $db->prepare($sql);
$query->execute(array($stop_id1, $stop_id2));
$result = $query->fetchAll(PDO::FETCH_ASSOC);

var_dump($result);

Now even if for some reason you have to use temp tables and perform some manipulations along the way before returning the result set then I'd suggest to wrap it up a stored procedure

DELIMITER $$
CREATE PROCEDURE sp_myproc (IN stop_id1 INT, IN stop_id2 INT, ...)
BEGIN
    DROP TEMPORARY TABLE IF EXISTS table1;
    CREATE TEMPORARY TABLE table1 AS
    ...
    WHERE times.stop_id = stop_id1;

    DROP TEMPORARY TABLE IF EXISTS table1;
    CREATE TEMPORARY TABLE table1 AS
    ...
    WHERE times.stop_id = stop_id2

    -- return the resultset
    SELECT table1.*, table2.b_time
    FROM table1
    ...
END$$
DELIMITER ;

And call your procedure once from php

...
$sql = 'CALL sp_myproc(?, ?)';
$query = $db->prepare($sql);
$query->execute(array($stop_id1, $stop_id2));
$result = $query->fetchAll(PDO::FETCH_ASSOC);

var_dump($result);

Now regarding the first part of your question why do I get wrong results? OUTER joins can be tricky and especially when you chain them. You can easily filter some rows out or produce additional rows (which happens most often).

Those few joins that you removed may be the cause.

Anyway provided information is not enough for conclusive answer.

But I would suggest instead of returning columns as table1.* specify all columns explicitly and give explicit aliases for columns that have the same names in different tables that are part of a join.