PHP MYSQL Array and Sorting
So, I have a database table that has up to 8 separate categories for each client.
Example:
company_name | category_1 | category_2 | category_3
****************************************************
My Company | computers | parts | electronics
ect ... up the eight category parameters. What I need to do is get the categories listed and list all the companies with that category under each category. I have categories in an array, but I am getting all of them in a foreach loop which will give me duplicates. I do not want to list duplicates, I just want to list them once and place all companies under this category.
how
Computers
Company Name
Details
Company Name
Electronics
Company Name
ECT ....
My code:
$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 give me the following:
- Computer components
- Computers Electronics
- Electronics parts
ect ... for each database record depending on how many categories the company has selected.
Any help would be appreciated!
source to share
I don't have a dataset to test, but this should work. We create a multidimensional array from a dataset like this:
[comp][0] = company A
[1] = company B
...
[elec][0] = company A
[1] = company C
...
[part][0] = company Y
[1] = company Z
...
We then 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 better to keep the categories in a separate table and start from there, using JOIN
to get related company names. It also takes less code to print it out the way you want it to.
update 2015-07-09 10:02 +0000
Changed testing of these columns to $cat->$column !== ''
update 2015-07-09 10:49 +0000
Changed testing of these columns based $cat->$column !== null && $cat->$column !== ''
on the wpdb results structure.
source to share
I think you need to create a 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 get company details and many categories, hopefully this is the best way to do it.
source to share