left Join problem

[proctk]

HI

 

The below code is supposed to bring together multiple tables but for some reason its retuns repeat results for the query

 

based on my test data it should return DylanJennifer

 

but its returning DylanJenniferTylerJennifer

 

any ideas why

 

thank you

		$query_get_Birthdays = ("SELECT * FROM users LEFT JOIN parent ON parent.owner_id = users.user_id LEFT JOIN children ON children.owner_id = users.user_id LEFT JOIN sibling ON sibling.owner_id = users.user_id WHERE month(childdob) = '3' OR month(spousedob) = '3' AND users.user_id = '$user_id' OR month(siblingdob) = '3' AND sibling.owner_id ='$user_id' OR month(parentdob) = '3' AND parent.owner_id = '$user_id'");

$get_birthdays = mysql_query($query_get_Birthdays) or die ("Invalid query". mysql_error());

while($birthdays = mysql_fetch_assoc($get_birthdays)){
echo $birthdays['childfirstname'];
echo $birthdays['spousefirstname'];
echo $birthdays['parentfirstname'];
echo $birthdays['siblingfirstname'];
}

[btherl]

Can you reformat your output a little?  I can't tell if you are getting more rows or if you are getting more columns than you expected.

[spfoonnewb]

Meh too many join's for me.. Haven't used them in a while.. I did format it to take a look, it's probably one of those or's

 

<?php
$q = mysql_query("SELECT * FROM `users` LEFT JOIN `parent` ON `parent.owner_id` = 'users.user_id' LEFT JOIN `children` ON `children.owner_id` = 'users.user_id' LEFT JOIN `sibling` ON `sibling.owner_id` = 'users.user_id' WHERE `month(childdob)` = '3' OR `month(spousedob)` = '3' AND `users.user_id` = '".$user_id."' OR `month(siblingdob)` = '3' AND `sibling.owner_id` ='".$user_id."' OR `month(parentdob)` = '3' AND `parent.owner_id` = '".$user_id."'") or die ("Invalid query". mysql_error());
while($b = mysql_fetch_assoc($q)){
echo $b['childfirstname'].$b['spousefirstname'].$b['parentfirstname'].$b['siblingfirstname'];
}
?>

[Barand]

When using OR condtions, use () to define the logic

 

eg  A AND B OR C

 

is it

 

A AND (B OR C)

 

or is it

 

(A AND B) OR C

 

Also, the WHERE conditions below are superfluous as you are matching on the id

  AND sibling.owner_id ='$user_id'

  AND parent.owner_id = '$user_id'

 

Could be other problems but, as there is no indication of which tables all those xxxdob fields come from, I cannot be sure

[proctk]

Thank you for the replies, I'm having a hard time working with where to place the () around the ORS

 

I'll explain the xxdob and what tables they are from

 

 

childdob is from the table childen

spousedob is from the table users

siblingdob is from the table sibling

parentdob is from the table parent.

 

my goal is to get all results where the month is = 4(April)

[proctk]

the code looks like this with many edits since my last post

 

$query_get_Birthdays = ("SELECT * FROM users LEFT JOIN (parent, sibling, children) ON (parent.owner_id = users.user_id AND children.owner_id = users.user_id AND sibling.owner_id = users.user_id) WHERE month(children.childdob) = '3' OR month(users.spousedob) = '3' OR month(sibling.siblingdob) = '3' OR month(parent.parentdob) = '3' ");

 

its returning the same results over and over

[Barand]

Is this any better?

$query_get_Birthdays = "SELECT * FROM users 
            LEFT JOIN parent ON parent.owner_id = users.user_id AND month(parent.parentdob) = '3' 
            LEFT JOIN sibling ON sibling.owner_id = users.user_id AND month(sibling.siblingdob) = '3' 
            LEFT JOIN children ON children.owner_id = users.user_id AND month(children.childdob) = '3'  
            WHERE  (month(users.spousedob) = '3')
                OR (parent.owner_id IS NOT NULL) 
                OR (sibling.owner_id IS NOT NULL)
                OR (children.owner_id IS NOT NULL)";

 

Change '3' to '4' if you want April

[proctk]

thank you for the help it works excellent,  The April / March thing was a small typo in my post.

 

So I understand going forward and so I can write better mysql Statements from a formating stand point a line break show appear for each new mysql function is OR AND SELECT etc.

 

also is there a tutorial or a witting on when and where to place () around statements

[Barand]

The line breaks are optional. I prefer to do it that way to make it easier to read. As for the (), certainly use them with the And/OR combinations as I mentioned earlier, apart form that it's an aid to readability again.

 

I would recommend using the join syntax that I used.

 

Even if you are just using normal JOIN it's better to have

 

SELECT x,y,z FROM a JOIN b ON a.id = b.id WHERE something

rather than

SELECT x,y,z FROM a,b  WHERE something AND a.id = b.id

 

as it defines the relationship structure separate from the selection criteria and IMO afain it's easier to understand what's going on.

 

Also, I'd normally use table aliases (sometimes they are necessary) as an aid to readability - the query is less cluttered. If I hadn't been copy/pasting your code I'd have written it like this

SELECT * FROM users u
LEFT JOIN parent p ON p.owner_id = u.user_id AND month(p.parentdob) = '3' 
LEFT JOIN sibling s ON s.owner_id = u.user_id AND month(s.siblingdob) = '3' 
LEFT JOIN children c ON c.owner_id = u.user_id AND month(c.childdob) = '3'  
WHERE  (month(u.spousedob) = '3')
    OR (p.owner_id IS NOT NULL) 
    OR (s.owner_id IS NOT NULL)
    OR (c.owner_id IS NOT NULL)

 

One last thing - avoid SELECT * unless you really do need to pull every column. Specify the columns you need

 

SELECT u.name, u.spousedob, .... FROM ...

 

The less data you fetch the more efficient the query, and again, it let's the reader see what the query is really doing. (If you had a 2Mb blob image in each record you bee pulling those too in every record even though the birthdate query doesn't need them.)

 

Hope that jelps

[proctk]

Thank you for taking time for the explanation. One last question why did you include this " IS NOT NULL" for each id

 

 

 

 

[Barand]

When using

 

user LEFT JOIN parent

 

if no match is found in the parent table then all result values from the parent table are NULL. So if parent.id value IS NOT NULL a matching record was found.

 

You are looking for users where

spouse dob in March OR there was a match against any of the other tables with dob in March.

 

Also note, because data may or may nor be present in the RIGHT table (parent in the example) the condition

month(p.parentdob)=3 was moved to the join condition and not left in the WHERE clause. This was why I wanted to know which tables they were in when I first posted.

 

 

[proctk]

I have run into a little snag

 

Her is a a query that I created with what I have learned from this post

 

 

the problem with this query is if no records are found 'mysql_query_row_num' will return a result of 8 based on data in my tables.  The same problem holds true for the query that was created with this form.  The interesting thing is if I echo a value for a row three should be no results nothing is returned but the mysql_query_row_num still retuns a value

 

any thoughts why this is happening

  <?php $query_get_buddyPhoto = ("SELECT * FROM buddylink b
								 LEFT JOIN users u ON b.buddy_id = u.user_id
								 LEFT JOIN image_files img ON  img.user_id = b.buddy_id AND album = 'Member'
								 WHERE b.owner_id = '$id' ORDER BY b.added"); 
$get_buddyPhoto = mysql_query($query_get_buddyPhoto) or die ("Invalid query". mysql_error());

while($buddyPhoto= mysql_fetch_assoc($get_buddyPhoto)){	

[proctk]

I must add that if I set a limit in the query it messes things all up

[Barand]

"mysql_query_row_num" not in my manual. Is it one of your own functions?

[darkfreaks]

dont ya mean mysql_num_rows ?

[proctk]

that was a typo on my part in the post.

[proctk]

the typo was not just in the post but in my code that fixed it ops, thanks