Solved

Cold Fusion Data Sorting

Posted on 2003-11-07
10
249 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
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.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

810 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