need help multiple assignments

[eluser]mariuskmc[/eluser]
Please I need help

I have a list of categories and a list of articles.
An article is assigned to several categories

I fail to create the list of categories that have items

table categorie
idcategorie
numecategorie

table articol
idarticol
numearticol
categoriemultipla(1,4,6)



controler

Code:

$data['navigare_categorii']=$this->Navigare_model->getAllnavigarecategoriiC();

model

Code:

//NAVIGARE CATEGORII SEJUR
    function getAllnavigarecategoriiC() {

        $this->db->select('categorie.idcategorie,
  categorie.numecategorie,
  articol.idarticol,
  articol.categoriemultipla');
        $this->db->from('categorie,articol);

        $q = $this->db->get();
        if ($q->num_rows()>0) {
            foreach ($q->result_array() as $row){
                $categoriiar = explode(', ', $row['categoriemultipla']);
                    foreach($categoriiar as $categorie)
                    {
                    $this->db->select(categorie.idcategorie, categorie.numecategorie');
                    $this->db->from('categorie');
                    $this->db->orderby('categorie.numecategorie');
                    
                    $this->db->where('categorie.idcategorie', $categoriiar);
                    $qi = $this->db->get();
                    if ($qi->num_rows()>0) {
                        foreach ($qi->result() as $rowd){
                        $data[]= $rowd;
                        }
                        return $data;
                    }
                
                    
                    }
            }
            return $data;
        }
        
        
    }

view

Code:

<?php foreach ($navigare_categorii as $rownavcat):?>
<?php
$nume_categorie_navcat=$rownavcat->numecategorie ;
$nume_categorie_navcat=str_replace(' ', '_', $nume_categorie_navcat);
$nume_categorie_navcat=strtolower($nume_categorie_navcat);
$numecategorie_intreg=$rownavcat->numecategorie;
?>
<li> &lt;?=anchor("site/categorie/".$nume_categorie_navcat,$numecategorie_intreg.")" )?&gt; </li>
&lt;?php endforeach;?&gt;

[eluser]Nick_MyShuitings[/eluser]
If you want to make your life a lot simpler, use a joining table for that many to many relationship:

table - categori_articol
fields - id, categori_id, articol_id

Then your sql is one query on the joining table to get the categories that are present, group_by categori_id and game over.

That said, if you want to continue with your existing table structure I'd propose this:

1) select count(categori);
2) select categoriemultipla from articol

Code:

$count = $this->db->count_all('categorie');
do {
    $articols = $this->db->select('categoriemultipla')->get('articol')->result();
    foreach($articols as $articol) {
      $categori = explode(',',$articol->categoriemultipla);
      foreach($categori as $categori_id) {
        $categories[$categori_id] = $categori_id;
      }
    }
    $newcount = count($categories);
} while ($newcount < $count);

var_dump($categories);

Should contain all the categories that have a linked article... and the good part is it will stop all the looping over the articles once it has found at least one that is in the category. Of course if you wanted something different like the number of articles in the category you would modify the logic a bit.

[eluser]mariuskmc[/eluser]
Thanks a lot
I already have your proposed format (joining table) ... but wanted to see if it's better this way.
Still not decided what to use ... depending on how much i need to change the administration and related pages