General CFML Queries for Newbie

welcome to the forums!

couple of pointers for you:

1) since it looks like you are very new to cf, i highly recommend the
"Web Application Construction Kit" (a.k.a. " CF WACK") set of books by
Ben Forta & Co. - an invaluable resource for someone new to CF or as a
reference for a seasoned cf developer.

2) since it also looks like you are quiet new to database
development/SQL, the books "Teach yourself SQL in 10 minutes" and
"Database design for mere mortals" often come highly recommended on
these forums and other mailing lists/forums.

3) go right now to adobe.com and download the free PDF versions of 'CFML
reference' and 'CF Developers' Guide'. You can also use online livedocs
or the documentation that comes with CF download, but i personally find
the PDF versions a lot more useful for quick reference when needed.

4) learn to rigorously use <cfqueryparam> in all your sql queries. will
protect you from imminent SQLi attacks.

5) learn to NOT use SELECT * in your queries - select just the fields
you need in your resultset. it will a) make the db interactions faster
since you are returning limited amount of data; b) save you frustration
later when you alter your db schema and your queries start to error out
or not return the newly added fields...

6) now, for your specific issues:

(i assume you have a reason to use method="get" instead of post in your
form and will not delve into that, since it seems you already know to
check for URLscope variables then instead of FORM scope as you have to
with method="post")

a) your <cfif> statement checks for existence of a url variable and runs
a query if one is present, which is fine. however, your <cfelse> part
will run only if the url variable in question is NOT defined, in your
case on initial page load only.
so, what you may want to do is:
- put your query inside another nested cfif block that checks that a
search term has been entered (i.e. <cfif len(trim(url.productname)) gt
0> wil check that url.productname is not an empty string), because a
textbox will be submitted with the form even if it was left empty.
[alternatively, you can add a second check in your existing CFIF: <cfif
IsDefined("URL.productname") AND len(trim(url.productname))>, which will
achieve same result]

- after your query add another <cfif> statement that checks if the query
returned any results, something like this will do:
<cfif product.recordcount gt 0>
... output your results here...
<cfelse>
No Products Found
</cfif>

- change your existing <cfelse> to display instructions instead of "No
Product Found" text, because, as i mentioned before, this <cfelse> will
get executed on initial page load, when no product has been searched for
yet.

b) as for also searching for a price range:
- you will need to add another field to your form, maybe a select, which
allows your users to select a price range to search for, something like:
<select name="pricerange" size="1">
<option vaue="500" selected>0-500</option>
<option vaue="1000">501-1000</option>
<option value="1500">1001-1500</option>
....
</select>

then in your query you will need to add another criteria to your WHERE
clause:
where XXXXXX = <cfqueryparam cfsqltype="cf_sql_varchar"
value="#URL.productname#"> AND price_field BETWEEN <cfqueryparam
cfsqltype="cf_sql_integer" value="0"> AND <cfqueryparam
cfsqltype="cf_sql_integer" value="#URL.pricerange#">

this is in its very basic. depending on your aplication logic, you may
have pricerange selector to be optional, then you will need to enclose
the added criteria in your WHERE clause into its own <cfif> block
checking for existence of URL.pricerange, or you may have to re-work the
whole select field if you want to search for only a specific price range
and not 0-to-selected-value price as in my example above...

i will leave it up to you to figure the details out now, with the help
of the books i have mentioned, which i hope you have already ordered
from amazon.com...

hth


Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/