Page 1 of 1
Chronologically display a list
Posted: Wed Sep 06, 2006 5:45 am
by matth2004
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
Posted: Wed Sep 06, 2006 5:57 am
by CoderGoblin
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.
Posted: Wed Sep 06, 2006 6:02 am
by rsmarsha
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.
Posted: Wed Sep 06, 2006 6:27 am
by onion2k
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:
Code: Select all
select membername, score from whatevertable order by score desc
Posted: Wed Sep 06, 2006 6:28 am
by matth2004
Hmm I'm a little bit new to all this. Would you be able to explain more clearly how to order? I need to order by the score column which is different for everyone. I don't currently have any code on it as the base to it is sorting the data.
Regards,
Matt
Posted: Wed Sep 06, 2006 6:30 am
by onion2k
At the end of your line of SQL where you select the data from the database put "ORDER BY ", then your column name, then either " ASC" if you want ascending order (eg 1,2,3,4) or " DESC" if you want descending order (eg 4,3,2,1).
Posted: Wed Sep 06, 2006 6:34 am
by matth2004
Sorry was replying to the post above yours when you posted the other and I didn't see it. You answered it perfectly! Thanks heaps,
Matt
Posted: Wed Sep 06, 2006 6:48 pm
by matth2004
feyd | Please use 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++;
}
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
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]
Posted: Wed Sep 06, 2006 6:54 pm
by matth2004
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
Posted: Wed Sep 06, 2006 7:52 pm
by Luke
what type is the column "score"?
Posted: Thu Sep 07, 2006 2:54 am
by matth2004
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
Posted: Thu Sep 07, 2006 2:56 am
by matth2004
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.
Regards,
Matt
Posted: Thu Sep 07, 2006 2:56 am
by Luke
Posted: Thu Sep 07, 2006 3:31 am
by onion2k
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.
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..

Posted: Thu Sep 07, 2006 3:37 am
by matth2004
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