Question 20864 in Mysql Question posted in
The official MySQL documentation can be found here.

Trouble creating parent-child relationship between two database tables using php codeigniter – Mysql

I have two databases tables called article_category and article_case. They are supposed to be combined in a webpage as below:

Article category[title] Article category[description] Article case[title] Article case[contents] Article case[title] Article case[contents]

This should repeat itself using foreach until all article categories are populated on the page.
Each article_case will also use foreach, but it has to respect its ‘category_id’ value which matches the ‘id’ in article_category .

I’m having trouble filtering the article_case table so that it only picks up rows from its parent ‘category_id’.

This is my current php code (which only makes an array of each table):

$this->db->select( "id,title,description" );
$this->db->from( 'article_category' );
$query_article_category = $this->db->get_compiled_select();

$query = $this->db->query( $query_sustainability_category . 'ORDER BY sort ASC ' );
foreach ($query->result_array() as $val) {
        $data['article_category_posts'][$val['id']] = $val;}

$this->db->select( "id,category_id,title,contents" );
$this->db->from( 'article_case' );
$query_article_case = $this->db->get_compiled_select();

$query = $this->db->query( $query_article_case);
$data[ 'article_cases' ] = $query->result_array();

And here is my html:

    <?php foreach ($article_category_posts as $key => $val) { ?>
<section>
   <h2><?php echo $val['title']; ?></h2>
   <p><?php echo $val['description']; ?></p>
</section>
<?php foreach ($article_cases as $key => $val) { ?>
<section>
   <h3><?php echo $val['title']; ?></h3>
   <p><?php echo $val['contents']; ?></p>
</section>
<?php } ?>
<?php } ?>

The current setup will input all article_cases disregarding the category_id.
How can I use foreach while assigning each article_case to their respective article_cateogry?

Tags:

2

Answers


  1. 0

    This would generate unnecessary amount of queries to the DB server.

    The rly basic approach would be:

    • select all categories (as you have) SELECT * FORM a_category
    • for each category select cases SELECT * FROM a_case WHERE category_id = {$category['id']}

    The second one is to combine the categories and cases right in the query with join and adjust the resulting data in php:

    SELECT cat.title, cat.description, case.id AS case_id, case.category_id, case.title, case.content
FROM a_case case
INNER JOIN a_category cat ON cat.id = case.category_id
    Login or Signup to reply.
  2. 0

    • You can use a query to get all the records of article_case with related article_category.

    • group the query result by category_id.

       $this->db->select("cat.title,cat.description,case.category_id, 
  case.title AS case_title, case.contents" );
 $this->db->from( 'article_category cat');
 $this->db->join('article_case case','case.category_id=cat.id');
 $result=$this->db->get()->result_array();

 $result_group = array_group_by($result, 'category_id');

    • array group by function

       function array_group_by(array $array, $key)
 {
     if (!is_string($key) && !is_int($key) && !is_float($key) && !is_callable($key)) {
         trigger_error('array_group_by(): The key should be a string, an integer, or a callback', E_USER_ERROR);

         return NULL;
     }
     $func = (is_callable($key) ? $key : NULL);
     $_key = $key;
     // Load the new array, splitting by the target key
     $grouped = [];
     foreach ($array as $value) {
         if (is_callable($func)) {
             $key = call_user_func($func, $value);
         } elseif (is_object($value) && isset($value->{$_key})) {
             $key = $value->{$_key};
         } elseif (isset($value[$_key])) {
             $key = $value[$_key];
         } else {
             continue;
         }
         $grouped[$key][] = $value;
     }
     // Recursively build a nested grouping if more parameters are supplied
     // Each grouped array value is grouped according to the next sequential key
     if (func_num_args() > 2) {
         $args = func_get_args();
         foreach ($grouped as $key => $value) {
             $params = array_merge([$value], array_slice($args, 2, func_num_args()));
             $grouped[$key] = call_user_func_array('array_group_by', $params);
         }
     }

     return $grouped;
 }

    • in view you can show as below

       <?php foreach ($result_group as $key => $cat_result) { ?>
   <section>
     <h2><?php echo $cat_result[0]['title']; ?></h2>
     <p><?php echo $cat_result[0]['description']; ?></p>
     <div class="box-bd-doted">
   </section>
   <?php foreach ($cat_result as $key => $val) { ?>
     <section>
       <h3 style="color:red"><?php echo $val['case_title']; ?></h3>
       <p><?php echo $val['contents']; ?></p>
     </section>
   <?php } 
 }
 ?>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search