Solved

Cold Fusion Data Sorting

Posted on 2003-11-07
10
250 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

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

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

713 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