Sorting a Database?
Moderator: General Moderators
Sorting a Database?
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?
array_multisort http://www.php.net/manual/en/function.a ... tisort.php
Try this illustration:
This results in:
2: 1
5: 2
1: 3
6: 4
7: 5
4: 6
3: 7
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 />";
}2: 1
5: 2
1: 3
6: 4
7: 5
4: 6
3: 7
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: Sorting a Database?
Assuming mysql, something like this:
Or if you just need it for later, then:
Then you can use it like:
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++;
}Code: Select all
$i = 1;
while($row = mysql_fetch_assoc($result)) {
$rows[$i] = $row;
$i++;
}Code: Select all
foreach($rows as $place => $row) {
//use $row['total'] and $place
}mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Re: Sorting a Database?
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?
Thank you all for your replies! I'll work on it!