Sorting a Database?

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
carleihar
Forum Commoner
Posts: 36
Joined: Fri Nov 13, 2009 5:59 pm

Sorting a Database?

Post 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?
Reviresco
Forum Contributor
Posts: 172
Joined: Tue Feb 19, 2008 4:18 pm
Location: Milwaukee

Re: Sorting a Database?

Post 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
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Sorting a Database?

Post 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
}
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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Sorting a Database?

Post 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/
carleihar
Forum Commoner
Posts: 36
Joined: Fri Nov 13, 2009 5:59 pm

Re: Sorting a Database?

Post by carleihar »

Thank you all for your replies! I'll work on it!
Post Reply