[SOLVED] PHP Combine Queries

[amwd07]

I really hope someone can help me here please, all I need to do is combine the 2 queries

joining the ratings query into $rs1 which must include the buildsql!

 

mysql_select_db($database_connDW, $connDW);
$query_rs1 = "SELECT * FROM restaurants WHERE ".$buildsql." AND onhold = '0' OR (accountname LIKE '$outlet' AND onhold = '0') OR (twn LIKE '$town' AND onhold = '0') ORDER BY old_image DESC";
$rs1 = mysql_query($query_rs1, $connDW) or die(mysql_error());
$row_rs1 = mysql_fetch_assoc($rs1);
$totalRows_rs1 = mysql_num_rows($rs1);

mysql_select_db($database_connDW, $connDW);
$query_overall_rate = "SELECT dine_id, AVG(total_value) FROM ratings GROUP BY dine_id";
$overall_rate = mysql_query($query_overall_rate, $connDW) or die(mysql_error());
$row_overall_rate = mysql_fetch_assoc($overall_rate);
$totalRows_overall_rate = mysql_num_rows($overall_rate);

[aschk]

Firstly define for me what your relationship between the two tables is,  and on what key.

e.g.

restaurants ==== has many ===> ratings

or

restaurants ==== has 1 ===> rating

 

and

restaurant has rating_id

or rating has restaurant_id

or (some intermediary table)

[amwd07]

OK currently the ratings query is working to find the average, if I can combine the 2 queries I can use it in the repeat region for example 20 outlets listed on the page would work out the average votes for each outlet

 

the relationship for the 2 tables is dine_id

[aschk]

Ok so assuming what you're telling me is correct, in that the dine_id is the ID index/key of the restaurant in question, your SQL shoud look something like this :

SELECT rest.*
          ,AVG(rat.total_value) as total_value
FROM restaurants rest
JOIN ratings rat ON rat.dine_id = rest.id
WHERE ".$buildsql." 
AND rest.onhold = '0' 
OR (accountname LIKE '$outlet' AND rest.onhold = '0') 
OR (rest.twn LIKE '$town' AND rest.onhold = '0') 
GROUP BY rest.id
ORDER BY rest.old_image DESC

 

Please check that the column is question is rest.id (line 4 & 9). I'm assuming you've called the primary key of your restaurant "id" but this might not be the case.

[amwd07]

I had to change the code slightly the average is correct but only the outlets rated are showing if i empty the ratings table no results are shown ???

 

mysql_select_db($database_connDW, $connDW);
$query_rs1 = "SELECT restaurants.*,AVG(ratings.total_value) as total_value
FROM restaurants JOIN ratings ON ratings.dine_id = restaurants.dine_id
WHERE ".$buildsql." AND restaurants.onhold = '0' OR (accountname LIKE '$outlet' AND restaurants.onhold = '0') OR (restaurants.twn LIKE '$town' AND restaurants.onhold = '0') GROUP BY ratings.dine_id ORDER BY restaurants.old_image DESC";
$rs1 = mysql_query($query_rs1, $connDW) or die(mysql_error());
$row_rs1 = mysql_fetch_assoc($rs1);
$totalRows_rs1 = mysql_num_rows($rs1);

[aschk]

Ah yes, because it's essentially an inner join that is being performed, if there doesn't exist a row in the right hand table (ratings) for a row in the left hand table no row will be shown.

Change it to a LEFT JOIN

[amwd07]

OK I have changed to LEFT JOIN and now there is only 1 record displayed ???

[aschk]

Any chance you could post your table structures + some sample data so I can see where this is going wrong?

[amwd07]

I had just noticed my problem it works now just need to test it in the repeat region.

 

I changed GROUP BY ratings.dine_id  to    restaurants.dine_id   

[amwd07]

ahhhhhh still have a problem

 

I need rattings.dine_id because this is how the average is worked out for each rating

how do I have both withou changing the ammount of records dispalyed ???

 

Just when I thought it was going to work 

[aschk]

I think there's something funny about your structure + data, nevertheless here is a modified version of what I was doing :

 

SELECT rest.*, x.total_value
FROM restaurants rest
JOIN 
  (SELECT IFNULL(AVG(total_value),0) as total_value
     FROM ratings
     GROUP BY dine_id
  ) x ON x.dine_id = rest.dine_id
WHERE ".$buildsql." 
AND rest.onhold = '0' 
OR (accountname LIKE '$outlet' AND rest.onhold = '0') 
OR (rest.twn LIKE '$town' AND rest.onhold = '0') 
ORDER BY rest.old_image DESC

[amwd07]

this works for me i have also added the total votes

couldn't of done it without you thanks 

 

SELECT restaurants.*,AVG(ratings.total_value) as avg_total_value, AVG(ratings.total_votes) as avg_total_votes FROM restaurants LEFT JOIN ratings ON ratings.dine_id = restaurants.dine_id

WHERE ".$buildsql." AND restaurants.onhold = '0' OR (accountname LIKE '$outlet' AND restaurants.onhold = '0') OR (restaurants.twn LIKE '$town' AND restaurants.onhold = '0') GROUP BY restaurants.dine_id ORDER BY restaurants.old_image DESC