Not getting output from SQL query

Question

I am trying to make a script which will display the latest posts from my friends AND me. There are 3 tables related to this - statusUpdates (contains posts), friends (links between friends using IDs) and users (contains details of all users of the website). The script I am using at the moment is as follows.

$myQuery = mysql_query ("SELECT * FROM statusUpdates AS su 
             LEFT JOIN (friends AS fr) ON
                (fr.frHostID = su.authorID OR fr.frContactID = su.authorID) 
             WHERE (fr.frHostID = 105090)
             GROUP BY su.statusID
             ORDER BY su.statusID
             DESC LIMIT 0,5");

Please note that 105090 is my own ID.

Problem is that when I have no friends, not even a single post is showing up even though I have made more than one status update.

A solution would be to create a row in the friends table, adding me as my own friend. But in that case, I would have to prevent my own name from occuring in a "Show List of Friends" page, and any other related page.

I was initially using INNER JOIN and having the above problem. Then I came a similar issue, and modified my script with LEFT JOIN as was suggested. It seemed to have solved the issue of the OP of that post, but not mine. I cannot submit comments in that post (probably because it is 2 years old). So I am creating this post.

[Please, don't use mysql_* functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) in new code. They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about prepared statements instead, and use [pdo](https://wiki.php.net/rfc/mysql_deprecation) or [mysqli](http://stackoverflow.com/questions/tagged/mysqli). — zessx, Jul 02 '13 at 08:56
@zessx At the moment I don't have much knowledge of either PDO or MySQLi. I'll start learning PDO after I am done with this project, which I need to complete as soon as possible. But thank you for your advice. — Debojyoti Ghosh, Jul 02 '13 at 09:47
You don't have to worry. You won't spend that much time to discover PDO or MySQLi. Just keep in mind `mysql_` are already deprecated, and will be remove in a future PHP version. — zessx, Jul 02 '13 at 10:22

score 3 · Accepted Answer · edited May 23 '17 at 12:11

3

UPDATE: I didn't take into account you also wanted your friends' posts.

Although you already marked an answer as correct, as the IN statement is usually slower than a JOIN, you can try this:

SELECT * 
FROM statusUpdates AS su LEFT JOIN friends AS fr
ON fr.frContactID = su.authorID
WHERE (fr.frHostID = 105090 or su.authorID = 105090)
GROUP BY su.statusID
ORDER BY su.statusID
DESC LIMIT 0,5

INITIAL ANSWER:

Just change the where condition from:

WHERE (fr.frHostID = 105090)

to

WHERE (su.authorID = 105090)

The reason is that you are putting a where condition in the right part of the left join, so you are forcing the existance of friends.

BTW, take care of this two posts:

Why shouldn't I use mysql_* functions in PHP?

and

How can I prevent SQL injection in PHP?

edited May 23 '17 at 12:11

Community

1
1

answered Jul 02 '13 at 09:08

Alejandro Colorado

6,034
2
28
39

I know you already marked an answer as correct, but I updated my answer. – Alejandro Colorado Jul 02 '13 at 10:47
Thanks a lot. It works perfectly now. Added a JOIN statement after the LEFT JOIN clause to pull username and other details from another table _users_. `JOIN (users AS u) ON (u.roll = su.authorID)` Just another question, though. Is this the most efficient technique for handling such cases - even for a huge number of rows? – Debojyoti Ghosh Jul 02 '13 at 11:33
1

In my opinion, and in my own experience, a `JOIN` usually works faster than an `IN` clause, especially with thousands of rows, although it may depend on your query because they are not exactly the same. Generally you have to experiment and test for performance issues. – Alejandro Colorado Jul 02 '13 at 11:39
Is it advisable to use them for handling even larger number of rows, say 1 million? Or are there more alternatives? – Debojyoti Ghosh Jul 02 '13 at 11:57
I would use this `LEFT JOIN` example rather than the `IN` one, especially in those cases. – Alejandro Colorado Jul 02 '13 at 12:01
Yeah, I am already using that one in my current script. Thank you. :-) – Debojyoti Ghosh Jul 02 '13 at 12:14

zessx · Answer 2 · 2013-07-02T09:05:17.653

1

Use this query instead :

SELECT * FROM statusUpdates AS su 
WHERE su.authorID = 105090 
OR su.authorID IN (
    SELECT fr.frContactID 
    FROM friends AS fr 
    WHERE fr.frHostID = 105090
)
GROUP BY su.statusID
ORDER BY su.statusID DESC 
LIMIT 0,5

edited Jul 02 '13 at 09:05

answered Jul 02 '13 at 08:59

zessx

68,042
28
135
158

That solves the problem as of now. Thank you. But could you please help me with another thing? I need to extract data from another table **users** which contains fields such as _username_, _profileLink_, _profilePic_. I tried using `INNER JOIN (users AS u) ON (u.roll = su.authorID)` but it didn't work. In fact, using INNER JOIN prevented my query from pulling any data at all! It used to work, however, with my earlier code (the one in the original post). – Debojyoti Ghosh Jul 02 '13 at 10:08
Okay, I found the answer. The modified code now reads as `$myQuery = mysql_query("SELECT * FROM statusUpdates AS su JOIN (users AS u) ON (u.roll = su.authorID) WHERE su.authorID = 105090 OR su.authorID IN ( SELECT fr.frContactID FROM friends AS fr WHERE fr.frHostID = 105090 ) GROUP BY su.statusID ORDER BY su.statusID DESC LIMIT 0,5");` – Debojyoti Ghosh Jul 02 '13 at 10:12

score 0 · Answer 3 · answered Jul 02 '13 at 09:00

0

try this query

SELECT * FROM statusUpdates 
WHERE autherID in (select frContactID from friends where frHostID = 105090)
or authorID = 105090
LIMIT 0,5

answered Jul 02 '13 at 09:00

chetan

2,876
1
14
15

Not getting output from SQL query

3 Answers3