php - 在while循环中使用JOIN sql查询

Question

I have this query:

    $r = $dbh->prepare("
            SELECT ur.user, urs.*
            FROM xeon_users_rented as ur 
              JOIN xeon_users_rented_stats as urs ON ur.user_by=urs.urs_user 
            WHERE ur.user_by=:user 
            "
    );
    $r->bindParam(':user', $userdata['username']);
    $r->execute();

Which I am looping:

while($referralData=$r->fetch()):

echo $referralData['id'];

endwhile;

My problem is, that the loop doesn't run. If I place anything inside the loop, is it not shown.

The original query (without JOIN) looks like this:

$r = $dbh->prepare("SELECT * FROM xeon_users_rented WHERE user_by=:user");

What is wrong?

Answer 1

Your user needs to exist in both tables.

When you join xeon_users_rented and xeon_users_rented_stats on ur.user_by=urs.urs_user, it means that you will combine the rows from both the tables when ur.user_by=urs.urs_user. So the combined row will both have the same user. Since your user only exists in 1 table, when joining, the row in xeon_users_rented with your user cannot find a row in the other table to combine with since the other table doesn't have the user.

For example:

TableA

user      age
------------------
john      20
ricky     24
paul      30

TableB

user      someStat
------------------
john      100
paul      200
paul      300

If we join TableA with TableB on TableA.user = TableB.user, we'll get

TableA.user     TableA.age    TableB.user    TableB.someStat
---------------------------------------------------------------
john            20            john           100
paul            30            paul           200
paul            30            paul           300

For every row in TableA, it will find all rows in TableB with the same user and combine the rows in the resulting table. Since Ricky is not in TableB, he is not in the results.

Now if we do a LEFT JOIN, this guarantees all the rows for the table on the left side of the join (TableA in this case), and will fill in data on the right side where available.

TableA.user     TableA.age    TableB.user    TableB.someStat
--------------------------------------------------------------
john            20            john           100
ricky           24            NULL           NULL
paul            30            paul           200
paul            30            paul           300

Now the result includes Ricky, but since Ricky is not in TableB, the columns corresponding to TableB are filled with NULLs