Solved

Cold Fusion Data Sorting

Posted on 2003-11-07
10
247 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
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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now