current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them, it only takes a minute:
up vote 3 down vote favorite

So, I have a database table that has up to 8 separate category options for each customer.

Example:

company_name | category_1 | category_2 | category_3
****************************************************
My Company   | computers  | parts      | electronics

ect... up on up to eight category options. What I need to do is get the categories in a list and list all companies with that category under each category item. I have the categories into an array, but I get all of them in a foreach loop which will give me duplicates. I don't want to list the duplicates, I just want to list them once and place all companies under that category.

Like:

Computers
Company Name

Parts
Company Name

Electronics
Company Name

ect....

My code currently:

$sql = $wpdb->get_results( "SELECT * FROM $table_name");

echo '<ul>';

foreach ($sql as $cat){
    $cats[0] = $cat->category_1.' '.$cat->category_2.' '.$cat->category_3.' '.$cat->category_4.' '.$cat->category_5.' '.$cat->category_6.' '.$cat->category_7.' '.$cat->category_8;
    $totalCats = $cats[0];
    echo '<li>'.$totalCats.'</li>';
}

echo '</ul>';
}// End of foreach loop

This will then give me the following:

  • Computers Parts
  • Computers Electronics
  • Electronics Parts

ect... for each database entry depending on how many categories that company chose.

Any help would be appreciated!

share|improve this question

2 Answers 2

up vote 1 down vote accepted

I don't have a dataset to test it but this should work. We create a multidimensional array from the dataset like this:

[comp][0] = company A
      [1] = company B
      ...
[elec][0] = company A
      [1] = company C
      ...
[part][0] = company Y
      [1] = company Z
      ...

Then we iterate over it to print it out.

<?php
$cats = array();

// loop through rows
foreach($sql as $cat) {

   // loop through row categories
   for($i=1; $i<=8; ++$i) {

      // column name
      $column = 'category_'.$i;

      // column has data
      // $cats[category][irrelevant index] = company name
      if($cat->$column !== null && $cat->$column !== '') {
         $cats[$cat->$column][] = $cat->company_name;
      }
   }
}

// sort categories
ksort($cats);

echo '<ul>';

// loop though categories
foreach($cats as $catname=>$cat) {

   // sort companies in category
   sort($cat);

   // category name
   echo '<li>'.$catname.'</li><ul>';

   // loop through companies
   foreach($cat as $company) {
      echo '<li>'.$company.'</li>';
   }

   echo '</ul>';
}
echo '</ul>';
?>

It would probably be best to store categories in a separate table and start from there, using a JOIN to retrieve associated company names. It would also require less code to print it out the way you want it.

update 2015-07-09 10:02 +0000

Changed column data testing to $cat->$column !== ''

update 2015-07-09 10:49 +0000

Changed column data testing to $cat->$column !== null && $cat->$column !== '' based on wpdb results structure.

share|improve this answer
    
That's almost right on the money! I tried that and I got the categories listed out with all the companies under them, but above that I get the companies listed as many times as they are in categories. See example: pastebin.com/Wzxvwduw – Tyler Robinson Jul 9 at 6:06
    
Can you add print_r($cats); at the end of my code and post its output ? – spenibus Jul 9 at 9:48
    
pastebin.com/58xnZEth – Tyler Robinson Jul 9 at 9:54
    
Exactly what I suspected. Just need to run some tests to see how to fix it. – spenibus Jul 9 at 9:59
    
Added code fix. – spenibus Jul 9 at 10:07
up vote 1 down vote

I think you need to create separate table for categories 

For eg:-

table companies

company_id | company_name

table categories

category_id | company_id | category_name

So your query will comes like

$query = "SELECT * FROM companies comp RIGHT JOIN categories cate ON comp.company_id=cate.company_id";

With this you will get company details and array of categories, hope this is the better way to do this

share|improve this answer
1  
I would suggest using 3 tables: "companies" (id, name), "categories" (id, name), "companies_categories" (company_id, category_id). – spenibus Jul 8 at 19:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.