If you don't mind a few extra SQL calls, you can do a pseudo linked-list; that is, have each element have a 'previous' entry that points to the unique ID of the next element in the list, or NULL if this is the first entry. Then you can do a loop over a SQL statement such as:
my @order;
my $sth_1 = $dbh->prepare( "SELECT id FROM TABLE WHERE previous IS NUL
+L" ) or die $DBI->errstr;
my $sth_2 = $dbh->prepare( "SELECT id FROM TABLE WHERE previous IS ?"
+) or die $DBI->errstr;
$sth_1->execute() or die $DBI->errstr;
if ( $sth_1->rows() > 0 ) {
my ($id) = $sth_1->fetchrow_array();
push @order, $id;
$sth_2->execute( $id ) or die $DBI->errstr;
while ( $sth_2->rows() > 0 ) {
my ( $id ) = $sth_2->fetchrow_array();
push @order, $id;
$sth_2->execute( $id ) or die $DBI->errstr;
}
}
(That code could probably be optimized for logic of course.)
-----------------------------------------------------
Dr. Michael K. Neylon - mneylon-pm@masemware.com
||
"You've left the lens cap of your mind on again, Pinky" - The Brain
"I can see my house from here!"
It's not what you know, but knowing how to find it if you don't know that's important
| [reply]
[d/l] |
There are a few things that I can think of to do, that wouldn't involve much SQL.
The SQL Way:
- Make sure you
CREATE INDEX mycleverindexname on mybadasstable (myslickcolumn)
This will speed you up a ton, and the space tradeoff is almost always worth it if you're going to be doing a lot of sorting on it.
- Re-ordering them really isn't scalable. That cron job is going to sting if your site gets above X number of users.
Or you can do it the perl way
Typically, I'd like to think that perl is better for sorting small to moderate result sets. For instance instead of doing a huge "select" with a parameter, and then sorting by a certain value, then doing the "order by" statement when you know that you're only going to get a few results, bite the bullet, get out of your sql (and whatever locks it might have) and do it quickly in perl.
Hope this helps.
--jay | [reply]
[d/l]
[select] |
you could keep a column in your "user" table named "sort_order" which stores the display sequence as chosen by the user. so whenever you needed to display the data , all you need to do is sort the data according to "sort_order" rather than the primary key. | [reply] |