Displaying number of products in a category - MySQL Query

Hi,  I have a table in MySQL with properties in it with fields such as house.roadname & house.propertytype.

When a user searches my database for a road name, i'd like the query to group by house.propertytype and also state how many results per propertytype in brackets (so I assume it passes this value to a php variable?)  Lets say the user searches for Windsor Road, I'd like the results to look like this:

Windsor Road (Total 67 Properties)
     Detached House (18)
     Semi-detached House (38)
     Detached Bungalow (9)
     Semi-detached Bungalow (2)

Can someone enlighten me on the query as I'm no expert but the theory seems so simple!
Caldera_MikeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you mean:
select propertytype, count(*)
  from yourtable
where roadname = 'abc'
group by propertytype


the rest is displaying the results...

Open in new window

Caldera_MikeAuthor Commented:
Hi, y but how do i pass the count result per propertytype to php to display it?  Thats the bit i'm stuck on.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, a full sample:
$roadname = mysql_real_escape_string("test road");
$sql = "select propertytype, count(*) c "
  from yourtable
where roadname = '$roadname'
group by propertytype ";

$rows = mysql_execute($sql);

$total_found = 0;
$output = "";

while ($row = mysql_fetch_assoc($rows))
{
  $property = $row["propertytype"];
  $count = $row["c"];

  $total_found += $count;

  $output .= "<tr><td>$property</td><td>$c</td></tr>"; 
}

print "<table><tr><th>$roadname</th><th>$total_found</th></tr>$output</table>";

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Caldera_MikeAuthor Commented:
cool, going to try this out now....
Guy Hengel [angelIII / a3]Billing EngineerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.