Cold Fusion Data Sorting

By definition, rows in relational databases are in no particular sort order.

You need to add a column, let's call it "priority" and it gets it's value when the row is input.

Logic

If the position you want  is taken, have a loop to alter the priorities (by adding or subtracting 1 - mhowever you want to do it) and then add the new row in with the now not-used priority.

You will need to use CFTRANSACTION around the code so that multiple users don't cause undesirable updates.

yes, I made a typo in my first update statement... should be the sort field

if you're just resorting existing records, you'll use an UPDATE. if you're adding a new record and assigning a sort order, you'll use an INSERT. you'll probably have to do an additional query first to determine UPDATE vs. INSERT and then just do the appropriate one.

Both Mause and jyokum are right, <cfqueryparam> is better, but, it's easier to read without that tag. ;-))

Cheers,

Chris

Hi,

the display of records - depends in what order u want them to be sorted. so lets say if u have the follwowing 3 records in ur DB
Current Record
Record: 1 Sort: 1
Record: 2 Sort: 2
Record: 3 Sort: 3

New Record
Record: 4 Sort: 2

New Data Set - will look like this in teh DB
Record: 1 Sort: 1
Record: 2 Sort: 3 [* updated from 2 to 3]
Record: 3 Sort: 4 [* updated from 3 to 4]
Record: 4 Sort: 2 [sort order that falls inbetween the others - new rec that is inserted in the table]

u can achieve the above - by updating ur previous set of rows by [i hope u have a key that defines the rows] - use it to identify the rows & update them using:
update table set sort = sort - 1;

then do a select from ur table as
select * from table order by sort

so u ge the above 4 records displayed to teh user as
Record: 1 Sort: 1
Record: 4 Sort: 2
Record: 2 Sort: 3
Record: 3 Sort: 4

HTH
let me know

K'Rgds
Anand

jeffbeasley,
This has been open 59 days and there hasn't been a comment added in 56 days.
Please select a comment as the solution or give us an update.

jyokum