CODEWITHSUNDEEP
phpmysqlleft-join
rohitnetgains
rohitnetgains

Reputation: 123

How to calculate rows count from left join

I am creating a google map service for different kinds of physical stores.Feedback of stores is stored in different table and other data is in different table.I created a query and it is returning perfect response to me but I need to calculte how many rows comes from feedback table so that i can count the percentage of feedback using following:

$totalfeed=ceil(($price+$value+$quality)/(3*count(rows count from feedback table)));

Here is my query 

$query = sprintf("SELECT shoptitle,logopic,complocality, profileurl, lat, lng,feedprice,feedvalue,feedquality, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM marketplace_userdata LEFT JOIN marketplace_datafeedback ON marketplace_userdata.mageuserid=marketplace_datafeedback.proownerid HAVING distance < '%s' ORDER BY distance",
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($center_lng),
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($radius));
  }

Upvotes: 0

Views: 72

Answers (2)

sosada
sosada

Reputation: 114

Add a GROUP BY condition against the first table and specify the table being counted. You can replace the asterisk with a field from the marketplace_datafeedback table: 

SELECT COUNT(marketplace_datafeedback.*), 
    shoptitle, logopic, complocality, profileurl, 
    lat, lng, feedprice, feedvalue, feedquality, 
    ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance 
FROM marketplace_userdata 
LEFT JOIN marketplace_datafeedback 
ON marketplace_userdata.mageuserid = marketplace_datafeedback.proownerid 
GROUP BY marketplace_userdata.mageuserid
HAVING distance < '%s' 
ORDER BY distance

Upvotes: 0

Mahesh
Mahesh

Reputation: 58

You can use Count() function to count the number of row of any query. 

 SELECT COUNT(*) FROM table_name;

Upvotes: 1

Related Questions