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:
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.
Changed column data testing to $cat->$column !== ''
Changed column data testing to $cat->$column !== null && $cat->$column !== '' based on wpdb results structure.