Help me clean my ColdFusion Script.

[jtracking]

Hi everyone. I posted a problem here: Possible MySQL Doozie...

and was successful at writing the script and avoiding errors.

I want to submit my code below though for any ColdFusion Master's out there that could help me clean up my code a bit here and there if at all possible. Plus, there is one more tiny problem and that is if the contact_id that is fetched from the users2 query ends up being a "" or null I skip the interation and the referer data doesn't get displayed for that sale. Well even though the user didn't sign up for an account there still was a referer and a sale_id...

either way, that's not the point - I'll figure that part out on my own...

here is the code.

P.S. the form the user would use to submit a product number to search the referer for is at the bottom of the code and the action for that form is "go"

-------

<!--- set the default value of action to nothing //--->
<cfparam name="action" default="">
<cfoutput>

<!--- If the form variable action is set to go, run the script to find the referers based on sales of a particular product number //--->
<cfif action is "go">
<h3>Referers List for Product ###pnumber#</h3>

<!--- initialize the array needed to store the sales_id of each sale related to the specified/searched for product //--->
<cfset getlist = ArrayNew(1)>

<!--- set array position variable //--->
<cfset x = 1>

<!--- get the product number and sales id from the saledetail table where the product number was searched for by the user //--->
<cfquery name="sales" datasource="#request.dsn#">
select product_no,sales_id from salesdetail where product_no = #pnumber#;
</cfquery>

<table border="0" width="160px" cellpadding="3" cellspacing="0" class="mytable" align="center">
<tr>
<th align="center" colspan="2">Product ## #sales.product_no#</th>
</tr>
<tr>
<td width="100" align="center"><a href="javascript:NewWin('../../productimages/#trim(sales.product_no)#_largest.jpg');">
<img style="border:solid 1px ##FF0000;" align="center" src="../../productimages/#trim(sales.product_no)#_tbnl.gif" width="50" height="60" border="0" alt="" align="left"></a></td>
<td width="100" align="center"><a href="javascript:history.back(1);"><<< Back&nbsp;&nbsp;</a></td>
</tr>
</table><br>

<!--- Allow the user to search again on the same page as the search results //--->
<b>Search Again</b>
<form action="index.cfm?fuseaction=sales_by_product_num& action=go" method="post">
<input type="text" name="pnumber">&nbsp;<input type="submit" name="Submit" value="Search Referers By Product ##">
</form>
<br>

<!--- display HTML output //--->
<table border="1" cellpadding="3" cellspacing="0" class="mytable" align="center" width="70%">
<tr>
<th align="center">Referer Ad</th>
<th align="center">Contact ID</th>
<th align="left">&nbsp;&nbsp;Referers for the sales on product ## #pnumber#</th>
</tr>

<!--- loop through the sales query results //--->
<cfloop query="sales">

<!--- apply the sales_id to the getlist array at position x //--->
<cfset getlist[x] = #sales.sales_id#>

<!--- get the contact_id from the sales table based on the sales_id value in the getlist array //--->
<cfquery name="users2" datasource="#request.dsn#">
select contact_id from sales where sales.sales_id = #getlist[x]#;
</cfquery>

<!--- increment array position variable //--->
<cfset x = x + 1>

<!--- set the value of users2.contact_id to a simple variable //--->
<cfset cnumber = #users2.contact_id#>

<!--- not all sales have user/contact_id, need to make sure select statement below doesn't run and throw an sql error if variable is empty //--->
<cfif #cnumber# neq "">

<!--- get ad, contact_id, referer info. based on contact_id which was based on the sale which was based on the product number //--->
<cfquery name="users3" datasource="#request.dsn#">
select referer, ad, contact_id from users where contact_id = #users2.contact_id#;
</cfquery>

<!--- output the information in HTML below //--->
<cfif #users3.referer# neq "unknown" and #users3.referer# neq " " and #users3.referer# neq "" and #users3.referer# neq "http://127.0.0.1/">
<tr>
<td>&nbsp;<b>#users3.ad#</b>&nbsp;</a></td>
<td>&nbsp;<b><a href="javascript:NewWin('../marketing/index.cfm? fuseaction=contact&Contact_ID=#users3.contact_id#&menu=no');">#users3.contact_id#</a></b>&nbsp;</a></td>
<td><a target="_blank" href="#users3.referer#">#left(users3.referer, 612)#.......&nbsp;&nbsp;</a></td>
</tr>
</cfif>
</cfif>
</cfloop>
</table><br><br>

<!--- If action is not GO then simply load the main search page //--->
<cfelse>
<h3>Referers by Product Number</h3>
Simply enter a product number to see a list of referers related to that product's sales.<br>
Please be patient as this could take a few minutes to run.<br><br>
<form action="index.cfm?fuseaction=sales_by_product_num& action=go" method="post">
<input type="text" name="pnumber">&nbsp;<input type="submit" name="Submit" value="Search Referers By Product ##">
</form>
</cfif>
</cfoutput>

-------

Thanks

[jtracking]

Geze that's messy looking...sorry everyone...

[imvain2]

I tried to look over your other thread but was having trouble following, so this is what I came up with for a new single query.

select salesdetail.product_no, salesdetail.sales_id, sales.contact_id, users.referer,users.ad
from salesdetail,sales, users
where salesdetail.product_no = #pnumber#
and sales.sales_id = salesdetail.sales_id
and users.contact_id = sales.contact_id

I use this method all the time, I'm sure someone can say why the joins are better, but I find this to be easy to understand and implement.

In regards to the: <!--- not all sales have user/contact_id, need to make sure select statement below doesn't run and throw an sql error if variable is empty //---
It maybe just me but for my integer/int fields I always try to have a default value, -1 or 0. Yes people can argue that it adds 1 more character per record to the overall database size but its easier to run queries when I have a database that I control the input of integer fields and the default is numeric. And if you wanted to fix this, its an easy fix, just login to your mySQL manager, set the default value for that field and run a simple query: update tablename set fieldname = 0 where fieldname = ''

If you do want/need to keep the different queries, I do see one thing you can clean up.

FROM:

<cfquery name="users3" datasource="#request.dsn#">
select referer, ad, contact_id from users where contact_id = #users2.contact_id#;
</cfquery>

TO:

<cfquery name="users3" datasource="#request.dsn#">
select referer, ad, contact_id
from users
where contact_id = #users2.contact_id#
and length(referer) > 1
and referer <> ' unknown'
and referer not like '%127.1.1.1%' 
</cfquery>

This way you don't need the database to pull back data if you aren't going to use it.