CODEWITHSUNDEEP
mysql
skolind
skolind

Reputation: 1754

mySQL - WHERE, AND/OR selecting

I'm trying to select all posts from database, (where an ID is = an id from another table, which connects the people i'm in network with) OR (to select posts where i'm the writer of the post.). Actually i'm trying to get posts from people in my network AND my own.

I've found out how to do this, but when using the OR clause it's selecting the rows twice. So for example, if I write a post it's duplicated when echo'ed out from database.

My mySQL:

$sId = validate::intValidate($_SESSION['userId']);
$sql = "SELECT * FROM networkPost
            INNER JOIN authentication
                ON networkPost.FK_networkYou=authentication.userId
            INNER JOIN network
                ON networkPost.FK_networkYou=network.networkYou
            WHERE networkMe=$sId AND FK_networkYou=networkYou OR networkYou=$sId
            ORDER BY networkPostId DESC";

networkYou is: The userId of the post writer.

So how do I select my own posts and my networks posts?

Upvotes: 2

Views: 130

Answers (4)

user1237385
user1237385

Reputation:

Try this : 

$sId = validate::intValidate($_SESSION['userId']);
$sql = "SELECT DISTINCT * FROM networkPost np,authentication at,network n
            WHERE
                 np.FK_networkYou=n.networkYou AND
                 np.FK_networkYou=at.userId AND
                 np.FK_networkYou in(SELECT DISTINCT np.FK_networkYou FROM networkPost np,authentication at,network n
                          WHERE np.FK_networkYou=n.networkYou AND
                                np.FK_networkYou=at.userId AND
                                (networkMe=$sId AND FK_networkYou=networkYou) OR networkYou=$sId
                                 ORDER BY networkPostId DESC");

Upvotes: 0

skolind
skolind

Reputation: 1754

I found a solution on my problem. I wanna say thanks to Starx, which I might have pissed off, sorry! I'll make your answer the right one.

But here is my solution: 

SELECT DISTINCT networkPost.*, authentication.* FROM networkPost
            INNER JOIN authentication
                ON networkPost.FK_networkYou=authentication.userId #Så jeg kan skrive hvem posteren er.
            LEFT JOIN network
                ON networkPost.FK_networkYou=network.networkYou
            WHERE networkMe=$sId OR FK_networkYou=$sId
            ORDER BY networkPostId DESC

Upvotes: 1

Starx
Starx

Reputation: 78981

You have few errors in your code

validate::intValidate($_SESSION['userId']); //missing square bracket

And the WHERE part of the query is missing brackets

WHERE (networkMe=$sId AND FK_networkYou=networkYou) OR networkYou=$sId

Upvotes: 3

Taryn
Taryn

Reputation: 247690

looks like you are missing '()'

$sId = validate::intValidate($_SESSION['userId');
$sql = "SELECT * FROM networkPost
            INNER JOIN authentication
                ON networkPost.FK_networkYou=authentication.userId
            INNER JOIN network
                ON networkPost.FK_networkYou=network.networkYou
            WHERE (networkMe=$sId AND FK_networkYou=networkYou) OR networkYou=$sId
            ORDER BY networkPostId DESC";

Upvotes: 1

Related Questions