Selecting Multiple Rows from DB

[Ultrashock]

I'm building code where I need to be able to read all the rows in a single
column of data. My SQL code currently is simple and looks like this:

SELECT column
FROM table

and sure enough, if I do a cfdump on the query it returns all rows. Here's
the conflict, the result goes into an if statement like this (NOT a cfoutput or
cfloop):

<cfif query.column eq #variable#>

I need the #variable# to be able to match ANY of the returned rows in the
column, not just the first row (as it is doing now). I thought I'd be able to
achieve this using a WHERE clause in the SQL statement, but have had no luck
thus far. DOes anyone have an idea how I might achieve this?

I hope this isn't too vague. Please let me know if I need to elaborate.

Thanks for your time in advance.

[mxstu]

The best method depends on what you're trying to do. Do you need a list of the
actual query.Column values for usage somewhere else in your page, or are you
just testing for the existence of a value (#variable#) in the Table.Column?
What exactly are you trying to?

[Ultrashock]

I don't necessarily need a list. It's more along the lines of checking for the
existance of a value.

This is what it is in depth.

I've constructed a dynamic monthly calendar. I have topics written on a
specific date which I would like to highlight/link the date in the calendar to
that particular writing. Using the code above, I was able to get the calendar
to read the date column, but only the data from the first row. In other words,
the calendar is only linking the first date listed in the database. So maybe I
need a list of dates, but I think the database itself already provides this
information. I think it's more along the lines of checking for the existance
of a date.

Does that help? Thanks again for your time.

[mxstu]

Well, if it's a calendar app, then I'm assuming you're doing some looping, in
which case hitting the database numerous times might not be such a great idea.
You could use the ValueList() function to convert the query column results to a
list and then use ListFind() or ListFindNoCase() to determine if the #variable#
exists in the list.




<cfset myList = ValueList(myQuery.myColumnName)>
<cfif ListFindNoCase(myList, variable) GT 0>
Value found... do something
<cfelse>
Not found
</cfif>

[Ultrashock]

This didn't do it either, although it didn't error out. Rather than link the
first date though, it didn't link a date at all. I've attached the code if it
might help a bit more.

<cfinvoke component="#Application.DRIVER#" method="calendar"
returnvariable="calendar">
<cfparam name = "month" default = "#datepart('m', now())#">
<cfparam name = "year" default = "#datepart('yyyy', now())#">
<cfparam name = "currentday" default = "#datepart('d', now())#">
<cfparam name = "startmonth" default = "#datepart('m', now())#">
<cfparam name = "startyear" default = "#datepart('yyyy', now())#">
<cfset thismonthyear = createdate(year, month, '1')>
<cfset days = daysinmonth(thismonthyear)>
<cfset lastmonthyear = dateadd('m', -1, thismonthyear)>
<cfset lastmonth = datepart('m', lastmonthyear)>
<cfset lastyear = datepart('yyyy', lastmonthyear)>
<cfset nextmonthyear = dateadd('m', 1, thismonthyear)>
<cfset nextmonth = datepart('m', nextmonthyear)>
<cfset nextyear = datepart('yyyy', nextmonthyear)>
<cfset listlinks = ValueList(calendar.date)> <!--- Build a list for the linked
days --->
<table class="calFrame" cellspacing="0" cellpadding="0"> <!--- Month Framework
--->
<tr>
<td>
<div class="calMonth">
<cfoutput>
<a
href="index.cfm?month=#lastmonth#&year=#lastyear#" ><<</a>#monthasstring(month)#
#year#<a href = "index.cfm?month=#nextmonth#&year=#nextyear#"> >></a>
</cfoutput>
</div>
<table cellspacing="0" cellpadding="0"> <!--- Sunday - Saturday Header --->
<tr>
<cfloop from = "1" to = "7" index = "loopday">
<cfoutput>
<td class="calHead">#left(dayofweekasstring(loopday), 1)#</td>
</cfoutput>
</cfloop>
</tr>
</table>
<table class="calDayframe" cellspacing="0" cellpadding="0">
<cfset thisday = 0>
<cfloop condition = "thisday lte days">
<tr>
<cfloop from = "1" to = "7" index = "loopday">
<cfif thisday is 0>
<cfif dayofweek(thismonthyear) is loopday>
<cfset thisday = 1>
</cfif>
</cfif>
<cfif (thisday is not 0) and (thisday lte days)>
<cfoutput>
<cfset dayview = #dateformat(createdate(#year#, #month#, #thisday#),
"mm/dd/yyyy")#>
<cfif (#thisday# eq #currentday#) and (#month# eq #startmonth#) and (#year# eq
#startyear#)> <!--- Highlight Today --->
<td class="calToday">
#thisday#
</td>
<cfelse>
<!--- <cfif calendar.date eq #dayview#> ---> <!--- Linked Days to Entries --->
<cfif ListFindNoCase(listlinks, dayview) gt 0>
<td class="calPday">
<a href="">#thisday#</a>
</td>
<cfelse> <!--- Standard Days --->
<td class="calDay">
#thisday#
</td>
</cfif>
</cfif>
</cfoutput>
<cfset thisday = thisday + 1>
<cfelse>
<td></td>
</cfif>
</cfloop>
</tr>
</cfloop>
</table>
</td>
</tr>
</table>

[mxstu]

I didn't read through all of the code yet, but are you trying to search through
a list of dates (not strings)? If so, it could very well be that the format of
the date in the list is different than the variable date. Try outputting the
ValueList()... and the variable and see if it's a format problem.

[mxstu]

Yes, I just checked this by retrieving 2 date records from my database and
searching the list for the same date, created with the CreateDate() function.
It did not work, due to the different date formats. I would assume that
ListFind() just performs a straight string list search, and does not take into
account datatypes.




<!--- format of dates directly from cfquery db query --->
<cfset dateListFromQuery = "2000-10-01 00:00:00.0,2000-10-01 00:00:00.0">
<cfset variableDate = CreateDate(2000, 10, 2)>

<cfdump var="#dateListFromQuery#">
<cfdump var="#variableDate#">

<!--- Results in "Not Found" --->
<cfif ListFind(dateListFromQuery, variableDate) GT 0>
Found
<cfelse>
Not Found
</cfif>

[Ultrashock]

Yes it was a format problem... thanks for calling me on that. I was able to put a simple DateFormat on the dayview variable in the if statement and voila!

Thanks so much for your time! :)