Solved

Cold Fusion Data Sorting

Posted on 2003-11-07
10
252 Views
Last Modified: 2013-12-24
I am trying to create a custom sort of data....I want to be able to have multiple records that are inputted into a database...and when someone adds another record with a sort order that falls inbetween the others it shifts the others down

------------------------------------------------
Example: (Data Fields: Record,Sort)
Current Record
Record: 1 Sort: 1
Record: 2 Sort: 2
Record: 3 Sort: 3

New Record
Record: 4 Sort: 2

New Data Set
Record: 1 Sort: 1
Record: 4 Sort: 2
Record: 2 Sort: 3
Record: 3 Sort: 4
------------------------------------------------
I am using mySQL and Cold Fusion...

I invision something that checks any other field for sort position and if exists shifts all other down through an update....

I am having trouble with the methodology of quickly comparing and shifting data fields..

Thanks,
Jeff
0
Comment
Question by:jeffbeasley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 1

Expert Comment

by:kjuliff
ID: 9704200
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.
0
 
LVL 12

Accepted Solution

by:
jyokum earned 168 total points
ID: 9704712
this should do it

<cfset sortPriority = 2>
<cfset recordValue = 4>

<cftransaction>
      <cfquery name="qryResort" datasource="EE">
            UPDATE tablename
            SET sort = sort + 1
            WHERE record >= <cfqueryparam value="#sortPriority#" cfsqltype="cf_sql_numeric">
      </cfquery>
      
      <cfquery name="qryUpdate" datasource="EE">
            UPDATE tablename
            SET sort = <cfqueryparam value="#sortPriority#" cfsqltype="cf_sql_numeric">
            WHERE record = <cfqueryparam value="#recordValue#" cfsqltype="cf_sql_varchar">
      </cfquery>
</cftransaction>
0
 
LVL 4

Assisted Solution

by:procept
procept earned 166 total points
ID: 9706489
Hi,

jyokum is quite right, I would just change the update statements to use the sort field, not the record field (or recortd number for that matter):

UPDATE myTable
SET sort = sort + 1
WHERE sort >= #newRecord_Sort#


Then of course you need to do an INSERT for the new record, not an UPDATE:

INSERT INTO myTable (field1, field2, sort)
VALUES ('#value1#', '#value2#', #newRecord_Sort#)

HTH,

Chris
0
Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

 
LVL 10

Assisted Solution

by:Mause
Mause earned 166 total points
ID: 9706846

Procept is totaly right.

But I think you should also use the cfqueryparam as jyokum used.
So if you combine those 2 code you well get this:

<cftransaction>
     <cfquery name="qryResort" datasource="EE">
          UPDATE tablename
          SET sort = sort + 1
          WHERE sort >= <cfqueryparam value="#SORT#" cfsqltype="cf_sql_numeric">
     </cfquery>
     
     <cfquery name="qryUpdate" datasource="EE">
          INSERT INTO tablename(Record, sort)
          VALUES (<cfqueryparam value="#RECORD#" cfsqltype="cf_sql_numeric">,<cfqueryparam value="#SORT#" cfsqltype="cf_sql_numeric">)
     </cfquery>
</cftransaction>

Mause
0
 
LVL 12

Expert Comment

by:jyokum
ID: 9708591
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.
0
 
LVL 4

Expert Comment

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

Cheers,

Chris
0
 
LVL 17

Expert Comment

by:anandkp
ID: 9713230
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
0
 
LVL 12

Expert Comment

by:jyokum
ID: 10049729
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
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question