Wow. Thank you so much for putting the time into this J.J. I really
appreciate it, and I'm sure others will as well.
It really makes a lot of sense sir. Thank you.
On Jun 14, 2007, at 2:00 PM, J.J. Merrick wrote:
> 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>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
ColdFusion MX7 and Flex 2
Build sales & marketing dashboard RIAâs for your business. Upgrade now
http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT
Archive:
http://www.houseoffusion.com/groups/CF-Newbie/message.cfm/messageid:2846
Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
