php - using JOIN sql query in while loop

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?

Have you tried running the query directly in your database to verify that you're getting the result you expect? — Matt Browne
– Matt Browne, Commented Aug 25, 2014 at 14:33
Run the query directly in your database as @MattBrowne mentioned, the query probably isn't returning anything. — Edson Horacio Junior
– Edson Horacio Junior, Commented Aug 25, 2014 at 14:35
Please see my updated question. I have included the original query (without JOIN function) Maybe that can help. — oliverbj
– oliverbj, Commented Aug 25, 2014 at 14:36

Rickkwa · Accepted Answer · 2014-08-25 15:16:51Z

2

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

edited Aug 25, 2014 at 15:16

answered Aug 25, 2014 at 14:53

Rickkwa

2,3015 gold badges25 silver badges37 bronze badges

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

7 Comments

oliverbj Over a year ago

Is there anyway I can use the join function, if the user doesn't exist in the other table? (xeon_users_rented_stats) It only exist in some cases.

Rickkwa Over a year ago

Say it exists in rented but not in rented_stats. What do you expect to see in the result?

oliverbj Over a year ago

I expected to see records from "rented" in the loop. And it it existed in "rented_stats", then I would be able to use those records.

Rickkwa Over a year ago

Try using a LEFT JOIN.

oliverbj Over a year ago

I am very new to the JOIN functions, care to show an example?

|

Collectives™ on Stack Overflow

php - using JOIN sql query in while loop

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related