Brian,
After looking at your qeury there was a couple "better practice"... nice I
didn't say best since a lot of times that is a matter of opinion:
1. Yes it is better to specify all the columns coming back since it is
better performance because the database doesn't have to scan to see what
those are... and also if there is names the same in each one it will handle
conflicts.
2. Use of StructKeyExists is advised... but once again matter of opinion
3. When evaluating a Boolean it isn't neccessary to to a GT 0 or a EQ 1.
Essentially in CF if you just do arguments.boolean then it will convert it
to a boolean and then the evaluation will happen. SOOOOO cfif
arguments.stateid is the same as cfif arguments.stateid GT 0
And you can see how CFqueryparam works below... very simple and also does
some nice eval of the data coming in so to make sure it is ONLY a number.
etc.
<cfquery name="getBusinesses" datasource="#application.DSN#">
SELECT tblBiz.*, tblLocation.*
FROM tblBiz
Inner Join tblLocation ON tblBiz.Countyid = tblLocation.Countyid
WHERE
CatID = #Val(Arguments.CatID)#
<cfif StructKeyExists(arguments 'bizname')>
AND bizname LIKE <cfqueryparam value="%#arguments.bizname#%"
cfsqltype="cf_sql_varchar">
</cfif>
<cfif arguments.CountyID>
AND CountyID = <cfqueryparam value="#val(arguments.CountyID)#"
cfsqltype="cf_sql_integer">
</cfif>
<cfif StructKeyExists(arguments, 'City') >
AND BizCity LIKE <cfqueryparam value="%#arguments.City#%"
cfsqltype="cf_sql_varchar">
</cfif>
<cfif arguments.stateid>
AND stateid = <cfqueryparam value="#val(arguments.stateid)#"
cfsqltype="cf_sql_integer">
</cfif>
<cfif StructKeyExists(arguments, 'zip')>
AND bizzip LIKE <cfqueryparam value="%#arguments.zip#%"
cfsqltype="cf_sql_varchar">
</cfif>
<cfif arguments.bizid>
AND bizid = <cfqueryparam value="#arguments.bizid#"
cfsqltype="cf_sql_integer">
</cfif>
</cfquery>
happy cf'ing!
J.J.
On 6/13/07, Brian Petti <[EMAIL PROTECTED]> wrote:
>
> Very good. Thanks J.J. I will read up on those.. Is there a place
> where I can read about tips that CF newbies should follow?
>
> I noticed I left the DSN in my code snippet below.. I guess I will be
> changing that right away LOL.. Oh well.
>
> Anyway, I have never used cfqueryparam.. Anyway, if it would only
> take a second, could you convert my code below to show me what it
> would look like? If it will take a while don't bother, I will figure
> it out.. First things first, I have to go change the DSN now.. stupid
> me.
>
> Thanks again.
> -Bri
>
> P.S. Why is using the * not a great practice if I am using all the
> fields in each? Or is it because of the joins in case there was a
> field with the same name?
>
>
> On Jun 13, 2007, at 8:16 AM, J.J. Merrick wrote:
>
> > There are 3 things I am seeing with this query that would help you...
> >
> >
> > 1. Since you are doing a X.ID = Y.ID you are essentially doing a join,
> > though the more up-to-date way would be to specify INNER JOIN
> >
> > 2. The use of a * is not such a great practice. It is always
> > recommended to
> > layout everything you need and only what you need, even if you are
> > pulling
> > back all rows lay those out here.
> >
> > 3. You really should use a cfqueryparam, no and if's or buts about
> > it :-).
> > If you go read on CF-TALK about the SQL injections it will give you
> > the
> > reason why. Essentially this is a security measure that also has some
> > performance benefits as well.
> >
> >
> > Happy CF'ing!
> >
> > J.J. Merrick
> >
> >
> > On 6/13/07, Brian Petti <[EMAIL PROTECTED]> wrote:
> >>
> >> Hi All,
> >> Is the code snippet below the correct way to get information from 2
> >> tables? Should I specify a join in the SQL? This seems to work with
> >> out specifying it however.
> >>
> >> Thanks for any suggestions.
> >>
> >> -Brian
> >>
> >>
> >> <cfcomponent>
> >> <cffunction name="ListBusinesses" returntype="query">
> >> <cfargument name="CatID" required="false" type="numeric"
> >> default="0">
> >> <cfargument name="bizname" required="false" type="string">
> >> <cfargument name="CountyID" required="false" type="numeric">
> >> <cfargument name="City" required="false" type="string">
> >> <cfargument name="stateid" required="false" type="numeric">
> >> <cfargument name="zip" required="false" type="string">
> >> <cfargument name="bizid" required="false" type="numeric">
> >>
> >> <cfquery name="getBusinesses"
> >> datasource="kenhugh_america411">
> >> SELECT
> >> *
> >> FROM
> >> tblBiz,tblLocation
> >> WHERE
> >> tblBiz.Countyid =
> >> tblLocation.Countyid
> >> AND
> >> CatID = #Val(Arguments.CatID)#
> >> <cfif isdefined('arguments.bizname')>
> >> AND bizname LIKE '%
> >> #arguments.bizname#%'
> >> </cfif>
> >> <cfif arguments.CountyID GT 0 >
> >> AND CountyID = #val
> >> (arguments.CountyID)#
> >> </cfif>
> >> <cfif isdefined('arguments.City') >
> >> AND BizCity LIKE '%
> >> #arguments.City#%'
> >> </cfif>
> >> <cfif arguments.stateid GT 0 >
> >> AND stateid = #val
> >> (arguments.stateid)#
> >> </cfif>
> >> <cfif isdefined('arguments.zip')>
> >> AND bizzip LIKE '%#arguments.zip#%'
> >> </cfif>
> >> <cfif arguments.bizid GT 0 >
> >> AND bizid = '#arguments.bizid#'
> >> </cfif>
> >> </cfquery>
> >>
> >> <cfreturn getBusinesses>
> >> </cffunction>
> >> </cfcomponent>
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 &
Flex 2
Free Trial
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU
Archive:
http://www.houseoffusion.com/groups/CF-Newbie/message.cfm/messageid:2845
Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
