php - count number of property available for member according to there zip code

Question

i have two table

1) member

• member_id
• name
• address
• zip

2) property

• property_id
• property_name
• property_zip

i want to list all the member zip (without duplicate) and show the count of property for each zip code in a table

---

Member Zip | Property Count

95674 | 50

95852 | 90

95614 | 0

95070 | 5

its have multiple member with same zip code and also multiple property with same zip code

i don't want any repeat on the Member Zip output column.

i have 5 member with 92550 & 10 member with 82475, i have to show

92550
82475

& on property table i have 10 with zipcode 82475 and 4 with zip code 92550.

Final O/P

92550 (4)
82475 (10)

can anyone help me to find it ?

Answer 1

Try this

SELECT a.zip, COUNT(b.propety_zip)
FROM member a
LEFT JOIN propety b
ON a.zip = b.propety_zip
GROUP BY a.zip

Edit

I used your code

$checke = "SELECT a.zip, COUNT(b.property_zip) AS property_count FROM member a LEFT JOIN property b ON a.zip = b.property_zip GROUP BY a.zip";
$rsd = mysql_query($checke) or die(mysql_error()); ?> 

<table width="600" style="font-size:12px; border:1px solid #000000; text-align:center;" cellpadding="0" cellpadding="0">
  <tr> 
    <td><strong>Zip</strong></td> 
  </tr>
  <?php while($row = mysql_fetch_array($rsd)) { ?> 
  <tr> 
    <td><h2><?php echo $row['zip'] . " (" . $row['property_count'] .")"; ?></h2></td> 
 </tr> 
 <?php }  ?>