Updating Multiple Form Values at once

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kenji776 - 29 Oct 2005 07:12 GMT

Hey everyone, I have a looping query that gets all the votes for a project from
a database and puts a dropdown list next to each project for voteing. You can
see the page here at

http://www.digitalswordsmen.com/Digital%20Swordsmen/prod/Site_Projects.cfm

Anywho I want instead of a submit button for each item, i want one submit
button at the bottom of the whole list so a user can submit a multitude of
votes at once, instead of having to vote for each project individually. I just
don't know how the query to update the values is going to work, and such. This
all sounds kinda wierd, but click that link above and it should make more
sence, I just want one submit button instead of one by each project so they
user can do all voting with one click, and I just don't know how to write the
queries for it. Ill post the code, any help is appreciated, thanks.

<html>
<head>
<cfparam name="listfunc" default="project_name" type="string">
<cfquery datasource="users" name="getprojects">
    SELECT *
    FROM Projects
    order by #listfunc#
</cfquery>
<title>Site Project Viewer</title>
<cfif isdefined("form.submit")>
    <cfset interest = #getprojects.public_interest# + #form.voteval#>
    <cfquery name="update_public" datasource="users">
        UPDATE Projects
        SET Public_interest = '#interest#'
        Where ID = #form.ID#
    </cfquery>
    <cflocation url="#cgi.SCRIPT_NAME#" addtoken="no">
</cfif>
<link href="mainload.css" rel="stylesheet">
<META NAME="Description" CONTENT="Digital Swordsmen- Free Music, Games,
Videos, Programs, File Sharing, E-mail, Web logs and more. .">
<META NAME="Keywords" CONTENT="Swordsmanship, digital, swordsmen, sword,
swords, swordfighting, anime, manga, snes, nes, gba, sega 3x, roms, free,
downloads, music, games, programs, web log, personal profiles, forums, chat,
pictures, downloads.">

<link rel="stylesheet" href="mainLoad.css">
<style type="text/css">

</style>
</head>

<body>
<span class="style1">Site Projects <br>
</span>
<hr>
<br>
Here you can view upcoming projects that the site admins are working on, and
vote on what you would like to see most, so we can focus on what the public
wants. Please only vote once.<br>
<span class="style3"><span class="style6">Order Results By: </span></span>
<form name="orderitems" method="post">
<select name="listfunc">
<option value="project_name">Project Name A-Z</option>
<option value="project_name desc">Project Name Z-A</option>
<option value="description">Description A-Z</option>
<option value="description desc">Description Z-A</option>
<option value="type">Type A-Z</option>
<option value="type desc">Type Z-A</option>
<option value="difficulty">Difficulty A-Z</option>
<option value="difficulty desc">Difficulty Z-A</option>
<option value="status">Status A-Z</option>
<option value="status desc">Status Z-A</option>
<option value="public_interest">Public Interest 0-99</option>
<option value="public_interest desc">Public Interest 99-0</option>
</select>
<input type="submit" name="submit2" value="sort list">
</form>
<br>
<br>
<cfoutput>
<table border="1">
    <tr>
        <td><span class="style2">Project Name</span></td>
        <td><span class="style2">Description</span></td>
        <td><span class="style2">Type</span></td>
        <td><span class="style2">Difficulty</span></td>
        <td><span class="style2">Status</span></td>
        <td><span class="style2">Public Interest</span></td>
        <td><span class="style2">Date Posted</span></td>
        <td><span class="style2">Your Interest</span></td>
    </tr>
    <cfloop query="getprojects">
    <tr>
        <td>#getprojects.project_name#</td>
        <td>#getprojects.description#</td>
        <td>#getprojects.Type#</td>
        <td>#getprojects.difficulty#</td>
        <td>#getprojects.status#</td>
        <td>#getprojects.public_interest#</td>
        <td>#getprojects.date_time#</td>
        <td>
        <form name="vote" method="post">
            <input type="hidden" name ="ID" value="#getprojects.ID#">
            <select name="voteval">
                <option>1</option>
                <option>2</option>
                <option>3</option>
                <option>4</option>
                <option>5</option>
                <option>6</option>
                <option>7</option>
                <option>8</option>
                <option>9</option>
                <option>10</option>
            </select>
            <br>
            <br>
            <input type="submit" value="Vote" name="submit">
        </form>
        </td>
    </tr>
    </cfloop>
</table>
</cfoutput>
</body>
</html>

Reply to this Message

PaulKD - 29 Oct 2005 13:14 GMT

kenji776,

The code you require is fairly straight-forward, but before I submit any code
I have a couple of questions.

Wont visitor B overwrite visitor A's votes? How are you allocating the
form.id variable?

Regards.

Reply to this Message

Dan Bracuk - 29 Oct 2005 14:35 GMT

Put everything in one form, then you only need one button.

Originally posted by: kenji776
Hey everyone, I have a looping query that gets all the votes for a project
from a database and puts a dropdown list next to each project for voteing. You
can see the page here at

http://www.digitalswordsmen.com/Digital%20Swordsmen/prod/Site_Projects.cfm

Anywho I want instead of a submit button for each item, i want one submit
button at the bottom of the whole list so a user can submit a multitude of
votes at once, instead of having to vote for each project individually. I just
don't know how the query to update the values is going to work, and such. This
all sounds kinda wierd, but click that link above and it should make more
sence, I just want one submit button instead of one by each project so they
user can do all voting with one click, and I just don't know how to write the
queries for it. Ill post the code, any help is appreciated, thanks.

Reply to this Message

kenji776 - 29 Oct 2005 18:11 GMT

Good question about visitor B overwritting Visitor A's votes. I already took
this into account, look closly at this..

<cfif isdefined("form.submit")>
    <cfset interest = #form.Value# + #form.voteval#>
    <cfquery name="update_public" datasource="users">
        UPDATE Projects
        SET Public_interest = '#interest#'
        Where ID = #form.ID#
    </cfquery>
    <cflocation url="#cgi.SCRIPT_NAME#" addtoken="no">
</cfif>

The variable interest holds the current value of public_interest column, and
the vote value of the project voted on added together. It then sends that to
the query to update with. Also every proects has its own ID, which is an
autonumber/primary key assigned by the database. And can I do this all in one
form, i mean ill give it a shot, but its one of those things where that seems
to easy to work you know? Seems like id have to like pass the whole form
structure, and all variables would have to be named different and such. Ill
give it a whack and let ya know how it goes. Thanks.

Reply to this Message

kenji776 - 29 Oct 2005 18:15 GMT

Well I tried and got this

The value "12,2,0,0,0,0,10,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,13" cannot be
converted to a number

The error occurred in D:\FTP\Digital Swordsmen\Prod\Site_Projects.cfm: line 11

9 : <title>Site Project Viewer</title>
10 : <cfif isdefined("form.submit")>
11 :     <cfset interest = #form.Value# + #form.voteval#>
12 :     <cfquery name="update_public" datasource="users">
13 :         UPDATE Projects

Seems as though everything is being passed correctly, just not sure how to
make the interest variable work now. I am really bad with working with this
kind of stuff, like anything that invlolves slightly more than simple queries,
sorry. Any help is good. Thanks.

Reply to this Message

Dan Bracuk - 29 Oct 2005 18:20 GMT

It might not be a number, but it sure looks like a list of numbers. Shouldn't
be too hard to add them up if that's your objective.

Originally posted by: kenji776
Well I tried and got this

The value "12,2,0,0,0,0,10,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,13" cannot be
converted to a number

The error occurred in D:\FTP\Digital Swordsmen\Prod\Site_Projects.cfm: line 11

9 : <title>Site Project Viewer</title>
10 : <cfif isdefined("form.submit")>
11 :     <cfset interest = #form.Value# + #form.voteval#>
12 :     <cfquery name="update_public" datasource="users">
13 :         UPDATE Projects

Seems as though everything is being passed correctly, just not sure how to
make the interest variable work now. I am really bad with working with this
kind of stuff, like anything that invlolves slightly more than simple queries,
sorry. Any help is good. Thanks.

Reply to this Message

kenji776 - 29 Oct 2005 19:57 GMT

Its not much i need to add them up, each on of those numbers represents the
total public interest of each project. Basically how it works is like this
1) The page pulls out all the projets and votes from the projects table in the
users database
2) It loops through the results creating a row in a table including name,
description, difficulty, public_interest and a dropdown list for each
project.
3) The user marks their interest in each project by using the dropdown lists
provided, and then votes
4) All the project ID's and value of their votes are passed to the query that
updates the database
5) The values of the new votes are added to the old total, coming up with a
new public_interest level which is then set in the database. Example: If the
project name "Fix the gallery" had an interest level of 10 previously, and a
new user saw that project and it interested him 5 points, he could mark 5 in
the dropdown list, and click vote. 5 would then be added to the original 10,
creating a new total public_interest on that project of 15.

All voteing should be able to be done with one click of the vote button. A
user could simply mark their interest levels in all projects and click vote,
all that users interest levels would be added to the corresponding projects,
resulting a new total public_interest level for each project they voted on. So
a user could see the projects

ID-----Title-----------------------Description---------------------Public
Interest------------Vote
1------one-----------------------Project
one---------------------------3-------------------------*Dropdown List*
2------Two-----------------------Project
Two--------------------------6-------------------------*Dropdown List*
3------three----------------------Project
Three------------------------3-------------------------*Dropdown List*
4------four------------------------Project
Four--------------------------8-------------------------*Dropdown List*
5------five------------------------Project
Five---------------------------3-------------------------*Dropdown List*

And the viewing user decided that
project one interested him 4,
project two interested him 8
project thee interested him 2
project four interested him 9
project five interested him 1.
His votes would all be added to the respective runnning tallies for each
project making the new totals.
Project Total
1---------------7
2--------------14
3--------------5
4-------------17
5-------------4

See how each projects public interest is the old total, plus the value of the
new vote for it? This is what I want, all done in one click of the vote button.
I hope this was clear becasue i dont' really know how else to explain it.
thanks for any help.

Reply to this Message

mxstu - 29 Oct 2005 20:27 GMT

kenji776,

I agree with Dan about using a single form. This would allow users to vote
for multiple items at once. However, I would use dynamic form field names
instead of giving all the fields the same name. It avoids some of the
potential pitfalls with lists and empty strings.

Just use the cfquery #currentRow# to give each set of form fields a unique
name. When the form is submitted, loop from 1 to the total number of fields and
extract the values for each set of form fields. If the interest level is > 0,
run an update. NOTE - The attached code assumes the data type of
"Public_Interest" field is "numeric" with a default value of "0". It will not
work if the field is "text".

<cfparam name="listfunc" default="project_name" type="string">

<cfif isDefined("form.numberOfRows")>
    
    
    <cfloop from="1" to="#val(form.numberOfRows)#" index="row">
        <cfset projectID = val(form["ID"& row])>
        <cfset interest = val(form["voteVal"& row])>
        
        <cfif interest gt 0>
         <cfquery name="update_public" datasource="#yourDSN#">
            UPDATE     Projects
            SET     Public_interest = Public_interest + #interest#
            Where     ID = #projectID#
        </cfquery>
        </cfif>
    </cfloop>
</cfif>
<cfquery datasource="#yourDSN#" name="getprojects">
    SELECT     ID, project_name, description, type, difficulty, status,
public_interest
    FROM     Projects
    ORDER BY #listfunc#
</cfquery>

<span class="style1">Site Projects <br></span>
<hr>
<br>
<cfoutput>
<form name="vote" method="post" action="#CGI.SCRIPT_NAME#?listfunc=#listfunc#">
<table border="1" width="100%">
    <tr>
        <td><span class="style2">Project Name</span></td>
        <td><span class="style2">Description</span></td>
        <td><span class="style2">Type</span></td>
        <td><span class="style2">Difficulty</span></td>
        <td><span class="style2">Status</span></td>
        <td><span class="style2">Public <br>Interest</span></td>
        <td><span class="style2">Your Interest</span></td>
    </tr>
    <cfloop query="getprojects">
    <tr>
        <td>#getprojects.project_name#</td>
        <td>#getprojects.description#</td>
        <td>#getprojects.Type#</td>
        <td>
            <cfif #getprojects.difficulty# EQ "Easy">
                <font color="##00FF00">
                <cfelseif getprojects.difficulty EQ "moderate" >
                    <font color="##FFFF00">
                <cfelseif getprojects.difficulty EQ "Hard" >
                    <font color="##FF0000">
                <cfelseif getprojects.difficulty EQ "Unknown" >
                    <font color="##0000CC">
            </cfif>
            #getprojects.difficulty#</font
        </td>
        <td>
            <cfif getprojects.status EQ "Queued">
                <font color="##FFFF00">
                <cfelseif getprojects.status EQ "Working" >
                    <font color="##0000FF">
                <cfelseif getprojects.status EQ "Failing" >
                    <font color="##FF0000">
                <cfelseif getprojects.status EQ "Paused" >
                    <font color="##CCCCCC">
                <cfelseif getprojects.status EQ "Done" >
                    <font color="##00FF00">
                <cfelseif getprojects.status EQ "Abandoned" >
                    <font color="##660066">
            </cfif>
            #getprojects.status#</font>
        </td>
        <td>#getprojects.public_interest#</td>
        <td>
            
            <input type="hidden" name ="ID#CurrentRow#" value="#getprojects.ID#">
            <select name="voteVal#CurrentRow#">
                <cfloop from="0" to="10" index="currVal">
                <option value="#currVal#">#currVal#</option>
                </cfloop>
            </select>
            <br>
            <br>
        </td>
    </tr>
    </cfloop>
</table>
Submit your votes <input type="button" value="Vote"
onClick="this.form.submit();">

<input type="hidden" name="numberOfRows" value="#getprojects.recordCount#">
</form>
</cfoutput>

Reply to this Message

kenji776 - 29 Oct 2005 20:49 GMT

Awsome thanks, I am gonna try that code right now!

Reply to this Message

mxstu - 29 Oct 2005 23:16 GMT

kenji776,

Glad I could help and thanks to PaulKD and Dan Bracuk too for their observations.

Reply to this Message

mxstu - 29 Oct 2005 19:42 GMT

>UPDATE Projects
>SET Public_interest = '#interest#'

I noticed you're using single quotes around the #interest# value. Is the
Public_interest column data type "text" or "number"? It should be data type
"number" - probably with a default value of zero (0). That would make your
updates much easier and would also avoid the problem of inconsistencies
described below.

UPDATE Projects
SET Public_interest = Public_interest + 1
WHERE ID = #someID#

The way you're doing it now (using the previous value stored in a form field)
isn't good. I could pull the page at 6AM and the votes for are project ID (1)
are "0". I then leave for school. In the mean time 12 other people visit the
page and add their votes, so the votes for project ID (1) are now "112" I come
home hours later and submit the form. Because you're using the value the form
pulled at 6AM, your UPDATE will wipe out all the previous votes and only record
mine + "0".

Reply to this Message

mxstu - 29 Oct 2005 20:05 GMT

I think I understand exactly what you're trying to do, but I believe the same
issue still applies.

If I pull the form at 6AM the previous value stored in the form field will be
"10". Other people vote after that raising the interest level to "85". I
submit my form 8 hours later and the new value of the interest will be my "5"
vote plus "10" ---> "15". The previous "85" will be completely wiped out.

Reply to this Message

Webmaster Forum / ColdFusion / Advanced Techniques / October 2005

Updating Multiple Form Values at once