SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Member
    Join Date
    Nov 2009
    Location
    London, UK
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Group Employees by Company Name - PHP / Mysql

    Going round in circles due to being very new at php / mysql!

    I have one very simple Mysql table called 'people' populated with employee and address information.
    Fields are;
    first
    last
    company
    cell
    email
    address_1
    address_2

    I want to produce a loop? in PHP which prints out and groups records by the company field as shown at the bottom of this post.

    I don't want to print multiple copies of the Company records each time to save space in the final PDF I am creating.

    Eventually I want to print the results to a FPDF generated PDF, but for now just want to create a script which will just print the results to screen. I can then figure out later how to format and print to FPDF.


    =========================================
    Bobs Widgets (from company field)
    24 Madison Avenue (from address_1 field)
    ---------------------------------------------------------------------
    John Doe, Tel: 123 345, Cell: 188 456 (These are the employees from Bobs Widgets)
    Sam Smith, Tel: 444 3456, Cell: 728 129
    =========================================
    Cadbury Chocolate Limited (from company field)
    23 Caramel Street (from address_1 field)
    ---------------------------------------------------------------------
    Bob Smythe, Tel: 876 442, Cell: 123 456 (These are all of the lucky employees from Cadbury Chocolate Limited)
    Sarah Dickenson, Tel: 154 3456, Cell: 723 129
    Darren Johnson, Tel: 1234, Cell: 8232 9323
    =========================================
    repeat until we have displayed all people.....

    The loop also needs to then print out people where there is no company listed (i.e. freelancers etc).

    Any help would be appreciated...

    Stagelink

  2. #2
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,807
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    easy way:

    PHP Code:
    while($rows mysql_fetch_assoc($query)) {
    if(
    $rows['company'] != $set_company_flag) {
        echo 
    $rows['company'];
    } else {
        echo 
    $rows['first']; // and other details
    }
    $set_company_flag $rows['company']; // set the company flag at the end of the display loop



    Disclaimer: Not tested!

    and welcome to SitePoint Stagelink
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  3. #3
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well first make a loop which outputs company info with every emploee

    then, add a simple condition inside the loop, and output company info only if it was changed.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    since no one else mentioned it, let me add that the key to success here is the ORDER BY clause in the SELECT query

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    This should do it, although it does seem a little overly complicated.

    PHP Code:
    <?php
    $result 
    mysql_query('SELECT first, last, company, cell, email, address_1, address_2 FROM people');

    $records = array();

    while(
    $record mysql_fetch_assoc($result))
    {
        if(
    false === array_key_exists(md5($record['company']), $records))
        {
            
    $records[md5($record['company'])] = array(
                
    'company'    => $record['company'],
                
    'address_1'    => $record['address_1'],
                
    'address_2'    => $record['address_2'],
                
    'employees'    => array()
            );
        }
        
    array_push($records[md5($record['company'])]['people'], $record);
    }

    foreach(
    $records as $company)
    {
        
    printf(
            
    "
            ===============
            Company: %s
            Address:
            %s
            %s
            ---------------
            "
    ,
            
    $company['company'],
            
    $company['address_1'],
            
    $company['address_2']
        );
        foreach(
    $company['employees'] as $employee)
        {
            
    printf(
                
    "%s %s. Tel: %s , Email: %s\r\n",
                
    $employee['first'],
                
    $employee['last'],
                
    $employee['cell'],
                
    $employee['email']
            );
        }
        echo 
    '===============';
    }
    ?>
    Edit: Ha, now I'm CONVINCED it's over complicated.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    anthony, pls see post #4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Nov 2009
    Location
    London, UK
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey Guys,

    Thanks for the help. Not getting anyway though.....

    I tried AnthonySterling (slightly complicated!!) and just got this error;
    Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result

    I tried the suggestion of Post #4 using this really simple query;

    Code:
    $result = mysql_query("SELECT * FROM people ORDER BY company, first");
    
    while($row = mysql_fetch_array($result))
      {
      echo $row['first'];
      echo " " . $row['last'];
      echo " " . $row['company'];
      echo "<br />";
      }
    This as you would expect just gives me a list of every person and company sorted by company, which is not what I want. I want to only print the company (and address) one time and then print the employees and their details belonging to each company (like my original post).

    You probably need to be really explicit with me, as I couldn't work out how to make spikeZ's post work as I couldn't see where I was going to select the right TABLE.

    Thanks so much for your input.
    Stagelink

  8. #8
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    now, put a simple condition inside the loop, to see if company info was changed

  9. #9
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Hey Stagelink,

    Sorry for the already confessed complicated solution, as Shrapnel_N5 states, you just need a switch of sorts to indicate a new company has begun.

    PHP Code:
    <?php
    $result 
    mysql_query('SELECT * FROM people ORDER BY company ASC');
    $current_company null;
    while(
    $record mysql_fetch_assoc($result))
    {
        if(
    $current_company !== $record['company'])
        {
            
    printf(
                
    "
                ===============
                Company: %s
                Address:
                %s
                %s
                ---------------
                "
    ,
                
    $record['company'],
                
    $record['address_1'],
                
    $record['address_2']
            );
            
    $current_company $record['company'];
        }
        
    printf(
            
    "%s %s. Tel: %s , Email: %s\r\n",
            
    $record['first'],
            
    $record['last'],
            
    $record['cell'],
            
    $record['email']
        );
    }
    ?>
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  10. #10
    SitePoint Member
    Join Date
    Nov 2009
    Location
    London, UK
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey guys (and particularly Anthony Sterling),

    What can I say it worked! - Brilliant.

    My next and hopefully last two questions;
    1) The pesky freelancers who don't work for any company (i.e. the company field is blank), but who we still have information about their cell, email and more importantly address_1, address_2 .

    They are currently listed together at the top of this report grouped under a blank company, but I need to somehow split out those who have no company name and display their address etc.
    Maybe this would be better done by updating the 'people' table first to copy the 'first' and 'last' fields into the company field ( where it is blank) so that the rest of the script runs ok.

    2) Sometimes people will NOT have an Email address, so how do I NOT print the 'Email:' preceeding bit of text in the script?

    I've learnt something today which is great news.

    Cheers,

    Stagelink.

  11. #11
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Did you really learned?
    Seems no.
    It seems you just possessed code, but have no idea how it works
    otherwise you'd solve that email problem yourself.
    Because it is much the same as checking if we need to print out company name, or no.

  12. #12
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Not the best, but a super quick retro-fit for you to work on.
    PHP Code:
    <?php
    $result 
    mysql_query('SELECT * FROM people ORDER BY company ASC');
    $current_company null;
    while(
    $record mysql_fetch_assoc($result))
    {
        if(
    $current_company !== $record['company'])
        {
            if(
    false === empty($record['company']))
            {
                
    printf(
                    
    "
                    ===============
                    Company: %s
                    Address:
                    %s
                    %s
                    ---------------
                    "
    ,
                    
    $record['company'],
                    
    $record['address_1'],
                    
    $record['address_2']
                );
            }
            else
            {
                echo 
    "
                    ===============
                    FREELANCERS
                    ---------------
                "
    ;
            }
            
    $current_company $record['company'];
        }
        
    printf(
            
    "%s %s. Tel: %s %s\r\n",
            
    $record['first'],
            
    $record['last'],
            
    $record['cell'],
            
    false === empty($record['email']) ? sprintf(', Email: %s'$record['email']) : null
        
    );
    }
    ?>
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  13. #13
    SitePoint Member
    Join Date
    Nov 2009
    Location
    London, UK
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you - it makes alot more sense now.


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
  •