sql results from a table matching field from another table including sub fields

Question

using codeigniter query i am unable to get appropriate results i need to get results of a table this way

$catid=$_POST['category'];
$new_id = select catid from categories_table where catid='$catid' and parent='$catid';

so it will add also include results of other cats have $cadid as parent

Select * from articles_table where catid = '$new_id';

i am trying in codeigniter like this

  $p=$this->input->post('category');
  $this->db->select('catid');
  $this->db->where('parent',$p);
  $this->db->where('catid',$p);
  $query = $this->db->get('categories_table');

  if($query->num_rows()>0)
             {
             foreach($query->result() as $row)
             $new_id[]=$row->catid;
             }
  $this->db->where('catid',$new_id);
  $this->db->where('status',1);
  $this->db->order_by('time_added', 'desc');  
  $res = $this->db->get('articles_table');
  $query_res= $res->result();

it gives the error Message: Trying to get property of non-object

cats table
catid -- parent -- name
1      -- 0          -- first cat
2  --     1         -- cat child of first

Article table
id -- catid - content
1  -- 2     -- first article
2 --  1     -- second article

if i query where cat id = 1 it should return results from catid 2 too as 2 is child of one

Just want to remind you that you call $query->result() twice. Try to delete one of them. Like foreach($query as $row){...} or delete the one before if — Jaaaaaaay, Mar 07 '16 at 21:16
Also, you missed brackets after num_rows, it should be if($query->num_rows() > 0), I suggest that you might need to read through codeigniter doc a little bit, it will save you a lot of time. — Jaaaaaaay, Mar 07 '16 at 21:24
One more mistake is, it probably should be `$new_id[] = $row->catid;`. — Tpojka, Mar 07 '16 at 21:44
correcting these errors i am not getting any error msg but no result — iCodeCrew, Mar 07 '16 at 21:48
I don't know your tables structure but it seems odd a category that have itself as parent here: `where catid='$catid' and parent='$catid';` — Jorge Campos, Mar 07 '16 at 22:01
it should be one of then or an `or` clause like `where catid='$catid' OR parent='$catid';` — Jorge Campos, Mar 07 '16 at 22:03
`$this->db->where('catid',$new_id);` has a problem. `$new_id` is an array. — jtheman, Mar 07 '16 at 22:10
Can't you use a `JOIN` statement instead? Could you please add to the question a simplified database schema for the tables involved and how you want your return. This shouldn't be too complicated... — jtheman, Mar 07 '16 at 22:13
Also, the where row `$this->db->where('catid',$p);` selects the rows where `catid` equals to the $p value. But the only thing you use the results for is to get back the `catid` value, which should be $p, so nothing is really achieved by the first database query... — jtheman, Mar 07 '16 at 22:18
@JorgeCampos It is 'mysql hierarchical data' and snippet you pointed out serves $catid category with all belonging first level subcategory. Check it [here](http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) or [here](http://www.slideshare.net/billkarwin/models-for-hierarchical-data). — Tpojka, Mar 07 '16 at 22:29
@Tpojka I know very well what hierarchical data is and it has nothing to do with mysql as you say `mysql hierarchical data` hierarchical data is just a way to design data. I pointed out the error that states right what would be an error in hierarchical data which is a registry pointing to itself. In hierarchical data a registry can have childs, therefore be a father a father registry can't point to its PARENT if it is itself that is what his sql states `catid='$catid' AND parent='$catid'` using `AND` operator means that a registry would point to itself which is a design flaw. A first level... — Jorge Campos, Mar 07 '16 at 23:20
category should point to a null registry or to one that does not exists on the table. Pointing a registry to itself would make most of the hierarchical algorithms to a infinite loop. — Jorge Campos, Mar 07 '16 at 23:21
@Tpojka I hope you can understand what I tried to explain. English is not my primary language :) — Jorge Campos, Mar 07 '16 at 23:23
@JorgeCampos First my apology. You are 101% right. I misunderstood and thought you were trying to propagate `AND` relation in sql query. I saw `OR` as good option (too). I should read your comment twice. :/ — Tpojka, Mar 08 '16 at 14:18

jtheman · Accepted Answer · 2016-03-07T22:41:59.180

I would try using the model something like this:

$p = $this->input->post('category');
$query = $this->db
    ->select('catid')
    ->where('parent',$p)
    ->or_where('catid',$p)
    ->get('categories_table');

$data = array();
if($query->num_rows())
{
   foreach($query->result() as $row)
   {
       $res = $this->db
            ->where('catid',$row->catid)
            ->where('status',1)
            ->order_by('time_added', 'desc') 
            ->get('articles_table');
       foreach ($res->result() as $article_data)
       {
          $data[] = $article_data;
       }
    }
}
return $data;

The code first checks for categories where $p is EITHER equal to catid OR parent in the categories_table. Then checks for articles that has the same catid as the category and adds all articles to an array, $data.

sql results from a table matching field from another table including sub fields

1 Answers1