Here's a webpage I designed (with Loftboy's help) for an online trading card application. To see this in action goto http://mtgotradingpost.com/login/login.cfm and login with user/pass: test/test.

Here's the question: can this page be done better? I know the checkboxes are ugly - what I'm talking about is the underlying SQL Query. Anything to make is shorter or smoother?



BTW, the three tables referenced above are

mycards - contains the cards that individual users own
tblcards - contains details on each card
users - contains user login information

Please let me know your thoughts!

Two things..

if you used <cfparam> you wouldn't have to do all that isDefined crap everytime.

For the query, you could try using a loop to iterate over the query since it seems all that changes is the spellType. Just make sure you have a check to leave the union off if its the last one.

I can elaborate a little more if needed.

Elaborate, please! I'm not familiar with CFPARAM, and I'm also not sure how you're thinking of implementing the loop.

Nice icon, BTW.

<cfparam> is used to set a default value- a kind of "catch-all" if the variable doesn't exist- like with checkboxes on a form field to use it just go:

<cfparam name="form.checkboxName" default="0">

where name is the name of your variable and default is your default value.

For the query, I'll post an example of what I was thinking when I have more time later today.

So instead of this:

<input type="checkbox" name="white" value="white"<cfif isDefined("form.white") and trim(form.white) eq "white"> CHECKED</cfif>>White

which checks for the existence of the variable before making the comparison, I would simply have a block of this before the query:

<cfparam name="form.white" default="0">
<cfparam name="form.black" default="0">
<cfparam name="form.blue" default="0">
.

and the inputs would look like this:

<input type="checkbox" name="white" value="white"<cfif trim(form.white) eq "white"> CHECKED</cfif>>White

BTW, the code we're talking about here is designed specifically to keep the checkbox values after the form is submitted, since the form actually submits to itself.

Jamie,

This is kinda what I was thinking although my brain is fried this week and I'm not sure I can explain it clearly.



Where the loop is a comma delimited list of the items you want to union together. If you are getting this list from a query, you can use ValueList() to convert it.

Hi Jamie,

The way your using checkboxes is typical of coldfusion newbies. I used to do it the same way when I 1st got started.

Try this train of thought instead; Name all your check boxes the same.
and then loop throught the list. (checkboxes values are returned as a list by default in Coldfusion)

So....

<cfparam name="color" default="">

<input type="checkbox" name="color" value="W"
<cfif trim(form.white) eq "W"> CHECKED</cfif>>White

<input type="checkbox" name="color" value="B"
<cfif trim(form.white) eq "B"> CHECKED</cfif>>White

<!--- Calling page just before your Query --->

<cfif isDefined('form.fieldnames') and Form.fieldnames EQ 'color'>
<cfoutput>
<!--- we create a var for the list (name it) --->
<cfset colorlist="#form.color#">
<!--- now we format the list so it's SQL friendly (in SQL format) --->
<cfset colorlist = #ListchangeDelims(colorlist, "','")#>
</cfoutput>
</cfif>

<cfquery name="showmyCards" datasource="#dsn_name#" username="#dsn_login#"
password="#dsn_password#">
SELECT mycards.userID, mycards.CardID, users.username, tblcards.CardTitle, tblcards.ColorCode, mycards.own, mycards.trade, tblcards.RulesText
FROM mycards, users, tblcards
WHERE mycards.userID = users.userID
AND mycards.CardID = tblcards.CardID
AND users.username = "#getAuthUser()#"
AND tblcards.ColorCode IN ('#colorlist#')
</cfquery>


This is for the color codes only! You could do the same with spelltypeID.
This is pseudo code but I'm pretty sure it should work for you.

In my defense, I originally had all the checkboxes named the same, but I had to rename them in order to preserve the values of the checkboxes on submit. Now, my question is will the code you listed return results for both colors when multiple checkboxes are selected, I.e. if I'm looking for black cards + white cards so I check both, will your code return both or return neither since no cards are black AND white?

Of course... Copy and paste the following into a new coldfusion template and name it test.cfm. RUN IT and view the ouput. It will help you make senese of what's happening.



[/code]