Gossamer Forum
Home : General : Databases and SQL :

PHP / Mysql Query to group by company name / print company employees

Quote Reply
PHP / Mysql Query to group by company name / print company employees
Hi there,

Going round in circles due to being slightly inept 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 like so;
I don't want to print multiple copies of the Company records each time to save space in the final PDF I am creating.


=========================================
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...

Best wishes,

Piers
Quote Reply
Re: [Piers1] PHP / Mysql Query to group by company name / print company employees In reply to
You considered using something like GROUP BY company ?

Alternativly, in perl it could be done with something like:

SELECT * FROM table;

Code:
my $companies;
while (my $hit = $sth->fetchrow_hashref) {
if ($companies->{$hit->{comapny}}) {
$companies->{$hit->{comapny}} .= qq|\n$hit->{first}::$hit->{last}::$hit->{company}::$hit->{cell}::$hit->{email}::$hit->{address_1}::$hit->{address_2}|;
} else {
$companies->{$hit->{comapny}} = qq|$hit->{first}::$hit->{last}::$hit->{company}::$hit->{cell}::$hit->{email}::$hit->{address_1}::$hit->{address_2}|;
}
}

map {

my @loop = split /\n/, $companies->{$_};

print qq|<h2>$_</h2>|

foreach my $vals (@loop) {
my ($first,$last,$company,$cell,$email,$address_1,$address_2) = split /::/, $vals;
print qq|print the details of it here <Br />|;
}

} keys %$companies;

Untested, but something like that should work :)

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] PHP / Mysql Query to group by company name / print company employees In reply to
Hi Andy,

Thanks for that. I'm kind of committed to using PHP for this particular problem due to having to incorporate it into FPDF (a PHP library to generate pdf's).

I need a PHP wiz to translate what you just did!!

Cheers,

Piers
Quote Reply
Re: [Piers1] PHP / Mysql Query to group by company name / print company employees In reply to
NP - afraid I can't help with convering to PHP - as been a hell of a long time since I last touched it =)

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!