Chronologically display a list

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
matth2004
Forum Commoner
Posts: 40
Joined: Wed Sep 06, 2006 3:26 am

Chronologically display a list

Post 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
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Post 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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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
Last edited by onion2k on Wed Sep 06, 2006 6:28 am, edited 1 time in total.
matth2004
Forum Commoner
Posts: 40
Joined: Wed Sep 06, 2006 3:26 am

Post 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
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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).
matth2004
Forum Commoner
Posts: 40
Joined: Wed Sep 06, 2006 3:26 am

Post 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
matth2004
Forum Commoner
Posts: 40
Joined: Wed Sep 06, 2006 3:26 am

Post by matth2004 »

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++;
}
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]
matth2004
Forum Commoner
Posts: 40
Joined: Wed Sep 06, 2006 3:26 am

Post 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
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

what type is the column "score"?
matth2004
Forum Commoner
Posts: 40
Joined: Wed Sep 06, 2006 3:26 am

Post 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
matth2004
Forum Commoner
Posts: 40
Joined: Wed Sep 06, 2006 3:26 am

Post 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
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

:wink:
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.. :wink:
matth2004
Forum Commoner
Posts: 40
Joined: Wed Sep 06, 2006 3:26 am

Post 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
Post Reply