Confused Query Sometimes Returning Null

[mattyvx]

Hi,

 

I'm using the following query to select userdata & their reviews from my database where $ID is user ID which is pulled & santised from page URL;

 

//query to detect if user exists and gets info about details & reviews
$sql = "SELECT Count(RID) as R_Count,IfNull(Round(AVG(Rating),2),0) AS R_Average, I.Email, I.Company, I.Price, I.Car, I.Website, I.Contact, I.About, I.Areas, I.Status, I.Featured
FROM Reviews as R
INNER JOIN IUsers AS I ON R.DSID = I.ID
WHERE R.Approved='Y' AND I.ID=$ID AND I.Status='A' Limit 1";

 

Now, if I run the query and there is a match on $ID in "IUsers" (User exists) AND there also is a review about that user the query works

great....

 

R_Count R_Average Email Company Price Car Website Contact About Areas Status Featured

1 5 abc@abc 123 Ltd 23 My car www.a.com 1111222 xxxxx xxxxx A N

 

However,

 

If I run the query and there is a match on $ID (User exists) BUT there is no reviews in the reviews table..... I get the following result;

 

R_Count R_Average Email Company Price Car Website Contact About Areas Status Featured

0 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

 

 

Userdata returns as "NULL" even if the user exists.... I want the reviews to be 0 and the user data to still display!!!!

 

Now the real confusing thing is I have the exact same query running fine on another site and it pull userdata if there are reviews or aren't reviews.... :S

 

Additional Info:

• Server: Localhost via UNIX socket
  
• Server version: 5.1.65-community-log
  
• Protocol version: 10
  
• MySQL charset: UTF-8 Unicode (utf8)
  
• PHPAdmin : Version information: 3.4.11.1
  

Reviews fields:

RID = Unique Review ID

DSID = UserID

Review = Text

Rating = 1-5

Date = Date

Approved = Y or N

Edited October 6, 2012 by mattyvx

[Barand]

change the FROM clause around and use LEFT JOIN

 

SELECT ....
FROM IUsers AS I 
LEFT JOIN Reviews as R ON R.DSID = I.ID AND R.Approved='Y' 
WHERE I.ID=$ID AND I.Status='A' 
Limit 1

Edited October 6, 2012 by Barand

[mattyvx]

OK nearly there... I've changed the SQL round to

 

SELECT I.Email, I.Company, I.Price, I.Car, I.Website, I.Contact, I.About, I.Areas, I.Status, I.Featured, Count( R.RID ) AS R_Count, IfNull( Round( AVG( R.Rating ) , 2 ) , 0 ) AS R_Average
FROM Instructors AS I
LEFT JOIN Reviews AS R ON I.ID = R.DSID
WHERE I.ID =$ID
AND I.Status = 'A'
AND R.Approved = 'Y'
LIMIT 1

 

But it still shows NULL.....

 

I can get it to work If I remove

AND R.Approved = 'Y'

from the above HOWEVER..... when there is a review it includes reviews that are not approved.

 

How / where can I add the clause AND R.Approved = 'Y' so it only counts & averages approved reviews?

 

P.S @Barand I'm from Cheshire too!

Edited October 6, 2012 by mattyvx

[Barand]

You must have read it before my edit. Make the approved condition part of the join instead of part of the WHERE clause

[mattyvx]

Thanks - It's solved...

 

I can't work out why it works fine on my other 3 sites which run EXACTLY the same code & datatables.....

[Barand]

I can't work out why it works fine on my other 3 sites which run EXACTLY the same code & datatables.....

 

Could it be that you just hadn't noticed that it wasn't working correctly?

[mattyvx]

i double/triple checked the databases and the code testing it on over 20 different member profiles both with and without reviews (for a painfull 3 hours last night).... the original code works fine on 3/4 of the sites.

 

The 4th site is new but the other 3 have been working OK for > 2 years

 

I've updated them all to the new version now and rechecked, they still work.

Edited October 6, 2012 by mattyvx

[fenway]

It's important to recognize that checking a column that's been NULL-ed out by a non-matching LEFT JOIN can't ever succeed.