• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

Displaying only one category for duplicate categories

I can't remember how to do this.  I have two items in the database that are part of a category so when I list the categories that have items in them, I get a duplicate category because it has two items in it.  How can I just display the category once?

$grabItemInfo = sprintf("SELECT category_items.gID, category_items.cID, category_items.organizationID, category_items.item_name, category_items.catID, category_items.listing, category_items.viewable, category_items.image, organization.id AS orgID, organization.name AS orgName, categories.id, categories.category AS catName
                  FROM category_items
                  LEFT JOIN organization ON category_items.organizationID = organization.id
                  LEFT JOIN categories ON category_items.catID = categories.id
                  WHERE category_items.organizationID = %d AND category_items.viewable = '%s'", $org, $on);
            $itemsGrabbed = mysql_query($grabItemInfo) or die("Item grab info didn't work because: " . mysql_error());
                  echo "<h3>Available categories for {$o['name']}</h3>";
                  echo "<div class='category'>";
                        while($cat = mysql_fetch_array($itemsGrabbed)) {
                              echo "<div class='category-list'>";
                              echo (!empty($cat['catName'])) ? ("<div class='category-list-text'><a href='/classifieds.php?catID={$cat['catID']}&orgID={$cat['orgID']}'>" . $cat['catName'] . "</a></div>") : ("");
                              echo "</div>";
                        }
                  echo "<div class='spacer'></div>";
                  echo "</div>";
0
pingeyeg
Asked:
pingeyeg
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
Ray PaseurCommented:
ORDER BY and LIMIT can work together to help you out here!

Best, ~Ray
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree on the ORDER BY, but not on the LIMIT...
$grabItemInfo = sprintf("SELECT category_items.gID, category_items.cID, category_items.organizationID, category_items.item_name, category_items.catID, category_items.listing, category_items.viewable, category_items.image, organization.id AS orgID, organization.name AS orgName, categories.id, categories.category AS catName
                  FROM category_items
                  LEFT JOIN organization ON category_items.organizationID = organization.id
                  LEFT JOIN categories ON category_items.catID = categories.id
                  WHERE category_items.organizationID = %d AND category_items.viewable = '%s' 
                   ORDER BY categories.name, categories.id, category_items.item_name ", $org, $on);
            $itemsGrabbed = mysql_query($grabItemInfo) or die("Item grab info didn't work because: " . mysql_error());
                  echo "<h3>Available categories for {$o['name']}</h3>";
                  echo "<div class='category'>";
                        $prev_cat_id = "";
                        $prev_cat_name = "";
                        $end_div = "";
                        while($cat = mysql_fetch_array($itemsGrabbed)) 
                        {
                           if ($prev_cat_id != $cat['catID'])
                           {
                              $prev_cat_id = $cat['catID'];
                              $prev_cat_name = $cat['catName'];
                
                              echo $end_div;
                              echo "<div class='category-list'>";
                              echo "<div class='category-list-text'><a href='/classifieds.php?catID$prev_cat_id }&orgID={$cat['orgID']}'>{$prev_cat_name }</a></div>";
                              $end_div = "</div>";
                            } 
                            echo "item:{$cat['item_name']}<br/>"; 
                        } //while($cat = mysql_fetch_array($itemsGrabbed)) 
                  echo $end_div; 
                  echo "<div class='spacer'></div>";
                  echo "</div>";

Open in new window

0
 
dqmqCommented:
You don't have duplicate categories; you have two items in the same category. Since you are retrieving items, you see the category twice (or rather, as many times as there are items in the category).

Change your SQL to return categories, rather than category items.

Remove the join to category_items and any references to columns in that table.

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
pingeyegAuthor Commented:
dqmq: I want to display a category if it has items in it.  If the category does not have items in it then I don't want that category to be displayed.
0
 
dqmqCommented:
>I want to display a category if it has items in it


sprintf("SELECT categories.id, categories.category AS catName
                  FROM category_items
                  INNER JOIN categories ON category_items.catID = categories.id
                  WHERE category_items.organizationID = %d AND category_items.viewable = '%s'
                  GROUP BY categories.id, categories.category
                  ORDER BY categories.name, categories.id"
0
 
pingeyegAuthor Commented:
dqmq: I'm not sure I can make sense of what you are doing.  You seem to be selecting categories information from a table that is not related to categories.  ??  
0
 
dqmqCommented:
All the fields come from the categories table.   I've joined it to category_items because you only want to see the categories with dependent items.  I've grouped the results, because you only want see each cateogory once.

I needed to remove the orgId, because it is not singular for a category.

 
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
out of curiosity, anything wrong with my suggestion?
:)
0
 
pingeyegAuthor Commented:
angelIII: Honestly, it was just too much code when I know it could be a bit shorter.  That's all.  :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now