Skip to content

Instantly share code, notes, and snippets.

@kirasiris
Forked from Jakeii/controller.php
Created April 27, 2018 10:08
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save kirasiris/7bfc8d83c72ed26ad96ca0ee71f4b4ba to your computer and use it in GitHub Desktop.
Save kirasiris/7bfc8d83c72ed26ad96ca0ee71f4b4ba to your computer and use it in GitHub Desktop.
Get categories and subcategories in one query, seen many solutions that get the categories and for each category check for subcategories. Uses codeigniter framework, fairly easy to convert to vanilla php if you need. Probably a better storage/display solution than the strange array that I used.
<?php
$cats_result = $this->model->get_categories($swapid);
if ($cats_result->num_rows() > 0) {
$cat_index = $sub_index = $row_index = 0;
do {
$cats[$cat_index]['cat'] = array(
'name' => $cats_result->row($row_index)->cat_name,
'title' => $cats_result->row($row_index)->cat_title
);
if($cats_result->row($row_index)->sub_name !== null){
do {
$cats[$cat_index]['subs'][$sub_index] = array(
'name' => $cats_result->row($row_index)->sub_name,
'title' => $cats_result->row($row_index)->sub_title
);
$sub_index++;
$row_index++;
if ($cats_result->row($row_index)->cat_name === $cats_result->row($row_index - 1)->cat_name)
continue;
break;
if ($row_index > 50) break 2;
} while (true);
} else {
$row_index++;
}
$sub_index = 0;
$cat_index++;
} while ($row_index <= $cats_result->num_rows() + 1);
}
<?php
public function get_categories($swap)
{
$sql = 'select
`categories`.`title` AS `cat_title`,
`categories`.`name` AS `cat_name`,
`subcategories`.`title` AS `sub_title`,
`subcategories`.`name` AS `sub_name`
from
`subcategories`
join
`categories` ON `categories`.`ID` = `subcategories`.`parent_id`
join
`swaps_looking_for_subcat` ON `swaps_looking_for_subcat`.`category_id` = `subcategories`.`ID`
where
`swaps_looking_for_subcat`.`swap_id` = ' . $swap . '
UNION select
`categories`.`title` AS `cat_title`,
`categories`.`name` AS `cat_name`,
`subcategories`.`title` AS `sub_title`,
`subcategories`.`name` AS `sub_name`
from
`categories`
left outer join
`subcategories` ON `subcategories`.`parent_id` = `categories`.`ID`
join
`swaps_looking_for_cat` ON `swaps_looking_for_cat`.`category_id` = `categories`.`ID`
where
`swaps_looking_for_cat`.`swap_id` = ' . $swap . '
order by
`cat_title`, `sub_title`';
return $this->db->query($sql);
}
<?php
if ($cats) {
// echo '<pre>';
// print_r($looking_for_cats);
// echo '</pre>';
echo '<ul>';
foreach ($cats as $cat) {
echo '<li><a href="/browse/' . $cat['cat']['name'] . '">' . $cat['cat']['title'] . '</a></li>';
if (isset($cat['subs'])){
echo '<ul>';
foreach ($cat['subs'] as $sub) {
echo '<li><a href="/browse/' . $cat['cat']['name'] . '/' . $sub['name'] . '">' . $sub['title'] . '</a></li>';
}
echo '</ul>';
}
}
echo '</ul>';
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment