PHP MYSQL Array and sort [closed]

Question

Closed. This question needs details or clarity. It is not currently accepting answers.

Want to improve this question? As written, this question is lacking some of the information it needs to be answered. If the author adds details in comments, consider editing them into the question. Once there's sufficient detail to answer, vote to reopen the question.

Closed 10 months ago.

Improve this question

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.

spenibus · Accepted Answer · 2015-10-07 22:19:28Z

1

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.

edited Oct 7, 2015 at 22:19

answered Jul 8, 2015 at 0:04

spenibus

4,41911 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tyler Robinson Over a year ago

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

spenibus Over a year ago

Can you add print_r($cats); at the end of my code and post its output ?

spenibus Over a year ago

Exactly what I suspected. Just need to run some tests to see how to fix it.

Tyler Robinson Over a year ago

Thanks for the update, however it is still showing up. pastebin.com/gHs1XWxH

spenibus Over a year ago

Let us continue this discussion in chat.

keviveks · Accepted Answer · 2015-07-08 13:01:03Z

1

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

answered Jul 8, 2015 at 13:01

keviveks

3201 gold badge4 silver badges17 bronze badges

1 Comment

spenibus Over a year ago

I would suggest using 3 tables: "companies" (id, name), "categories" (id, name), "companies_categories" (company_id, category_id).

Collectives™ on Stack Overflow

PHP MYSQL Array and sort [closed]

2 Answers 2

update 2015-07-09 10:02 +0000

update 2015-07-09 10:49 +0000

5 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

update 2015-07-09 10:02 +0000

update 2015-07-09 10:49 +0000

5 Comments

1 Comment

Related