I have a calendar application with various showTo 'views'. There are 2 problems: the datatypes for the primary to foreign key in the tables used are different. The calendar table has the field- showId, but it is a varchar (and the table already has a lot of data), while the corresponding foreign key in the showTo table, for showId is an integer. The application was originally built to insert "x" number of showTo (showId) variables in the calendar table for each entry. However, as the showTos grow, i don't want to continue copying the relevant pages for the application and inserting in the appropriate new folder. What are the best solutions? I ultimately just want this one application and perhaps using links and regular expressions, display the appropriate data. I already have well over 100 showIDs and I want to get control of this before I considered the following:
1) Using like within my query to obtain the one value for the particular view:
<cfquery name="getweekcal" datasource="#application.dsn#">
SELECT * FROM calendar
WHERE calendar.calendardate between #begofweek# and #endofweek#
AND ShowId like '%10%'
ORDER BY calendar.calendardate
This works, but I will still need to copy the pages of the application into various folders for all new views and change the values.
2) Link Option with a regular expression
This seems like a good choice, but I don't know how I would construct the regular expression to find all values where showID LIKE ShowId like '%10%'.
I thought I could do: <a href="test_calendar.cfm?showId*10" class="main">View Midwest Events</a>, but this does not work either.
Please advise. What are my options so that I can gain control of this application?