At 10:16 PM 1/12/2006, Miles Thompson wrote:
At 05:51 PM 1/12/2006, Charles River wrote:
I am a novice in both PHP and MySQL, otherwise I would be able to do
this myself. :-)
A non-profit I assist has a table
(<http://www.lbc4cc.org/04-05board.php>) that is
displayed by a simple PHP script. That was fine last year, but now we
have some new officers and some new board members and I have no idea
how to change the script so that "Officers and Members" table is
presented officers first (Pres, Vice-pres, Sec and Treas) and then an
alpha listing of board members who are not officers.
The existing script looks like this:
<?php
$sql = "SELECT * FROM members";
$result=mysql_query($sql);
if (!$result) {
echo "Could not successfully run query ($sql) from DB: " .
mysql_error();
exit;
}
if (mysql_num_rows($result) == 0) {
echo "No rows found, nothing to print so am exiting";
exit;
}
echo "<table cellspacing='0' border='1' cellpadding='2'
bgcolor='#FFFFCC' width='100%'>";
echo "<tr>\n";
echo "<th>Office</td>\n";
echo "<th>Name</td>\n";
echo "<th>Address</td>\n";
echo "<th>Phones</td>\n";
echo "<th>Church</td>\n";
echo "<th>Email</td>\n";
echo "<th>Term<br>Ends</td>\n";
echo "</tr>\n";
while ( $row = mysql_fetch_array($result,MYSQL_ASSOC) )
{
echo "<tr>\n";
if ($row['Office'] == " ") {
echo "<td> </td>\n";
} else {
echo "<td><b>{$row['Office']}</b></td>\n";
}
echo "<td>{$row['Name']}</td>\n";
echo "<td>{$row['Address']}</td>\n";
echo "<td>{$row['Phone']}</td>\n";
if ($row['Church'] == "") {
echo "<td> </td>\n";
} else {
echo "<td>{$row['Church']}</td>\n";
}
if ($row['Email'] == "") {
echo "<td> </td>\n";
} else {
echo "<td>{$row['Email']}</td>\n";
}
echo "<td>{$row['Term']}</td>\n";
echo "</tr>\n";
}
echo "</table>\n";
?>
Can some kind soul bang out the code I need? Thanks.
I'd cheat and add a numeric field, let's call it num_rank, ranking the
offices in the order you want them displayed, e.g.
President 10
Vice-President 20
.
.
.
Board Member 50
Board Member 50
Board Member 50
and change the query to
SELECT * from members order by num_rank, last_name
which would provide officers and board member in the order you want them,
automatically sorted by name.
Incidentally, why do you have a separate table for officers and directors?
Maybe the answer is "because it was cheap and easy",
but is there a separate listing for all members who are not on the Board?
In that case, you would be better off with two tables:
1. Add a logical lBoardMbr field to your general list of members, and this
general list of members also has to have a unique primary key. MySQL will
do that for you with an autoincrement field, with a table type of MyISAM.
That table type ensures no re-use of deleted keys.
2. Your BoardMbr table then consists of fields like this:
nMbrId - primary key of the person in the general list
cOffice - text, "President", "Vice-President" .... "Board Member"
etc.
num_rank - as discussed above
cTerm - text field describing term.
When a member becomes an officer you do four things:
a) set the lBoardMbr field to true in the general table
b) enter the member's primary key value in nMbrId field in
BoardMbr table
c) enter correct value cTerm in the same table
d) set the lBoardMbr field to false in the general members table
for those members who are leaving the board.
The query to select the officers and directors would then change to
SELECT general.nMbrdID as nGenId, BoardMbr.nMbrID as nBrdId, *
from general, BoardMbr
WHERE nGenID = nBrdID
AND lBoardMbr != 0
ORDER BY num_rank, last_name
Advantage? Data more closely approaches 3rd normal form, no redundancy, no
errors when retyping, etc.
Long answer - hope it's been helpful.
Regards - Miles Thompson
PS If this is pedantic, and you know how to do all this stuff, pls forgive.
PPS Note I've assumed you have first_name and last_name fields /mt
And when I was lying bed this morning, in that half-state between full
wakefulness and sleep, I thought,
WHY BOTHER WITH THE lBoardMbr FIELD? AN ENTRY AND LINK IN THE BoardMbr
TABLE IS ENOUGH!!!
Good example of why one should sleep on solutions.
Which changes the SELECT to
SELECT general.nMbrdID as nGenId, BoardMbr.nMbrID as nBrdId, *
from general, BoardMbr
WHERE nGenID = nBrdID
ORDER BY num_rank, last_name
Cheers - Miles
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.371 / Virus Database: 267.14.17/228 - Release Date: 1/12/2006
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
