Welcome Guest, Not a member yet? Register   Sign In
Displaying Parent/Child lists
#1

[eluser]emuexplosion[/eluser]
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.

Code:
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 ;

Code:
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!
#2

[eluser]Phil Sturgeon[/eluser]
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.

OR

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 ^_^
#3

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

(a little code wouldn't hurt though Wink )
#4

[eluser]thunder uk[/eluser]
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.

eg

Code:
$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.
#5

[eluser]emuexplosion[/eluser]
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.

Code:
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>';
            }
    }




Theme © iAndrew 2016 - Forum software by © MyBB