SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Complicated List... Order By problems... BrainTeaser!

    I want to make a list of people in my company.
    A person can have one or more functions, and can belong to one or more sections in the company.

    My database looks like this
    employee (UserID, Lastname, Firstname)
    works_as (UserID, FunctionID)
    function (FunctionID, Name)
    works_at (UserID, DepartID)
    department (DepartID, Name)

    The list should look like this:
    i.e.
    [b]Name............. Function(s).........Department(s)........
    Jack Frost -- Manager -- Economic Department
    <blank....>-- Secretary -- Development Department
    <blank....>-- <blank....>-- Blabla Department
    Jack Daniels -- Employee -- Economic Department
    <blank....>-- Custodian -- <blank...>

    So, the function of the person is not connected to a particular department. He/she can be an employee at two departments. Or, he/she can be employee and secretary at one department.

    Now, here comes the tricky part. I've already managed to write a code that does this. But, I would like the list to be order by name or function or department.

    The code that I use at the moment:
    PHP Code:
    $result mysql_query("SELECT * FROM employee");
        
    if(
    mysql_num_rows($result) > 0) {
          while(
    $row mysql_fetch_array($result)) {
              
    extract($row);
              
              
    $sql1        "SELECT * FROM employee WHERE UserID = '$UserID'";
          
    $result1    mysql_query($sql1);
          
    $num1        mysql_num_rows($result1);
          
          if (
    $num1 != '0'){
              
    $Firstname    mysql_result($result1,0,'Firstname');
              
    $Lastname    mysql_result($result1,0,'Lastname');    
          }
          print 
    "<tr><td valign='top'>$Firstname $Lastname &nbsp;&nbsp;</td>";
        
              
    $sql2        "SELECT FunctionID FROM works_as WHERE userid = '$UserID'";
          
    $result2    mysql_query($sql2);

          print 
    "<td align='top'>";

          if(
    mysql_num_rows($result2) > 0){
            while(
    $row mysql_fetch_array($result2)){
                  
    extract($row); 

                  
    $function mysql_query("SELECT * FROM function WHERE functionid = '$FunctionID'");
                          
              if(
    mysql_num_rows($function) > 0) {
                      while(
    $row1 mysql_fetch_array($function)) {
                    
    extract($row1);
                    print 
    "$Name<br>";
                      }
                    }

                  }
                }
          print 
    "</td>";          


              
    $sql3        "SELECT DepartID FROM works_at WHERE userid = '$UserID'";
          
    $result3    mysql_query($sql3);

          print 
    "<td align='top'>";

          if(
    mysql_num_rows($result3) > 0){
            while(
    $row mysql_fetch_array($result3)){
                  
    extract($row); 

                  
    $department mysql_query("SELECT * FROM department WHERE departid = '$DepartID'");
                          
              if(
    mysql_num_rows($department) > 0) {
                      while(
    $row1 mysql_fetch_array($sectie)) {
                    
    extract($row1);
                    print 
    "$Name<br>";
                      }
                    }

                  }
                }
          print 
    "</td></tr>";
        }


  2. #2
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no one?

  3. #3
    SitePoint Zealot oodie's Avatar
    Join Date
    Jul 2000
    Location
    Misty Mountain
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What about
    PHP Code:
    $query 
    "SELECT ".
    "concat(employee.Firstname,employee.Lastname) AS name, ".
    "function.FunctionID AS function, ".
    "department.Name AS department ".
    "WHERE ".
    "employee.UserID = works_as.UserID AND ".
    "works_as.FunctionID = function.FunctionID AND ".
    "employee.UserID = works_at.UserID AND ".
    "works_at.DepartID = department.DepartID ".
    "ORDER BY name,function,department";

    $result mysql_query($query);

    while(
    $row mysql_fetch_array($result)){
        echo 
    "$row['name'] $row['function'] $row['department']<br>";

    Can't guarantee it works, I haven't tried it.

  4. #4
    SitePoint Member NoXcuz's Avatar
    Join Date
    Jun 2001
    Location
    Sweden
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oodie - That wouldn't work, and you forgot the ...FROM employee... -part
    But here's an idea:
    I've simplified your dblayout and dropped the tables works_as and works_at, and replaced them with just one table

    Code:
    CREATE TABLE wheretheywork (
       id int(11) NOT NULL auto_increment,
       UserID int(11),
       FunctionID int(11),
       DepartID int(11),
       PRIMARY KEY (id)
    );
    where you insert stuff like
    INSERT INTO wheretheywork(UserID,FunctionID,DepartID) VALUES (1,1,1),(1,2,2),(1,NULL,3);
    notice the NULL...

    To get the results you want you can use something like
    Code:
    SELECT CONCAT(e.Firstname, ' ', e.Lastname) as name, 
      ifnull(f.Name,'-') AS function, 
      ifnull(d.Name,'-') AS department 
    FROM employee e, wheretheywork w 
    LEFT JOIN function f ON w.FunctionID=f.FunctionID 
    LEFT JOIN department d ON w.DepartID=d.DepartID 
    WHERE e.UserID=w.UserID 
    ORDER BY name DESC
    That'll give you results like

    name | function | department |
    Jack Frost | Manager | Economic.. |
    Jack Frost | Secretary | Development... |
    Jack Frost | - | - |

    If you want to get rid of the repeating names you can do something like this in your PHP-code:
    PHP Code:
    <?
    $old_name
    ="";
    $query "SELECT blahblah like the above...";
    $result mysql_query($query$con);
      if (
    $result) {
        while (
    $row mysql_fetch_array($result)) {
          if(
    $old_name != $row[name]) {
            echo 
    "$row[name]<br>";
          } else {
            echo 
    " <br>"; }
          
    $function $row[function];
          
    $department$row[department];
          echo 
    "$function<br>";
          
    // and so on
          
    $old_name $row[name];
        }
      }
    ?>
    Just an idea that you could adapt to your needs...

    /NoXcuz
    Last edited by NoXcuz; Jun 5, 2001 at 05:39.
    For those about to rock - we salute you

  5. #5
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanx, I'll give it a shot


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •