For those that are interested, this thread moved offline. I just wanted to
post my follow-up for anyone that was paying attention to this thread...
[excerpted from my email reply]
OK, so let me explain what I meant in my response. I think part of the
confusion stems from my having typed the response on a laptop keyboard, on a
moving train, so I didn't do as good a job typing as I should have 8^).
I'll go through each step using the sample data you had provided in an
earlier email:
> > ----------------------
> > ID* - Active - Approved
> > ----------------------
> > 12 - No - Yes
> > 23 - No - No
> > 24 - No - Yes
> > 29 - Yes - Yes
> > 32 - Yes - Yes
> > 33 - No - Yes
> > 40 - No - No
The basic steps, as per my last email, are:
1) Query DB for currently active items, sort by ID.
2) Discard the 1st record and store the ID of the 2nd.
3) Update DB to make currently active item inactive.
4) Query DB for all items greater than stored value that are approved, sort
by ID.
5) a) If RecordCount >= 2, grab 1st 2 values and ignore others.
b) Else, query DB for all approved items, sort by ID. Grab 1st 1 or 2
depending on how many you got from the previous query.
6) Update DB to set these 2 values to active.
Let me show you the code:
<!--- Step 1. Query DB for currently active items. Sort by ID --->
<!--- This should return only 2 records: 29 & 32 --->
<CFQUERY NAME="CurrentActive" DATASOURCE="#application.DSN#">
SELECT PaidAdID
FROM PaidAdverts
WHERE NowShowing IS Yes
ORDER by PaidAdID
</CFQUERY>
<!--- Step 2. Discard the 1st record and store the ID of the 2nd. --->
<!--- We only need the 2nd ID so we can use it to determine the --->
<!--- next 2 records to be activated. --->
<CFSET lastActiveID = CurrentActive.PaidAdID[2]>
<!--- Step 3. Update DB to make currently active item inactive. --->
<!--- Deactivate old items to make room for the new ones. --->
<CFQUERY NAME="DeactivateAdverts" DATASOURCE="#application.DSN#">
UPDATE PaidAdverts
SET NowShowing = 'No'
WHERE NowShowing IS Yes
</CFQUERY>
<!--- Step 4. Query DB for all items greater than lastActiveID that --->
<!--- are approved, sort by ID. This gets us all of the records with --->
<!--- IDs greater than lastActiveID that are eligible for activation. --->
<CFQUERY NAME="GetNextAdverts" DATASOURCE="#application.DSN#">
SELECT PaidAdID
FROM PaidAdverts
WHERE Approved IS Yes
AND PaidAdID > #lastActiveID#
ORDER by PaidAdID
</CFQUERY>
<!--- Step 5. Determine next 2 adverts --->
<CFSET nextAdvertIDList = "">
<!--- Step 5a. If RecordCount >= 2, grab 1st 2 values and ignore
others. --->
<CFIF GetNextAdverts.RecordCount GTE 2>
<CFSET nextAdvertIDList = ListAppend(nextAdvertIDList,
GetNextAdverts.PaidAdID[1])>
<CFSET nextAdvertIDList = ListAppend(nextAdvertIDList,
GetNextAdverts.PaidAdID[2])>
<!--- Step 5b. Else, query DB for all approved items, sort by ID. Grab --->
<!--- 1st 1 or 2 depending on how many you got from the previous query. --->
<CFELSE>
<!--- Store any valid IDs greater than lastActiveID. There will only be 0
or 1. --->
<CFLOOP QUERY="GetNextAdverts">
<CFSET nextAdvertIDList = ListAppend(nextAdvertIDList,
GetNextAdverts.PaidAdID)>
</CFLOOP>
<!--- Query DB for all valid adverts, from beginning of list --->
<CFQUERY NAME="GetAllApprovedAdverts" DATASOURCE="#application.DSN#">
SELECT PaidAdID
FROM PaidAdverts
WHERE Approved IS Yes
ORDER by PaidAdID
</CFQUERY>
<!--- Get enough adverts from query to fill the list --->
<CFLOOP QUERY="GetAllApprovedAdverts">
<CFIF ListLen(nextAdvertIDList) LT 2>
<CFSET nextAdvertIDList = ListAppend(nextAdvertIDList,
GetNextAdverts.PaidAdID)>
</CFIF>
</CFLOOP>
</CFIF>
<!--- Step 6. Update DB to set these 2 values to active. --->
<CFQUERY NAME="ActivateAdverts" DATASOURCE="#application.DSN#">
UPDATE PaidAdverts
SET NowShowing = 'Yes'
WHERE PaidAdIDs IN (#nextAdvertIDList#)
</CFQUERY>
<!--- *********** --->
<!--- END OF CODE --->
<!--- *********** --->
Note that all of the above code should be wrapped in a CFTRANSACTION. Also,
in step 5b, you may need to modify the following line:
<CFIF ListLen(nextAdvertIDList) LT 2>
to check that the current PaidAdID is not already in the list. This would
be the case if there is only 1 currently approved ad in the DB. Than, you
would get the same ID in the list twice. This may or not be a problem
depending on your business logic.
Also, I just wrote this off the cuff without testing it. It may have some
bugs and it could almost certainly be optimized. But this should at least
get you going.
Good luck.
--
Mosh Teitelbaum
evoch, LLC
Tel: (301) 625-9191
Fax: (301) 933-3651
Email: [EMAIL PROTECTED]
WWW: http://www.evoch.com/
> -----Original Message-----
> From: Mosh Teitelbaum [mailto:[EMAIL PROTECTED]]
> Sent: Friday, September 20, 2002 6:20 PM
> To: CF-Talk
> Subject: RE: Finding next 2 items in query
>
>
> You're explaining yourself just fine. No worries.
>
> Basically, you're looking at some conditional logic a few queries.
> Something like this:
>
> 1) Query DB for currently active items, sort by ID.
> 2) Discard the 1st record and store the ID of the 2nd.
> 3) Update DB to make currently active item inactive.
> 4) Query DB for all items greater than stored value that are
> approved, sort
> by ID.
> 5) a) If RecordCount >= 2, grab 1st 2 values and ignore others.
> b) Else, query DB for all approved items, sort by ID. Grab 1st 1 or 2
> depending on how many you got from the previous query.
> 6) Update DB to set these 2 values to active.
>
> Note that in the 5b, you're getting all approved items, not just
> those less
> than the last 2 approved items. This is just in case you only
> have to items
> that are currently approved.
>
> --
> Mosh Teitelbaum
> evoch, LLC
> Tel: (301) 625-9191
> Fax: (301) 933-3651
> Email: [EMAIL PROTECTED]
> WWW: http://www.evoch.com/
>
>
> > -----Original Message-----
> > From: W Luke [mailto:[EMAIL PROTECTED]]
> > Sent: Friday, September 20, 2002 12:08 PM
> > To: CF-Talk
> > Subject: Re: Finding next 2 items in query
> >
> >
> > > ----------------------
> > > ID* - Active - Approved
> > > ----------------------
> > > 12 - No - Yes
> > > 23 - No - No
> > > 24 - No - Yes
> > > 29 - Yes - Yes
> > > 32 - Yes - Yes
> > > 33 - No - Yes
> > > 40 - No - No
> >
> > > I think you'll have to fill us in on why 33, and 12 "meet the
> > > criteria"
> >
> > Because they're the "next" records in the table WHERE Approved = Yes AND
> > active = No. 40 has to be ignored because Approved = No.
> >
> > I know how to find all records that are "approved," or "active," or
> > whatever - but I don't see how I can get the "next 2." A friend
> > suggested doing something like WHERE ID > #previousID# - only problem
> > with this is if the CURRENT ID was 40 (as per the table above), it
> > wouldn't pickup on 12 which is the logical next record.
> >
> > Sorry, I'm not explaining myself properly here at all - if anyone has
> > any other ideas they'd be much appreciated
> >
> > Will
> >
> >
>
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
