I’m a self-taught, hobby-coder volunteering for a small, local non-profit organization and am updating its website admin section using coldfusion.
A section of it is used to display the volunteers in an assigned order on the front end. I’ve added a “Display” column in the table where I set the order number of how the volunteers will be displayed. (1 is first, 2 is second, etc.)
I’d like to be able to dynamically rearrange the display order when updating or adding a record. Typically I’d bring in a senior programmer to do this but that’s not within the budget, so I thought I’d give it a shot.
Example:
1 = 1
2 = 2
3 = 3
Currently if I change 3 to 1, I get two 1s and 3 goes away. (expected)
1 = 1 (used to be 3)
1 = 1
2 = 2
I’d like the update to move all records below it to update as well.
(If I move 3 to 1)
1 = 1 (used to be 3)
2 = 2 (used to be 1)
3 = 3 (used to be 2)
OR
(If I move 3 to 2)
1 = 1
2 = 2 (used to be 3)
3 = 3 (used to be 2)
Currently I’m just updating each record individually, but that’s clearly not ideal, especially when the volunteer list grows into the teens or more.
I’ve searched for hours looking for guidance on how to do this but am at a loss as to where to start. Any help would be greatly appreciated.
Here’s the update form code:
<div class="form-group">
<label for="Display">Display Order</label>
<select name="display" class="form-control form-control-lg" required>
<option value="">Select Display Order</option>
<cfoutput query="GetBioDisplay"><option value="#GetBioDisplay.display#">#GetBioDisplay.display#</option></cfoutput>
</select>
</div>
Here’s the update query:
<cfquery name = "UpdateProfile" datasource="#dsn#">
UPDATE DBO.PROFILE
SET firstname = '#form.firstname#',
lastname = '#form.lastname#',
title = '#form.title#',
email = '#form.email#',
display = '#form.display#',
Picture = '#form.picture#',
profile = '#form.profile#',
credentials = '#form.credentials#'
WHERE ID = #url.id#
</cfquery>
Here’s the query I use for the user / front end:
<cfquery name="GetProfile" datasource="#request.dsn#">
SELECT ID, FIRSTNAME, LASTNAME, TITLE, EMAIL,
PICTURE, DISPLAY, PROFILE, CREDENTIALS
FROM DBO.PROFILE
ORDER BY DISPLAY
</cfquery>
You can use a CASE/WHEN conditional statement in your WHERE clause to update to the correct value, regardless of the direction you're moving the record. You only need to pass two values: The position you're updating from (url.id) and the position you're updating to (form.position).
SQL Fiddle MS SQL Server 2017 Schema Setup:
CREATE TABLE test(
display int NOT NULL,
item varchar(2000) NULL
) ;
INSERT INTO test (display, item)
VALUES
(1,'a')
, (2,'b')
, (3,'c')
, (4,'d')
, (5,'e')
, (6,'f')
, (7,'g')
, (8,'h')
, (9,'i')
, (10,'j')
;
Query - Order Up:
/* Move 8 to 4 - UP */
DECLARE @updDisp int = 8
DECLARE @updTo int = 4
UPDATE test
SET display = (CASE
WHEN display = @updDisp THEN @updTo
WHEN @updDisp < @updTo THEN display-1
WHEN @updDisp > @updTo THEN display+1
END
)
WHERE
( display BETWEEN @updDisp AND @updTo )
OR
( display BETWEEN @updTo AND @updDisp )
;
RESULTS
SELECT * FROM test ORDER BY display
| display | item |
|---------|------|
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | h |
| 5 | d |
| 6 | e |
| 7 | f |
| 8 | g |
| 9 | i |
| 10 | j |
Query - Order Down:
/* Move 4 to 8 - DOWN */
DECLARE @updDisp int = 4
DECLARE @updTo int = 8
UPDATE test
SET display = (CASE
WHEN display = @updDisp THEN @updTo
WHEN @updDisp < @updTo THEN display-1
WHEN @updDisp > @updTo THEN display+1
END
)
WHERE
( display BETWEEN @updDisp AND @updTo )
OR
( display BETWEEN @updTo AND @updDisp )
RESULTS
SELECT * FROM test ORDER BY display
| display | item |
|---------|------|
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | f |
| 7 | g |
| 8 | h |
| 9 | i |
| 10 | j |
So your CF code would look something like this:
//// process and validate form variables here ////
local.updDisp = url.id
local.updTo = form.display
//////////////////////////////////////////////////
<cfquery name="UpdateProfile" datasource="#dsn#">
UPDATE DBO.PROFILE
SET display = (
CASE
WHEN display = #val(local.updDisp)# THEN <cfqueryparam cfsqltype="cf_sql_integer" value="#local.updTo#">
WHEN #val(local.updDisp)# < #val(local.updTo)# THEN display-1
WHEN #val(local.updDisp)# > #val(local.updTo)# THEN display+1
END
)
WHERE ( display BETWEEN <cfqueryparam cfsqltype="cf_sql_integer" value="#local.updDisp#"> AND <cfqueryparam cfsqltype="cf_sql_integer" value="#local.updTo#"> )
OR
( display BETWEEN <cfqueryparam cfsqltype="cf_sql_integer" value="#local.updTo#"> AND <cfqueryparam cfsqltype="cf_sql_integer" value="#local.updDisp#"> )
</cfquery>
I'm not sure if you can use cfqueryparam in a CASE statement inside of a SET, and I don't have a way to test. Regardless, since we know we always should be working with integers, then the val() function should offer the same protection that the cfqueryparam does in this instance.
I'm also of the somewhat more paranoid mindset when it comes to user input. cfqueryparam is very good at what it does, but it doesn't protect against all issues. I'd still recommend doing some sort of pre-processing before you allow those values anywhere near your database.