current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 0 down vote favorite

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?

share|improve this question
    
@SalmanA Yes :) – oliverbj Aug 25 '14 at 14:30
    
Do a var_dump($->fetch()) to us! – Elias Soares Aug 25 '14 at 14:32
2  
Have you tried running the query directly in your database to verify that you're getting the result you expect? – Matt Browne Aug 25 '14 at 14:33
    
@jeroen the loop isn't entered. (Edited my question, sorry) – oliverbj Aug 25 '14 at 14:34
    
Run the query directly in your database as @MattBrowne mentioned, the query probably isn't returning anything. – Edson Horacio Junior Aug 25 '14 at 14:35
up vote 2 down vote accepted

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

share|improve this answer
    
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. – oliverbj Aug 25 '14 at 14:55
    
Say it exists in rented but not in rented_stats. What do you expect to see in the result? – Rickkwa Aug 25 '14 at 14:59
    
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. – oliverbj Aug 25 '14 at 15:02
    
Try using a LEFT JOIN. – Rickkwa Aug 25 '14 at 15:06
    
I am very new to the JOIN functions, care to show an example? – oliverbj Aug 25 '14 at 15:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.