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>";
LVL 1
pingeyegAsked:
Who is Participating?
 
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
 
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.