passing value from menu

[Guest]

Aloha all, I am semi new to coldfusion, worked in clientserver and mianframe for 15 years and have built a few sites.

This is my first datadriven site although I have done DB work for 15 years. My question is I have a list of artists. When the user clicks on one I want to be able to pass that value into the select query ie select * from artists where artist name = "sValue"; How do I retrieve the value from the menu to pass. I only want to use one page rather than building a seperate page for each artist.

Much Mahalo,
Mark

[Dan_Bracuk]

First, think about duplicate names.

Next, assuming the user is clicking on a link, the where clause of your query will include

and fieldname = '#url.variablename#'

unless it's numeric in which case you lose the quotes.

[insuractive]

Dan's point about duplicate names is an important one. You're probably going to be better off using some sort of unique ID to indentify your artists, rather than names. Plus, passing text with spaces via URL variables can get messy. Your best bet may be to do something like this:

ListArtists.cfm
----------------------------------------
<cfquery name="qArtists" ...>
SELECT firstname, lastname, artistid
FROM artists
ORDER BY lastname, firstname
</cfquery>
<ul>
<cfoutput query="qArtists">
<li><a href="artistpage.cfm?artist=#artistid#">#lastname#, #firstname#</li>
</cfoutput>
</ul>

ArtistPage.cfm
------------------------------------
<cfparam name="URL.artist" default="">

<cfquery name="qArtistInfo" ...>
SELECT *
FROM artists
WHERE artistid = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#URL.artist#">
</cfquery>
<cfoutput>
#qArtistInfo.firstname# #qArtistInfo.lastname#<br/>
<!--- Other Artist Data --->
</cfoutput>

[insuractive]

A couple of notes if you're new to CF:

1) When you are passing variables via the URL, its always a good idea to default them using <cfparam>. You may also want to add some logic so that if URL.artist eq "", then you redirect back to your list

2) Integers are much easier to pass via URL variables than names. If you are worried about controlling which artists users have access to you can take steps to encrypt or obfuscate the artist ids.

3) Your queries may run a little quicker if you can define the fields you want to retrieve from your table instead of using "*". However, I know how much a pain it can be to go back and add fields to your code when you change your database scheme.

4) Using <cfqueryparam> does 2 things for you: First, it speeds up your queries (which is always good). Second, it provides you some protection against Cross Site Scripting and SQL injection built into the tag.

5) If you query is only returning 1 record, I believe the current best practice is to use <cfoutput>#qQueryName.myField#</cfoutput> instead of <cfoutput query="qQueryName">#myField#</cfoutput>. Since outside of a loop, #qQueryName.myField# always returns the value of myField in the first row of the recordset.

Hope that helps!