CODEWITHSUNDEEP
phpsqljoin
Darth Mikey D
Darth Mikey D

Reputation: 109

Inner Join on 3 different tables

I have 3 different tables I need to pull data from. What needs to happen is we pull posts from the posts table where the user who made the post is set as a public user, or if set as private, users they grant access to can view their posts.

Here are the tables:

user: id, username, name, password, email, private
posts: id, title, user, date, state
privateallowed: id, privateuser, alloweduser

and here is my code where I try to get the records:

$username = $_COOKIE['username'];
$sqlposts = "select p.id, u.id, u.private, a.privateuser, a.alloweduser from user u 
                inner join posts p on u.id = p.user 
                    inner join privateallowed a on a.privateuser = u.id and a.alloweduser = " . $username . " 
                        where u.private='public' or (a.privateuser=p.user and a.alloweduser=" . $username . ")";
$resultpost = mysqli_query($dbcon, $sqlposts);
$numrows = mysqli_num_rows($resultpost);

Unfortunately, no records are being returned. Any help or ideas are appreciated.

Upvotes: 1

Views: 74

Answers (2)

Darth Mikey D
Darth Mikey D

Reputation: 109

I did find an answer, though I'm sure it isn't a perfect one. What I did was I broke the statements into separate select statements, and then embed one in the where clause, as follows:

 $sqlposts = "SELECT p.id, u.id, u.private 
        FROM user u 
        INNER JOIN posts p ON u.id = p.user 
        WHERE u.private='public' or p.user in 
        (
            select a.privateuser 
            from privateallowed a 
            inner join posts p on p.user = a.privateuser 
            where a.alloweduser=" . $username . "
        )";

Me and another person who is pretty good with sql looked at this for a couple of hours, and we just couldn't get the select to return the correct results. We tried the following, but it returned each public user 3 times for some reason:

SELECT p.id, u.id, u.private 
        FROM user u 
        INNER JOIN posts p ON u.id = p.user 
        INNER JOIN privateallowed a on a.alloweduser=24
        WHERE u.private='public' or p.user = a.privateuser

I appreciate everyone's help on this. If anyone has any other ideas, I appreciate them. I'm working on a web site that I am hoping will grow, and I want to make sure the page loads as fast as possible.

Upvotes: 0

Brien Foss
Brien Foss

Reputation: 3367

For what it's worth (@pgngp commented about needing single quotes around '" . $username . "'):

$username = $_COOKIE['username'];
$sqlposts = "SELECT p.id, u.id, u.private, a.privateuser, a.alloweduser 
             FROM user u 
             INNER JOIN posts p ON
                   u.id = p.user 
             INNER JOIN privateallowed a ON
                   a.privateuser = u.id AND a.alloweduser = '" . $username . "' 
             WHERE u.private='public';
        $resultpost = mysqli_query($dbcon, $sqlposts);
        $numrows = mysqli_num_rows($resultpost)";

Also note, I've removed the following from your WHERE condition because it is redundant due to previous INNER JOIN's. We can only infer this because you've used INNER JOIN, had you used LEFT JOIN you would need to leave the condition in as-is.

. . .or (a.privateuser=p.user and a.alloweduser=" . $username . ")"

Explanation:

  • INNER JOIN posts p ON u.id = p.user
    • So we have determined that u.id = p.user
  • INNER JOIN privateallowed a ON a.privateuser = u.id
    • So we infer that a.privateuser = p.user
  • AND a.alloweduser = '" . $username . "'
    • This covers the remaining condition in your WHERE

Upvotes: 1

Related Questions