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!