Issue joining tables and running loops...

[eluser]Kerran[/eluser]
First of all, to everyone who has contributed to this forum and the framework this is my first post and would you all to know that from a 'newbies' perspective the support and documentation provided for this framework is by far the best I have ever come across and it is all because of you...

I have minimal experience with Codeigniter and I have no problem getting this to work with other frameworks such as doctrine but I am finding it difficult getting it working with CI.

This is ultimately (in the most basic form).

I have a table of topics and a table of children topics with the primary key in the topics table matching a column in the children table.

In my web application is simply run a loop through all the topics and echo the children records related to that particular topic.

I am able to
1: pull information from one particular table

Code:

$this->db->select('*');
    $this->db->from('process');

2: left join the second table

Code:

$this->db->join('process_sub_categories', ' process_sub_categories.process_id = process.id', 'left');

3: run the query

Code:

$q = $this->db->get();

4: retun a variable

Code:

if ($q->num_rows() > 0) {
      foreach ($q->result() as $row) {
        $data[] = $row;
              
      }
      return $data;
    }

I need some support regarding how I can now run a foreach loop that assigns all the children topics to the appropriate parent.
so that output will look like this:

TOPIC A
CHILD A
CHILD B
TOPIC B
CHILD A
TOPIC C
CHILD A
CHILD B

apposed to

TOPIC A CHILD A
TOPIC A CHILD B
TOPIC B CHILD A
TOPIC C CHILD A
TOPIC C CHILD B

Any support will be appreciated.

Thank you very much.
Kerran

[eluser]jedd[/eluser]
Hi Kerran, and welcome to the CI forums.

I have minimal experience with Codeigniter and I have no problem getting this to work with other frameworks such as doctrine but I am finding it difficult getting it working with CI.

I'd be curious how you get this working in other frameworks - do they have a hierarchical function for presenting this kind of data natively?


There are a few ways of doing what you want.

First, easiest, you could select your parent ID's, and then do a query within a loop based on each of those. This sounds ugly, but two things to consider - queries are cheaper than we often assume they will be (DB programmers tend to put a fair amount of effort into reducing query costs) and secondly, you're likely not going to get all children to all parents in one fell swoop (you'll either be paginating, or you'll be a bit more selective than 'SELECT * FROM ...').

Alternatively you could exercise more care in your model, basically putting that logic back into the PHP on the data as it comes in, massaging it into an extra-dimensioned array, and then returning that new construct to your controller.

Yet another alternative is to handle this entirely within your presentation logic - your view - and that's probably where I'd do it.

Consider your intent:

I need some support regarding how I can now run a foreach loop that assigns all the children topics to the appropriate parent.
so that output will look like this:

TOPIC A
CHILD A
CHILD B
TOPIC B
CHILD A
TOPIC C
CHILD A
CHILD B

apposed to

TOPIC A CHILD A
TOPIC A CHILD B
TOPIC B CHILD A
TOPIC C CHILD A
TOPIC C CHILD B

That latter set is an accurate reflection of what a LEFT JOIN provides.

Anyhoo, yes, my preference would be to mangle this in the view - it's not a terribly complex algorithm to go through each parent, and if it's different to the previous parent then unindent .. etc etc. Do you need help with the details?

Btw, you may be better off getting an array rather than objects as your return type .. say, using result_array() rather than pulling in each row (an object) and assigning it to an array element. So, instead of this:

Code:

if ($q->num_rows() > 0) {
      foreach ($q->result() as $row) {
        $data[] = $row;
              
      }
      return $data;
    }

.. try this :

Code:

return ($q->num_rows() > 0)  ?  $q->result_array()  :  FALSE;

[eluser]Kerran[/eluser]
Jedd, thank you very much for your support - If you could give me some sort of example of the algorithm that you would adopt in the view it would be much appreciated.

As for your last suggestion -> nice, I didn't think of doing that.

[eluser]jedd[/eluser]
This is pretty rough .. and you can squeeze it down a bit if you want (though I think it is possible to overdo the ternary operators).

And of course you'll have to suss out your own variable names and formatting - you'd either do this in a list or table construct - rather than just indenting it with non-breaking spaces. Illustrative purposes, and all that.

Code:

$last_topic = NULL;

foreach ($data as $datum)  {
    // If parent matches the previous parent, don't show it - just indent a bit
    if ($datum['topic'] == $last_topic)
        echo nbs(5);
    else
        echo $datum['topic'] . nbs(1);

    // You're always showing the child information
    echo $datum['child'];

    echo "<br />\n";

    // Set last topic to current topic (for test on next loop)
    $last_topic = $datum['topic'];
    }

[eluser]Kerran[/eluser]
Jedd, again, thank you very much.... your example worked the first time around... where do I send my donation?

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////SOLVED/////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////