Re: Table sorting problem

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>&nbsp;</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>&nbsp;</td>\n";

} else {

    echo "<td>{$row['Church']}</td>\n";

} if ($row['Email'] == "") {

    echo "<td>&nbsp;</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 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.1.371 / Virus Database: 267.14.17/227 - Release Date: 1/11/2006