Re: should be a simple means to display these results the way I want them
| From: | Chris Hobbs | Date: | Thu, 11 Oct 2001 18:28:39 +0000 |
| Subject: | Re: should be a simple means to display these results the way I want them | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-13269@lists.php.net to get a copy of this message | ||
I just thought of an even cleaner solution, which works with just the one query you're already doing:
// perform query stuff here
$initid = "";
$role = "";
while ($row = mysql_fetch_array($result)) {
if ($row['initid'] != $initid) {
$initid = $row['initid'];
$role = "";
print "\n";
print $initid."\n";
}
if ($row['role'] != $role) {
$role = $row['role'];
print "$role\n";
}
print $row['fname']." ".$row['mi']." ".$row['lname']."\n";
}
Tadaaa!
Mike Cermak wrote:
but I'll be damned if I can figure it out - I know exactly how to write the report in M$ Access, and I think that has numbed my brain :-). In a nutshell: 3 tables, as follows: mysql> describe people; +---------+-------------+------+-----+---------+----------------+-- _______ ___ _ ____ _____ Chris Hobbs / ____\ \ / / | | |/ ___\| __ \ Head Geek | (___ \ \ / /| | | | (___ | | | | WebMaster \___ \ \ \/ / | | | |\___ \| | | | PostMaster ____) | \ / | |__| |____) | |__| | \____/ \/ \____/ \____/|_____/ http://www.silvervalley.k12.ca.us chobbs@silvervalley.k12.ca.us| Field | Type | Null | Key | Default | Extra |+---------+-------------+------+-----+---------+----------------+| pid | int(11) | | PRI | NULL | auto_increment | | fname | varchar(25) | YES | | NULL | | | mi | varchar(4) | YES | | NULL | | | lname | varchar(25) | YES | | NULL | |+---------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)mysql> describe inits; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra |+----------+--------------+------+-----+---------+-------+| initid | int(2) | | PRI | 0 | | | initdesc | varchar(255) | YES | | NULL | |+----------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)mysql> describe initassign; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| pid | int(11) | YES | | NULL | | | initid | int(2) | YES | | NULL | | | role | varchar(50) | YES | | NULL | |+--------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) People can belong to one or more inits, and have any one of three roles (leader, staff, or volunteer) within an init. not ever init necessarily has people in every role, and any role within an init can have any number of people. As a result, the query I'm using, which is SELECT participants.fname, participants.mi, participants.lname, inits.initid, inits.initdesc, initassign.role from participants right join initassign on participants.pid = initassign.pid left join inits on initassign.initid = inits.initid order by inits.initid asc, initassign.role asc, participants.lname asc; creates a table that has the following fields: fname mi lname initid initdesc role what I want to do is display the output in a PHP-generated HTML page so it looks something like this Init1 Leaders name name name Staff name name name Volunteers name name name Init2 Leaders name name Staff name Volunteers name name name and instead, the best I can come up with is Init1 Leaders name Init1 Leaders name Init1 Staff name Init1 Volunteers name Init1 Volunteers name ... I'm sure you get the idea. I'm totally stumped as to how to build a looping construct that will do what I want it to do. Any and all assistance would be appreciated, please reply directly to me, as I don't not monitor the lists regularly. Thanks, Mike Cermak Webmaster/Web Server Administrator Buffalo Niagara Partnership, Inc. mcermak@buffniag.org webmaster@buffniag.org 716/852-7100 x112