Displaying Parent/Child lists / Forums / Community / EllisLab

Forum Logo

Displaying Parent/Child lists

Posted: 30 June 2007 05:26 PM

[ Ignore ]

Joined: 2007-06-27
12 posts

My first post here, yay. =)

I’ve got a simple problem, but I can’t seem to figure it out. I guess this deals more with logic than the coding side of things - but I am a designer, not a software engineer - but I’m hoping to bridge the gap a little with CI.

The complete outcome of this script needs to be a simple staff directory with an extension listing. My problem is when trying to sort each respective employee into their correct departments.

CREATE TABLE `departments` (
  `id` tinyint(4) NOT NULL auto_increment,
  `display` char(2) NOT NULL default '',
  `description` varchar(40) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ; 

CREATE TABLE `staff` (
  `id` tinyint(4) NOT NULL auto_increment,
  `fname` varchar(20) NOT NULL default '',
  `lname` varchar(20) NOT NULL default '',
  `department` varchar(20) NOT NULL default '',
  `title` varchar(20) NOT NULL default '',
  `email` varchar(40) NOT NULL default '',
  `ext` varchar(6) NOT NULL default '',
  `cell` varchar(10) NOT NULL default '',
  `dayoff` char(2) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; 

Those are my two tables. One is departments which has an id, display (which is how I can control the order of the departments.. for example ‘managers’ will display before ‘office staff’) and then description. The other table has all the staff’s info in it. The department field is where it keeps the ID of the department from the first table.

Right now, I’ve been able to get this output.

ie,

Bob - Accounting
Sue - Accounting
Billy - Service
John - Service
Tom - Service
Rena - Sales
Eric - Sales

—But what I want to do is..

Accounting
Bob
Sue

Service
Billy
John
Tom

Sales
Rena
Eric

——

Make sense? Yeah it does, like I said it is a simple problem - but I’m not a coder, trying to learn. I thought I was doing pretty good figuring out the joins on the table, but getting it to display the way I want is proving to be harder than thought. Thanks!

Phil Sturgeon

Posted: 30 June 2007 07:23 PM

[ Ignore ] [ # 1 ] [ Rating: 0 ]

Joined: 2007-06-11
2985 posts

Right now I presume you have one single loop with a join in the SQL to output their names and their department names.

There are two ways to achieve the separate lists. You can either use a switch in the main database loop (look in the PHP manual for help with switches) and then you can make 3 seperate arrays.

you can simply have one big SQL query calling all different departments, then for each appartment run a SQL query getting the names from this type.

Hope that wasn’t too vague. You said it was a logic issue, not code, therefore didnt use code ^_^

Signature

————————
Blog | Twitter | GitHub | BitBucket
————————-
PyroCMS - open source modular CMS built with CodeIgniter
PancakeApp - Simple, hosted invoicing/w project management

emuexplosion

Posted: 30 June 2007 08:25 PM

[ Ignore ] [ # 2 ] [ Rating: 0 ]

Joined: 2007-06-27
12 posts

No, not really too vague. And I more or less understand what you are talking about. Thanks =)

(a little code wouldn’t hurt though )

thunder uk

Posted: 01 July 2007 06:46 AM

[ Ignore ] [ # 3 ] [ Rating: 0 ]

Joined: 2006-08-23
18 posts

A slightly easier way to do it (assuming you do pull the staff and department info together and it’s ordered by department) would be to set a flag as you loop through the record set.

$current_department = "";

foreach($recordset as $row) 
{
    if($current_department != $row['department']) // If the department has changed
    {
        $current_department = $row['department']; // Set the current flag to the new department
        echo("<b>" . $current_department . "</b>"); // and output the department header
    }

    // Output the staff details here

} 

That way, the header row will only get output when the department changes. The above code would need to be fixed up a little obviously according to the fields in your select statement.

Signature

Senior Developer, MyBuilder.com

Hierarchical data trees using nested sets

emuexplosion

Posted: 01 July 2007 12:23 PM

[ Ignore ] [ # 4 ] [ Rating: 0 ]

Joined: 2007-06-27
12 posts

This is what I’ve come up with. It works for what it is right now, I’ve just got to come up with the code to pull the department names out of the db rather than the id numbers of them. Shouldn’t be too hard. Thanks for the help.

function getStaff()
    {    

            $this->db->select('*');
            $this->db->from('staff');
            $this->db->join('departments', 'departments.id = staff.department', 'left');
            $this->db->orderby('departments.display', 'asc');
            $query = $this->db->get(); 

            $dpt='';
            $output='';
            if ($query->num_rows() > 0) {
                foreach ($query->result() as $staff) {
                    if($dpt != $staff->department)
                    {
                        if($dpt!='')
                            $output .= '</ul>';
                        $dpt = $staff->department;
                        $output .= '<h1>'.$dpt.'</h1>';
                        $output .= '<ul>';
                    }
                    $output .= '<li><strong>' . $staff->fname . ' ' . $staff->lname . '</stong></li>';
                }
                return $output;
            } else {
                return '<p>Sorry, no results returned.</p>';
            }
    } 