?
Solved

Cold Fusion Data Sorting

Posted on 2003-11-07
10
Medium Priority
?
255 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 672 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 664 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 664 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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
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…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

765 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