Using a MySQL query with PHP

Question

I am creating a social network in which you can follow someone or be friends with them. The data you are able to see depends on the type of relationship you have with a user. Right now I have two tables to work with. Posts and Relationships.

Posts:

| user_id | post_id | story |
-----------------------------
|  1      |  1      | text. |
-----------------------------

Relationships:

| rel_id  | user_1  | user_2| status |
--------------------------------------
|  1      |  1      |   2   |    3   |
--------------------------------------

I also have a users table but I don't think that is important here. SO, basically I want to select all of the posts from users that I am friends with or following. User_2 is always the recipient of the relationship. The numbers 1, 2 and 3 represent the "status" of the relationship. 1 being you are following the recipient, 3 being you are friends, and 4 being you are following the recipient (the only difference is that you also have a pending friend request). I set up a SELECT QUERY but it is not working right.

$query=" SELECT * FROM posts LEFT JOIN relationships ON (posts.user_id=    
relationships.user_2 AND relationships.user_1 = $user_id AND relationships.status = 4 
OR 3 OR 1)";

It selects all the posts ever, twice. Why is that? I want it to only select the posts where the user posting the post is in a relationship with me, with a status of 1, 3, or 4. What might I be doing wrong? What are other ways to do this?

Thanks in advance!

Ignacio Vazquez-Abrams · Accepted Answer · 2012-08-12 04:30:13Z

2

That's not how OR works. Perhaps you should try IN.

... relationships.status IN (4, 3, 1) ...

As for the duplication, use DISTINCT.

answered Aug 12, 2012 at 4:30

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Barmar Over a year ago

When he fixes the OR, I think the duplication will go away, so he doesn't need DISTINCT. He's getting duplicates because "OR 3 OR 1" is making it match all rows, since these are always true.

Ignacio Vazquez-Abrams Over a year ago

@Barmar: Duplication of records usually comes from the relations rather than any fixed comparisons.

Barmar Over a year ago

Good point. I think it could only happen if the same pair of users appears multiple times in the relationships table, with different status. In that case, DISTINCT is the correct solution.

John Woo · Accepted Answer · 2012-08-12 04:34:17Z

Your OR in the WHERE clause condition is not in the correct syntax. try something like this:

SELECT  * 
FROM    posts a 
            LEFT JOIN relationships b
                ON a.user_id = b.user_2 
WHERE   b.user_1 = $user_id AND 
        b.status IN (1,3,4)

I suggest that you use PDO or MYSQLi extensions.

In PDO, it could look like this:

<?php
$stmt = $dbh->prepare("SELECT   * 
                      FROM  posts a 
                           LEFT JOIN relationships b
                                ON a.user_id = b.user_2 
                     WHERE  b.user_1 = ? AND 
                            b.status IN (1,3,4)");

$stmt->bindParam(1, $user_id);
$stmt->execute();

?>

Remember to always filter your inputs especially it is used querying your database.

Collectives™ on Stack Overflow

Using a MySQL query with PHP

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related