Table sorting problem
am 12.01.2006 22:51:29 von Charles River
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
() 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:
$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 "
bgcolor='#FFFFCC' width='100%'>";
echo "\n";
echo "Office\n";
echo " | Name\n";
echo " | Address\n";
echo " | Phones\n";
echo " | Church\n";
echo " | Email\n";
echo " | Term
Ends\n";
echo " |
\n";
while ( $row = mysql_fetch_array($result,MYSQL_ASSOC) )
{
echo "\n";
if ($row['Office'] == " ") {
echo " | \n";
} else {
echo "{$row['Office']} | \n";
}
echo "{$row['Name']} | \n";
echo "{$row['Address']} | \n";
echo "{$row['Phone']} | \n";
if ($row['Church'] == "") {
echo " | \n";
} else {
echo "{$row['Church']} | \n";
}
if ($row['Email'] == "") {
echo " | \n";
} else {
echo "{$row['Email']} | \n";
}
echo "{$row['Term']} | \n";
echo "
\n";
}
echo "\n";
?>
Can some kind soul bang out the code I need? Thanks.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: Table sorting problem
am 12.01.2006 23:02:38 von Bastien Koert
are the officers id'ed by some column? if so then add
ORDER by COLUMN_NAME
bastien
>From: Charles River
>Reply-To: chas.river@gmail.com
>To: php-db@lists.php.net
>Subject: [PHP-DB] Table sorting problem
>Date: Thu, 12 Jan 2006 16:51:29 -0500
>
>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 ()
>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:
>
>
> $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 "
>bgcolor='#FFFFCC' width='100%'>";
> echo "\n";
> echo "Office\n";
> echo " | Name\n";
> echo " | Address\n";
> echo " | Phones\n";
> echo " | Church\n";
> echo " | Email\n";
> echo " | Term
Ends\n";
> echo " |
\n";
>
> while ( $row = mysql_fetch_array($result,MYSQL_ASSOC) )
> {
> echo "\n";
>
>if ($row['Office'] == " ") {
> echo " | \n";
>} else {
> echo "{$row['Office']} | \n";
>}
> echo "{$row['Name']} | \n";
> echo "{$row['Address']} | \n";
> echo "{$row['Phone']} | \n";
>if ($row['Church'] == "") {
> echo " | \n";
>} else {
> echo "{$row['Church']} | \n";
>}
>if ($row['Email'] == "") {
> echo " | \n";
>} else {
> echo "{$row['Email']} | \n";
>}
> echo "{$row['Term']} | \n";
> echo "
\n";
> }
>echo "\n";
>?>
>
>Can some kind soul bang out the code I need? Thanks.
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, visit: http://www.php.net/unsub.php
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: Table sorting problem
am 13.01.2006 03:16:51 von Miles Thompson
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 ()
>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:
>
>
> $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 "
>bgcolor='#FFFFCC' width='100%'>";
> echo "\n";
> echo "Office\n";
> echo " | Name\n";
> echo " | Address\n";
> echo " | Phones\n";
> echo " | Church\n";
> echo " | Email\n";
> echo " | Term
Ends\n";
> echo " |
\n";
>
> while ( $row = mysql_fetch_array($result,MYSQL_ASSOC) )
> {
> echo "\n";
>
>if ($row['Office'] == " ") {
> echo " | \n";
>} else {
> echo "{$row['Office']} | \n";
>}
> echo "{$row['Name']} | \n";
> echo "{$row['Address']} | \n";
> echo "{$row['Phone']} | \n";
>if ($row['Church'] == "") {
> echo " | \n";
>} else {
> echo "{$row['Church']} | \n";
>}
>if ($row['Email'] == "") {
> echo " | \n";
>} else {
> echo "{$row['Email']} | \n";
>}
> echo "{$row['Term']} | \n";
> echo "
\n";
> }
>echo "\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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: Table sorting problem
am 13.01.2006 07:52:16 von Gerry Danen
On 1/12/06, Miles Thompson wrote:
> 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.
This is exactly how I implemented this page:
http://www.arls-lilies.org/h/board.php
--
Gerry
http://portal.danen.org/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: Table sorting problem - further thought
am 13.01.2006 14:26:20 von Miles Thompson
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
>>() 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:
>>
>>
>> $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 "
>>bgcolor='#FFFFCC' width='100%'>";
>> echo "\n";
>> echo "Office\n";
>> echo " | Name\n";
>> echo " | Address\n";
>> echo " | Phones\n";
>> echo " | Church\n";
>> echo " | Email\n";
>> echo " | Term
Ends\n";
>> echo " |
\n";
>>
>> while ( $row = mysql_fetch_array($result,MYSQL_ASSOC) )
>> {
>> echo "\n";
>>
>>if ($row['Office'] == " ") {
>> echo " | \n";
>>} else {
>> echo "{$row['Office']} | \n";
>>}
>> echo "{$row['Name']} | \n";
>> echo "{$row['Address']} | \n";
>> echo "{$row['Phone']} | \n";
>>if ($row['Church'] == "") {
>> echo " | \n";
>>} else {
>> echo "{$row['Church']} | \n";
>>}
>>if ($row['Email'] == "") {
>> echo " | \n";
>>} else {
>> echo "{$row['Email']} | \n";
>>}
>> echo "{$row['Term']} | \n";
>> echo "
\n";
>> }
>>echo "\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