FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


PHP MYSQL sorted table





Dark-Tech
I have a table that is generated from a mysql database
Example

Position Last Name First Name

Treasurer Foreman BOB
President Whittle Jake
Secretary Santiago Adam
Vice President Marsh Candy

Code:
$SQL = "SELECT * FROM Members";
$result = mysql_query($SQL);
print "<table border=\"1\">";
print "<tr><td>Position</td>";
print "<td>Last Name</td>";
print "<td>First Name</td></tr>";
while ($db_field = mysql_fetch_assoc($result)) {

print "<tr><td>" . $db_field['Position'] . "</td>";
print "<td>" . $db_field['LastName'] . "</td>";
print "<td>" . $db_field['FirstName'] . "</td></tr>";

}
print "</table>";
mysql_close($db_handle);

}
else {
print "Database NOT Found ";
mysql_close($db_handle);
}



the table is populated from a mysql database, how can i write it so that the when you click on the headers it sorts the table by the values of that column

for instance i click on position, the table would then look like

Position Last Name First Name

President Whittle Jake
Secretary Santiago Adam
Treasurer Foreman BOB
Vice President Marsh Candy

for instance i click on Last Name, the table would then look like


Position Last Name First Name

Treasurer Foreman BOB
Vice President Marsh Candy
Secretary Santiago Adam
President Whittle Jake

etc
coreymanshack
When you click on a header, link to another script that sorts it by the header you clicked, like if I click Position, link to a php script that orders it by position like this
Code:
SELECT * FROM members ORDER BY position ASC

If i click, Last Name, have it link to another script that sorts it by last name like this
Code:
SELECT * FROM members ORDER BY lastname ASC


If ASC is the wrong way, try DESC
Dark-Tech
is there a way to set priority, such that if i sort by position it would come back
President
Vice President
Secretary
Treasurer
presidential advisor
Member
member
etc....
mathiaus
I like to create an order column where each group name has a number associated with the order it should be sorted.
coreymanshack
mathiaus wrote:
I like to create an order column where each group name has a number associated with the order it should be sorted.


Yea create that column, and then use the orderby sql.
AftershockVibe
For something like that it might be better define the ordering of "ranks" within the database, rather than in code.

So you have a table which is something along the lines of:
Code:
Members: MemberId PositionId LastName FirstName
(e.g. 1, 1, "Bloggs", "Joe")


Then a positions table like:
Code:
Positions: PositionId, Rank, Title
(e.g. 1, 1, "President")


You'll then need to change your query like so:
Code:
SELECT Title, FirstName, LastName
FROM Members, Positions
WHERE Members.PositionID = Positions.PositionId
ORDER BY Rank ASC
rvec
Code:
SELECT Positions.Title, Members.FirstName, Members.LastName
FROM Members LEFT JOIN Positions ON Members.PositionID = Positions.PositionId
ORDER BY Positions.Rank ASC

That'd do almost the same, but a left join is better in this case.
AftershockVibe
Indeed it would. Although in practice no-one would ever notice the difference. For a tiny problem like this I didn't think it was worth getting into JOINs and the difference between ORDER BY and GROUP BY.

Wink
Related topics
PHP, MySQL...
ASP +PHP+MySQL Tutorials
what is your php mysql apache installer package
Need some php/mysql dlls
The Basics (php, mysql etc)
What is useful way in this php+mysql problem?
PHP/Mysql - beginner!
A very good PHP MySQL Tutorial
Setup php,mysql and others...
php, mysql and iis5
 EASY 40 frih$ IF YOU KNOW PHP+MySQL 
Php Mysql security testing
Project to join - php,mysql??
Link PHP/Mysql with Outlook Express
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.