Chronologically display a list
Moderator: General Moderators
Chronologically display a list
Hi,
I have a database set up in mysql with all the values for every member in it. Each row (member) has a member number, name, email and score. I want to display in order from highest score to lowest score the members in a table. I want the table to have 2 columns and on the left column to be the member name and the right to be the score. I know how to extract the data from mysql and that's all set up. I just need to know how to get each row chronologically from highest score to lowest. Any help would be much appreciated.
Regards,
Matt
I have a database set up in mysql with all the values for every member in it. Each row (member) has a member number, name, email and score. I want to display in order from highest score to lowest score the members in a table. I want the table to have 2 columns and on the left column to be the member name and the right to be the score. I know how to extract the data from mysql and that's all set up. I just need to know how to get each row chronologically from highest score to lowest. Any help would be much appreciated.
Regards,
Matt
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
You need the SQL to get the data in order (ok maybe don't need but it is likely to be a lot quicker) Then you need to use mysql functions in php e.g mysql-fetch-assoc.php to get the information and output as your HTML table.
If you have some php you can show us we can provide additional pointers.
If you have some php you can show us we can provide additional pointers.
I think this is what you want.
http://dev.mysql.com/doc/refman/4.1/en/ ... ation.html
You can then order your result set within the query and loop through it with a while loop.
http://dev.mysql.com/doc/refman/4.1/en/ ... ation.html
You can then order your result set within the query and loop through it with a while loop.
For the record (and coz I'm a bit of a grammar fiend) 'chronologically' only relates to temporal order .. by time or by age. You just want to order something numerically.
Your SQL will be roughly along the lines of:
Your SQL will be roughly along the lines of:
Code: Select all
select membername, score from whatevertable order by score desc
Last edited by onion2k on Wed Sep 06, 2006 6:28 am, edited 1 time in total.
feyd | Please use
What's happening is this is displaying the list properly and perfectly from highest score to lowest score until it finds a score which is less than 10. The scores which are less than 10 are automatically put at the top of the list and ordered by mysql first. Then the numbers greater then 10 come next. How do I fix? Any help would be much appreciated.
Regards,
Matt
feyd | Please use
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Can someone tell me what I'm doing wrong here? This is my code:Code: Select all
echo "<head>
<meta http-equiv='Content-Language' content='en-au'>
<meta name='GENERATOR' content='Matty H'>
<meta http-equiv='Content-Type' content='text/html; charset=windows-1252'>
<title>Family Ladder</title>
</head>
<body bgcolor='#000000'>
<font face='Verdana' size='2' color='#FFFFFF'>
<form action='tippingtools.php' method='POST'>
<input type='hidden' name='function' value='processfamilyround'>
<table border='0' width='100%' cellpadding='1'>
<tr>
<td><font face='Verdana' size='2' color='#FFFFFF'><u><b>Name</b></u></font></td>
<td><font face='Verdana' size='2' color='#FFFFFF'><u><b>Score</b></u></font></td>
</tr>";
$query="SELECT * FROM families ORDER BY score DESC";
$result=mysql_query($query);
$num=mysql_numrows($result);
$i=0;
while ($i < $num) {
$name=mysql_result($result,$i,"name");
$score=mysql_result($result,$i,"score");
echo "
<tr>
<td><font face='Verdana' size='2' color='#FFFFFF'>$name</font></td>
<td><font face='Verdana' size='2' color='#FFFFFF'>$score</font></td>
</tr>
";
$i++;
}Regards,
Matt
feyd | Please use
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]Also when I went into phpMyAdmin and executed that same string "SELECT * FROM families ORDER BY score DESC" i got the exact same response as my code got and mysql ordered it with the scores less than 10 at the top then the rest in perfect order. Heres the sample:
Full Texts member name email score
Edit Delete 9 CCCCCC afadfsd@kjs.com 8
Edit Delete 5 Matthew's Family xxxxx@swiftdsl.com.au 65
Edit Delete 4 The Nyoongar Family xxxxx@inthebush.com 43
Edit Delete 8 BBBBB adfsd@kjs.com 30
Edit Delete 1 The Hare Family xxxxx@swiftdsl.com.au 28
Edit Delete 6 Hello Family family@family.com 27
Edit Delete 7 AAAAAA adfsd@kjs.com 20
Full Texts member name email score
Edit Delete 9 CCCCCC afadfsd@kjs.com 8
Edit Delete 5 Matthew's Family xxxxx@swiftdsl.com.au 65
Edit Delete 4 The Nyoongar Family xxxxx@inthebush.com 43
Edit Delete 8 BBBBB adfsd@kjs.com 30
Edit Delete 1 The Hare Family xxxxx@swiftdsl.com.au 28
Edit Delete 6 Hello Family family@family.com 27
Edit Delete 7 AAAAAA adfsd@kjs.com 20
The column types are:
member bigint(200) No auto_increment Browse Change Drop Primary Index Unique Fulltext
name varchar(200) No Browse Change Drop Primary Index Unique Fulltext
email varchar(200) No Browse Change Drop Primary Index Unique Fulltext
score varchar(200) No 0
Hope this helps
Regards,
Matt
member bigint(200) No auto_increment Browse Change Drop Primary Index Unique Fulltext
name varchar(200) No Browse Change Drop Primary Index Unique Fulltext
email varchar(200) No Browse Change Drop Primary Index Unique Fulltext
score varchar(200) No 0
Hope this helps
Regards,
Matt
I don't know how your site works, but I suspect that BIGINT is a bit of overkill for a score field. The range is from 0 to 18446744073709551615 if it's unsigned. Admittedly that's not quite enough to track my Counterstrike kills, but it's an awful lot..matth2004 wrote:Ah yes I see the problem, I changed it to BIGINT and then I ordered it and it worked perfectly. Thanks heaps for your help.
Yep well it's a football tipping site and the score is how many tips they have right. This runs in Australia in AFL Aussie Rules from round 1 to 22. 8 Games per round and if you fluke every round thats 8 * 22 = 176 points max. I don't think we will come anywhere near the interger max for BIGINT.
Regards,
Matt
Regards,
Matt