I have been making a ad system that randomly selects a ad. Code below works.
<!--- Calls up record table --->
<CFQUERY DATASOURCE="#request.dsn#" NAME="GetADs">
SELECT top 1 *
FROM dbo.#request.stationName#_banner_top
WHERE (page_location = 'Front Page')
OR (page_location2 = 'Front Page')
OR (page_location3 = 'Front Page')
OR (page_location = 'Every Page')
OR (page_location2 = 'Every Page')
OR (page_location3 = 'Every Page')
ORDER BY newID()
</CFQUERY>
Now though I want to add the function of disabling the ad if the date_remove
passes. To disable I want the status column to be 1 instead of 0. Then I want
to randomly pick an ad from a list of the enabled ads only.
This is what I came up with. I call all the records then loop through them and
if they are old then change the status to disable(1).
<cfset todayDate = Now()>
<cfset todayDate2 = #dateformat(todayDate, 'mm/dd/yyyy')#>
<!---Calls all records so I can loop--->
<CFQUERY DATASOURCE="#request.dsn#" NAME="GetADsAll">
SELECT *
FROM dbo.#request.stationName#_banner_top
</CFQUERY>
<!---Loop though GetADsAll if Remove Date is Old then Disable--->
<cfloop query="GetADsAll">
<cfif #GetADsAll.date_remove# LT #todayDate2#>
<CFQUERY datasource="#request.dsn#" name="qDisableAd">
UPDATE dbo.#request.stationName#_banner_top
SET status = 1
WHERE ADID = #GetADsAll.ADID#
</CFQUERY>
</cfif>
</cfloop>
I know its all pieced together noob-like so if any help cleaning it up would be
greatly appreciated. So my problem from here is taking the two parts and
putting them together where the random query only shows the records with the
status of 0(enabled)
I tried putting this code with an AND status = 0 but that doesnt seem to work..
even the 1s show.
<!--- Calls up record table --->
<CFQUERY DATASOURCE="#request.dsn#" NAME="GetADs">
SELECT top 1 *
FROM dbo.#request.stationName#_banner_top
WHERE (page_location = 'Front Page')
OR (page_location2 = 'Front Page')
OR (page_location3 = 'Front Page')
OR (page_location = 'Every Page')
OR (page_location2 = 'Every Page')
OR (page_location3 = 'Every Page')
AND status = 0
ORDER BY newID()
</CFQUERY>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322143
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
