displaying active results

[ran_dizolph]

Hi there.

Okay, here's my issue;
I'm doing an online classifieds section for a magazine.

The table is set up like this;
id
category
subcategory
country
active
adtext

I need it to display the list of categories (once each), with one of each subcategory displayed under that. In brackets next to each subcategory, I need a number (a count) of the 'active' ads per subcategory.

Here's the page...it almost works, except as new ads are added, it duplicates the subcategories.

Here's the code i'm using;
[php]
$sql = mysql_query ("SELECT * FROM classifieds WHERE country = 'Canada' ORDER BY category");

while($cat=mysql_fetch_assoc($sql)) {

if($prevcat!=$cat['category']) {
echo '<h2>'.$cat['category'].'</h2>';
$prevcat=$cat['category'];
}

$resultCount = mysql_query( "SELECT COUNT(*) from classifieds WHERE active = 'Y' AND country = 'Canada' AND subcategory='".$cat['subcategory']."'")
or die(mysql_error());
$numCount = mysql_result( $resultCount, 0);

echo '<p><a href="classifieds_canada_detail.php?subcategory=' .$cat['subcategory'].'">' .$cat['subcategory']. '</a>&nbsp;(' .$numCount. ')</p>' ;

}
[/php]

Any help would be immensly appreciated...I"ve been bashing my head off my desk all week to get this right!

[edited by: jatar_k at 5:59 pm (utc) on April 25, 2006]
[edit reason] removed url [/edit]

[ChadSEO]

ran_dizolph,

Just a heads up, posting a link to a personal site is against the TOS.

The problem is that your first query is returning every single record from the table, instead of just a listing of unique categories and subcategories. The following might work a little better:

$sql = mysql_query ("SELECT category,subcategory FROM classifieds WHERE country = 'Canada' GROUP BY category,subcategory ORDER BY category");

Chad

[ran_dizolph]

K...I'll give that a whirl.

Wasn't aware that I couldn't post a link...my bad!

Thanks!

[ran_dizolph]

yeah...that was the problem.

Thanks a bunch!