"Multiple SELECT, INNER JOIN" sql query not working

Question

I tested my sql query in phpMyAdmin and it works there. But when I copy it and paste in my code then it return NULL. My other code is error-free as it works perfect when I remove this sql query. Thus, I believe there is some problem with this query. I spent 2 days to find the problem. One important thing is that it works if I remove INNER JOIN. I tried using JOIN and FULL JOIN, but none of them works. Does anyone see any problem with this query?

Below is my query :

SELECT *
    From (
      SELECT userTrip.id,userTrip.fromLat,userTrip.fromLon,userTrip.toLat,userTrip.toLon
          From userTrip
              WHERE userTrip.userId != :userId
              AND userTrip.departureTime > CURDATE()
              AND ABS(UNIX_TIMESTAMP(userTrip.departureTime) - UNIX_TIMESTAMP(:departureTime)) <= " . DEPARTURETIME_DIFFERENCE_THRESHOLD . " * 60
              AND userTrip.fromLat Between :fromMinLat AND :fromMaxLat
              AND userTrip.fromLon Between :fromMinLon AND :fromMaxLon
          ) AS SourcesNearBy FULL JOIN user ON user.id = SourcesNearBy.userId ORDER BY user.id
        WHERE toLat Between :toMinLat AND :toMaxLat
        AND toLon Between :toMinLon AND :toMaxLon
        AND (user.gender LIKE :matchGender OR :matchGender LIKE 'A')
        AND (:matchAge = -1 OR (CAST(DATEDIFF(CURDATE(),user.birthdate)/365.25 AS UNSIGNED) Between (:matchAge - " . MAX_AGE_DIFFERENCE . ") AND (:matchAge + " . MAX_AGE_DIFFERENCE . ")))

Thank you.

This is my code :

public function findMatchesForUser($userId,$tripId)
    {
        $sql = "SELECT * from userTrip WHERE tripFinished = 0 AND id = :tripId";
        $stmt = $this->dbLink->prepare($sql);
        $stmt->bindParam(':tripId', $tripId, PDO::PARAM_INT);

        try
        {
            $stmt->execute();
            $userTrip = $stmt->fetchObject();
        }
        catch (PDOException $err)
        {
            echo $err->getMessage();
        }

        if ($userTrip == null)
            throw new Frapi_Error('Error Code : 500. Try again.');

        // get user's trip length
        $distanceClass = new Distance();
        $userTripLength = $distanceClass->drivingDistance($userTrip->fromLat, $userTrip->fromLon, $userTrip->toLat, $userTrip->toLon);

        // set up first bounding square
        $this->setUpBoundingSquare($userTrip->fromLat, $userTrip->fromLon, $userTripLength * SOURCE_DISTANCE_THRESHOLD / 100, 1);
        // set up bounding second square
        $this->setUpBoundingSquare($userTrip->toLat, $userTrip->toLon, $userTripLength * DESTINATION_DISTANCE_THRESHOLD / 100, 0);

        // perform first phase of algorithm
        $Candidates = $this->firstPhase($userId,$userTrip);
    }


private function firstPhase($userId,$userTrip)
    {
        $sql = "SELECT *
                  From (
                          SELECT userTrip.id,userTrip.fromLat,userTrip.fromLon,userTrip.toLat,userTrip.toLon
                              From userTrip INNER JOIN user ON userTrip.userId = user.id
                                  WHERE userTrip.userId != :userId
                                  AND (user.gender LIKE :matchGender OR :matchGender LIKE 'A')
                                  AND (:matchAge = -1 OR (CAST(DATEDIFF(CURDATE(),user.birthdate)/365.25 AS UNSIGNED) Between (:matchAge - " . MAX_AGE_DIFFERENCE . ") AND (:matchAge + " . MAX_AGE_DIFFERENCE . ")))
                                  AND userTrip.departureTime > '".date('Y-m-d H:i:s')."'
                                  AND ABS(UNIX_TIMESTAMP(userTrip.departureTime) - UNIX_TIMESTAMP(:departureTime)) <= " . DEPARTURETIME_DIFFERENCE_THRESHOLD . " * 60
                                  AND userTrip.fromLat Between :fromMinLat AND :fromMaxLat
                                  AND userTrip.fromLon Between :fromMinLon AND :fromMaxLon
                      ) AS SourcesNearBy
                      WHERE toLat Between :toMinLat AND :toMaxLat
                      AND toLon Between :toMinLon AND :toMaxLon;";

        $stmt = $this->dbLink->prepare($sql);

        // Bind parameters

        $stmt->bindParam(':fromMinLat', $this->fromMinLat, PDO::PARAM_STR);
        $stmt->bindParam(':fromMinLon', $this->fromMinLon, PDO::PARAM_STR);
        $stmt->bindParam(':fromMaxLat', $this->fromMaxLat, PDO::PARAM_STR);
        $stmt->bindParam(':fromMaxLon', $this->fromMaxLon, PDO::PARAM_STR);
        $stmt->bindParam(':toMinLat', $this->toMinLat, PDO::PARAM_STR);
        $stmt->bindParam(':toMinLon', $this->toMinLon, PDO::PARAM_STR);
        $stmt->bindParam(':toMaxLat', $this->toMaxLat, PDO::PARAM_STR);
        $stmt->bindParam(':toMaxLon', $this->toMaxLon, PDO::PARAM_STR);
        $stmt->bindParam(':userId', $userId, PDO::PARAM_INT);
        $stmt->bindParam(':matchGender', $userTrip->matchGender, PDO::PARAM_STR);
        $stmt->bindParam(':matchAge', $userTrip->matchAge, PDO::PARAM_INT);
        $stmt->bindParam(':departureTime', $userTrip->departureTime, PDO::PARAM_STR);

        try
        {
            $stmt->execute();
            $Candidates = $stmt->fetchAll();
        }
        catch (PDOException $err)
        {
            echo $err->getMessage();
        }
        // If no matchCandidates
        if ($Candidates == null)
            throw new Frapi_Error("No match candidates found!");

        return $Candidates;
    }

The alternate query which runs perfect. But it is not good at performance. I want to shift INNER JOIN in outer SELECT.

$sql = "SELECT *
                  From (
                        SELECT userTrip.id,userTrip.fromLat,userTrip.fromLon,userTrip.toLat,userTrip.toLon
                            From userTrip INNER JOIN user ON userTrip.userId = user.id
                                WHERE userTrip.userId != :userId
                                  AND (user.gender LIKE :matchGender OR :matchGender LIKE 'A')
                                  AND (:matchAge = -1 OR (CAST(DATEDIFF(CURDATE(),user.birthdate)/365.25 AS UNSIGNED) Between (:matchAge - " . MAX_AGE_DIFFERENCE . ") AND (:matchAge + " . MAX_AGE_DIFFERENCE . ")))
                                  AND userTrip.departureTime > '".date('Y-m-d H:i:s')."'
                                  AND ABS(UNIX_TIMESTAMP(userTrip.departureTime) - UNIX_TIMESTAMP(:departureTime)) <= " . DEPARTURETIME_DIFFERENCE_THRESHOLD . " * 60
                                  AND userTrip.fromLat Between :fromMinLat AND :fromMaxLat
                                  AND userTrip.fromLon Between :fromMinLon AND :fromMaxLon
                      ) AS SourcesNearBy
                      WHERE toLat Between :toMinLat AND :toMaxLat
                      AND toLon Between :toMinLon AND :toMaxLon;";

Query by removing FULL JOIN

SELECT *
    From (
      SELECT userTrip.id,userTrip.fromLat,userTrip.fromLon,userTrip.toLat,userTrip.toLon
          From userTrip
              WHERE userTrip.userId != :userId
              AND userTrip.departureTime > CURDATE()
              AND ABS(UNIX_TIMESTAMP(userTrip.departureTime) - UNIX_TIMESTAMP(:departureTime)) <= " . DEPARTURETIME_DIFFERENCE_THRESHOLD . " * 60
              AND userTrip.fromLat Between :fromMinLat AND :fromMaxLat
              AND userTrip.fromLon Between :fromMinLon AND :fromMaxLon
          ) AS SourcesNearBy INNER JOIN user ON user.id = SourcesNearBy.userId
        WHERE toLat Between :toMinLat AND :toMaxLat
        AND toLon Between :toMinLon AND :toMaxLon
        AND (user.gender LIKE :matchGender OR :matchGender LIKE 'A')
        AND (:matchAge = -1 OR (CAST(DATEDIFF(CURDATE(),user.birthdate)/365.25 AS UNSIGNED) Between (:matchAge - " . MAX_AGE_DIFFERENCE . ") AND (:matchAge + " . MAX_AGE_DIFFERENCE . ")))

userTrip Table

id | userName<br>

 1 |     A<br>
 2 |     B<br>
 3 |     C<br>
 4 |     D<br>
 5 |     E<br>
 6 |     F<br>

user Table

id | userId | fromLat | fromLon | toLat | toLon | departureTime | matchGender | matchAge<br>
  5 | 1 | 40.712898 | -74.013199 | 40.728157 | -74.077644 | 2013-04-26 15:56:08 | M | 25<br>
 10 | 2 | 28.520140 | -81.388771 | 28.054642 | -82.469940 | 2013-01-17 10:34:56 | F | 30<br>

check your mysql log and see your final request. – BMN Apr 17 '13 at 12:38 — BMN, Apr 17 '13 at 12:38

Kickstart · Answer 1 · 2013-04-18T08:50:03.597

2

You appear to have a random ORDER BY user.id in the middle of your SELECT statement.

EDIT - Playing around with your code, there is no need for a subselect anyway.

Something like this should do it.:-

SELECT userTrip.id,userTrip.fromLat,userTrip.fromLon,userTrip.toLat,userTrip.toLon
FROM userTrip 
INNER JOIN user 
ON userTrip.userId = user.id
WHERE userTrip.userId != :userId
AND (user.gender LIKE :matchGender OR :matchGender LIKE 'A')
AND (:matchAge = -1 OR (CAST(DATEDIFF(CURDATE(),user.birthdate)/365.25 AS UNSIGNED) BETWEEN (:matchAge - " . MAX_AGE_DIFFERENCE . ") AND (:matchAge + " . MAX_AGE_DIFFERENCE . ")))
AND userTrip.departureTime > '".date('Y-m-d H:i:s')."'
AND ABS(UNIX_TIMESTAMP(userTrip.departureTime) - UNIX_TIMESTAMP(:departureTime)) <= " . DEPARTURETIME_DIFFERENCE_THRESHOLD . " * 60
AND userTrip.fromLat BETWEEN :fromMinLat AND :fromMaxLat
AND userTrip.fromLon BETWEEN :fromMinLon AND :fromMaxLon
AND toLat BETWEEN :toMinLat AND :toMaxLat
AND toLon BETWEEN :toMinLon AND :toMaxLon

Try that with some real values. If it performs poorly then I suspect the issue is down to indexes, so do an EXPLAIN and post the results.

edited Apr 18 '13 at 08:50

answered Apr 17 '13 at 11:48

Kickstart

21,403
2
21
33

The code you have added now looks fine to me (you have got rid of the order by, and the FULL JOIN). Suggests the issue might be a with the data you are using. – Kickstart Apr 17 '13 at 12:46
What is the alternative query? – Kickstart Apr 18 '13 at 08:03
I edited my question to have alternative query, which runs perfect. But I want to optimize it. – Geek Apr 18 '13 at 08:18
The query you have added is identical to the one you had previously posted in your code fragment. Can you update your post with the new code you are trying that doesn't work (the version there at the moment is still invalid sql due to the order by in the middle of it, and the full join). – Kickstart Apr 18 '13 at 08:29
both queries are different. Last alternate query is working, but not the one that is in starting of my question. Working query has `INNER JOIN` in inner SELECT, whereas problematic query has in outer SELECT. There is where the problem occurs. But not able to figure out. I started executing query by using only one condition and added one each time query works. So until I added INNER JOIN it was working. Do you want to take a look at my tables I am executing query on? – Geek Apr 18 '13 at 09:42
Problem is that your original query that you say is not working is invalid SQL (it still has a FULL join rather than an INNER JOIN). You say you have corrected this, but you have not posted up the version which has been corrected but is still not working. The other 2 queries you have posted up (the one in the php fragment, and the one at the end of your post) are identical to each other, so at the moment I am left trying to work out what is wrong with a query that you have not posted in its current form. Can you post that, and some table details would help as well. – Kickstart Apr 18 '13 at 09:50
Thanks for keep helping me. I added query with INNER JOIN and tables. – Geek Apr 18 '13 at 10:36
No problem but it still has the invalid FULL join with order in it – Kickstart Apr 18 '13 at 10:45
Sorry for that. I changed that and tried to run. But the same problem exists. – Geek Apr 18 '13 at 11:13
You still have the ORDER BY user.id after the join. This is invalid, hence the SQL fails. – Kickstart Apr 18 '13 at 11:25
sorry again. I removed it when executed last, just forgot to remove in question. I tried your query with `single SELECT statement`. Which works. I don't know if that is better at performance than what I want. – Geek Apr 18 '13 at 12:40
That will probably depend on the indexes on your tables, etc. Try entering the fields in the query and doing an EXPLAIN, then post the results of the explain. – Kickstart Apr 18 '13 at 13:21

score 0 · Answer 2 · answered Apr 17 '13 at 10:57

0

Have you run the query with variables value i.e there might be some variable in query which might contain wrong value as desired.Rest the query looking absolutely fine.

answered Apr 17 '13 at 10:57

Ajeet Singh

32
2

Yes, I ran it using absolute values. It worked in phpMyAdmin but not after integrating with my code. I tried using absolute values in my code, too. – Geek Apr 17 '13 at 11:04
@AjeetSingh This is not quite an answer – BMN Apr 17 '13 at 12:35

"Multiple SELECT, INNER JOIN" sql query not working

2 Answers2