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.

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.....

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!

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