Page 1 of 1

Sorting a Database?

Posted: Wed May 12, 2010 2:08 pm
by carleihar
If I query a database and use ORDER BY, how would I retrieve the number in the order that an item is? I want to be able to place that number into a database for further use. For instance, if id of 23 has a total of 945 and id of 16 has a total of 847, I want to give id of 16 a place of '1' and id of 23 a place of '2'. How would I go about doing this?

Re: Sorting a Database?

Posted: Wed May 12, 2010 4:13 pm
by Reviresco
array_multisort http://www.php.net/manual/en/function.a ... tisort.php

Try this illustration:

Code: Select all

$id = array(1, 2, 3, 4, 5, 6, 7);
$total = array(345, 945, 65, 124, 847, 209, 199);

array_multisort($total, SORT_DESC, $id);

$place = array();

foreach($id as $k=>$v) {
	$place[$v] = $k+1;
	}
foreach($place as $k=>$v) {
	echo "$k: $v<br />";
	}
This results in:

2: 1
5: 2
1: 3
6: 4
7: 5
4: 6
3: 7

Re: Sorting a Database?

Posted: Wed May 12, 2010 4:27 pm
by AbraCadaver
Assuming mysql, something like this:

Code: Select all

$result = mysql_query("SELECT * FROM table_name ORDER BY total ASC");

$i = 1;
while($row = mysql_fetch_assoc($result)) {
	//INSERT INTO somewhere SET id=$row['id'], place=$i
	$i++;
}
Or if you just need it for later, then:

Code: Select all

$i = 1;
while($row = mysql_fetch_assoc($result)) {
	$rows[$i] = $row;
	$i++;
}
Then you can use it like:

Code: Select all

foreach($rows as $place => $row) {
	//use $row['total'] and $place
}

Re: Sorting a Database?

Posted: Wed May 12, 2010 4:38 pm
by Eran
If you want to do it in SQL only, read this article - http://www.xaprb.com/blog/2006/12/02/ho ... -in-mysql/

Re: Sorting a Database?

Posted: Wed May 12, 2010 8:15 pm
by carleihar
Thank you all for your replies! I'll work on it!