• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

Using regular expressions in a link

Hi.

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
</cfquery>
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?

Thank you.
0
synergeticsoul
Asked:
synergeticsoul
  • 10
  • 6
1 Solution
 
mrichmonCommented:
Why can't you do :

<a href="test_calendar.cfm?showId=10" class="main">View Midwest Events</a>

Then on the page :
<cfquery name="getweekcal" datasource="#application.dsn#">
SELECT * FROM calendar
WHERE calendar.calendardate between #begofweek# and #endofweek#
AND ShowId like '%#URL.SHowID#%'
ORDER BY calendar.calendardate
</cfquery>
0
 
synergeticsoulAuthor Commented:
The problem is the 2 different datatypes.  The application is old.  When I tried the above, I get:

"Syntax error converting the varchar value '1,2,6,10,40' to a column of data type int."

I am trying to do this from a link.   Each record has various showTos, for example, one record may have, 1, 5, 10, 30 as possible showTos.  As the application grows, I am trying to fix the problem before it gets out of hand.  I am not sure if I should be doing replacements or what..

please advise... thank you...
0
 
mrichmonCommented:
Oh then you can use IN
<cfquery name="getweekcal" datasource="#application.dsn#">
SELECT * FROM calendar
WHERE calendar.calendardate between #begofweek# and #endofweek#
AND ShowId IN (#URL.ShowID#)
ORDER BY calendar.calendardate
</cfquery
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
lxdevCommented:
If you need to have single quotes around each value, then you can use the ListQualify() function:

<cfquery name="getweekcal" datasource="#application.dsn#">
SELECT * FROM calendar
WHERE calendar.calendardate between #begofweek# and #endofweek#
AND ShowId IN (#ListQualify(URL.ShowID, "'")#)
ORDER BY calendar.calendardate
</cfquery>

Note that the function's second argument is a single quote surrounded by double quotes.
0
 
synergeticsoulAuthor Commented:
what is the ListQualify() function?

Neither solution works because of the data type difference.  i still get : "Syntax error converting the varchar value '1,2,6,10,40' to a column of data type int."

Or I get the calendar minus the event data.  I also tried casting, but still get the same errors.

Are there other options to explore that can be done immediately?  I have a lot of data in the table now.  Each record has various showTo variables as varchar, from the showTo table (int).

??Help.  

0
 
synergeticsoulAuthor Commented:
FYI, there are currently 3943 total records in the table.
0
 
synergeticsoulAuthor Commented:
Could I possibly use a combination of "Like" and "In" in my query?
0
 
mrichmonCommented:
ListQualify will not work since the data type is int.  It is for strings.

The problem is that you are sending it in in quotes which makes it see it as a varchar instead of a string of ints.

Try this :

<cfset tempqry = "SELECT * FROM calendar WHERE calendar.calendardate between #begofweek# and #endofweek# AND ShowId IN (#URL.ShowID#) ORDER BY calendar.calendardate">


<cfoutput>#tempqry#</cfoutput>
<cfabort>

And see exactly what gets printed to the screen.

It should look like this (I filled in dates since I don't know what those will be):

SELECT * FROM calendar WHERE calendar.calendardate between 3/4/2004 and 3/11/2004 AND ShowId IN (1,2,6,10,40) ORDER BY calendar.calendardate
0
 
synergeticsoulAuthor Commented:
I am still getting the same error-

"Syntax error converting the varchar value '1,2,6,10,40' to a column of data type int."

Please advise.  What are my options besides rebuilding the table and datatypes which will be quite time-consuming?
0
 
synergeticsoulAuthor Commented:
I also tried the following:

<cfquery name="getweekcal" datasource="#application.dsn#">
SELECT * FROM calendar
WHERE ShowId IN
<cfif isDefined("Form.ShowID")>
      '#form.ShowID#'
      <cfelse>'#URL.ShowID#'</cfif>
AND ShowId like
<cfif isDefined("Form.ShowID")>
      '%#Form.ShowID#%'
      <cfelse>'%#URL.ShowID#%'</cfif>
AND CAST(showId AS int)
Order BY calendarDate ASC
</cfquery>

I get the following error: Incorrect syntax near '20'.
0
 
mrichmonCommented:
I do not think it should be deleted as the IN option will work.

I am sorry I did not respond after the post on the 8th, but I was out of town for a week.

 synergeticsoul  - the reason you got an error with incorrect syntax is that you left out the parenthesis.  Look again at my example at what the SQL should look like.  Then to test what yours looks like you do this :

<cfoutput>
SELECT * FROM calendar
WHERE ShowId IN
<cfif isDefined("Form.ShowID")>
     '#form.ShowID#'
     <cfelse>'#URL.ShowID#'</cfif>
AND ShowId like
<cfif isDefined("Form.ShowID")>
     '%#Form.ShowID#%'
     <cfelse>'%#URL.ShowID#%'</cfif>
AND CAST(showId AS int)
Order BY calendarDate ASC
</cfoutput>

You will see that you do not have the ( ) around the IN clause and that is causing your syntax problem.
0
 
synergeticsoulAuthor Commented:
Thank You.  MRichmon.  I have been trying to test this and should have a response soon.

Thanks.
0
 
synergeticsoulAuthor Commented:
I am still getting an error.  This time the error is not a list, but invalid.  Not sure what I am doing wrong.  I am passing from a link.  So on one page, the link may be: calendar.cfm?showid=1, but upon clicking on this, there is an error.
0
 
mrichmonCommented:
what is the error?

Can you give a URL so we can see it?
0
 
synergeticsoulAuthor Commented:
Here is the actual error:

<ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near '1'.



The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (39:1) to (39:58).

>
=================================
This is the code for the test link:

<a href="test_calendar.cfm?showId=1" class="main">Sample Calendar View By Link Only</a>
====================================
This is the actual code for the page now:

<cfquery name="getweekcal" datasource="#application.dsn#">
SELECT * FROM calendar
WHERE ShowId IN
<cfif isDefined("Form.ShowID")>
     '#form.ShowID#'
     <cfelse>'#URL.ShowID#'</cfif>
AND ShowId like
<cfif isDefined("Form.ShowID")>
     '%#Form.ShowID#%'
     <cfelse>'%#URL.ShowID#%'</cfif>
AND CAST(ShowID AS int)
</cfquery>
0
 
mrichmonCommented:
The problem is that you still do not have the correct syntax.

Again I recommend that you place the following on the page when you get problems like this so you can see exactly the query you are creating :

<cfoutput>
SELECT * FROM calendar
WHERE ShowId IN
<cfif isDefined("Form.ShowID")>
     '#form.ShowID#'
     <cfelse>'#URL.ShowID#'</cfif>
AND ShowId like
<cfif isDefined("Form.ShowID")>
     '%#Form.ShowID#%'
     <cfelse>'%#URL.ShowID#%'</cfif>
AND CAST(ShowID AS int)
</cfouput>
<cfabort>

You would then notice that the query created is :

SELECT * FROM calendar WHERE ShowId IN '1' AND ShowId like'%1%' AND CAST(ShowID AS int)

This is not valid syntax.

First of all if the column showID is an int then you should NOT have the single quotes.

Second, please carefully review all of the previous comments and you will see that you NEED parenthesis around andy IN clause

Also I am not sure what exactly you are trying to select.  I am guessing you want any ID with a 1 anywhere in the ID?

But re-reading the original post you say that showID in the calendar table is a varchar so the following should give you all those results:

SELECT * FROM calendar WHERE ShowID LIKE '%1%'

Then you don't need the IN or the CAST portions (I am not really sure why the CAST portion is there anyways).  If you then need to connect to a table that has showID as an int then please explain what you need and we can help from there.
0
 
synergeticsoulAuthor Commented:
Sorry for the late response, I have had no luck with this and have been trying to figure out what's wrong.  MRichmon, thanks for your help.  Points go to MRICHMON.  
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 10
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now