Re: Table sorting problem - further thought

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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>&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

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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux