Accessing nested MySQL Select with multiple conditions in PHP.

Question

I want to select data from one table that matches certain conditions, and then select data from another table based on conditions of the data I just selected, and be able to view the selections from both tables. I attempted this with a nested select:

$query = 
"
SELECT 
    `url`, `userID`, `likes`, `time`, `latitude`, `longitude`, `distance `,
    (SELECT `up` from userHistory WHERE userHistory.url = photoList.url AND userHistory.userID = photoList.userID)
FROM 
    photoList 
WHERE 
    `valid` = '1' AND `time` > '$validTime' AND `likes` > '-6' 
HAVING 
    `distance` < '1'
ORDER BY 
    `time` DESC 
LIMIT 
    '0' , '200'";

Basically I want to select an object in the photoList... and then do a select into userHistory matching the url and userID in the selected photoList row. I want to select both of these so that when I loop through in PHP, I could do the following:

while ($row = $result->fetch_assoc()){
        $storeObjects[] = array("url" => $row["url"], "userID" => $row["userID"],
        "latitude" => $row["latitude"], "longitude" => $row["longitude"],
        "likes" => $row["likes"],
        "time" => $row["time"], "distance" => $row["distance"], 
        "userLiked" => $row["up"]); //$row["up"] is ALWAYS NULL
}

In the above loop, $row["up"] is always NULL, while the other categories in the row are the expected values. I am wondering if $row["up"] is even the correct way to access the data selected by the subquery.

score 2 · Accepted Answer · answered Dec 19 '15 at 08:56

2

You can JOIN 2 tables as below:

SELECT p.`url`, p.`userID`, p.`likes`, p.`time`, p.`latitude`,
p.`longitude`, p.`distance`, u.`up`
FROM photoList p
JOIN userHistory u
ON u.`url` = p.`url`
AND u.`userID` = p.`userID`
WHERE p.`valid` = '1'
AND p.`time` > '$validTime'
AND p.`likes` > '-6' 
HAVING p.`distance` < '1'
ORDER BY p.`time` DESC
LIMIT 0, 200

answered Dec 19 '15 at 08:56

Samir Selia

7,007
2
11
30

Thank you! Works exactly as needed. – patrickd Dec 19 '15 at 09:08

score 0 · Answer 2 · answered Dec 19 '15 at 08:59

0

You can use NATURAL JOIN to join two tables on common attributes, like this:

SELECT url, userID, likes, time, latitude, longitude, distance, up
FROM photoList
NATURAL JOIN userHistory
WHERE valid = 1 AND time > '$validTime' AND likes > -6 AND distance < 1
ORDER BY time DESC
LIMIT 0, 200

answered Dec 19 '15 at 08:59

Rajdeep Paul

16,887
3
18
37

Your query will throw an error saying 'Ambiguous column `url` and `userID`'. – Samir Selia Dec 19 '15 at 09:02
Because columns `url` and `userID` are present in both tables `photoList` and `userHistory`. So in `SELECT` clause we need to specify the table or alias. – Samir Selia Dec 19 '15 at 09:05
@Samir A natural join removes the duplicate columns involved in the equality comparison so only 1 of each compared column remains. See [this answer](http://stackoverflow.com/a/7870216/5517143). – Rajdeep Paul Dec 19 '15 at 09:08
ohh my bad. Didn't notice Natural Join. – Samir Selia Dec 19 '15 at 09:13

Accessing nested MySQL Select with multiple conditions in PHP.

2 Answers2