Solved

Using regular expressions in a link

Posted on 2004-08-31
21
202 Views
Last Modified: 2013-12-24
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
Comment
Question by:synergeticsoul
  • 10
  • 6
21 Comments
 
LVL 35

Expert Comment

by:mrichmon
Comment Utility
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
 

Author Comment

by:synergeticsoul
Comment Utility
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
 
LVL 35

Expert Comment

by:mrichmon
Comment Utility
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
 
LVL 1

Expert Comment

by:lxdev
Comment Utility
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
 

Author Comment

by:synergeticsoul
Comment Utility
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
 

Author Comment

by:synergeticsoul
Comment Utility
FYI, there are currently 3943 total records in the table.
0
 

Author Comment

by:synergeticsoul
Comment Utility
Could I possibly use a combination of "Like" and "In" in my query?
0
 
LVL 35

Expert Comment

by:mrichmon
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:synergeticsoul
Comment Utility
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
 

Author Comment

by:synergeticsoul
Comment Utility
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
 
LVL 35

Expert Comment

by:mrichmon
Comment Utility
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
 

Author Comment

by:synergeticsoul
Comment Utility
Thank You.  MRichmon.  I have been trying to test this and should have a response soon.

Thanks.
0
 

Author Comment

by:synergeticsoul
Comment Utility
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
 
LVL 35

Expert Comment

by:mrichmon
Comment Utility
what is the error?

Can you give a URL so we can see it?
0
 

Author Comment

by:synergeticsoul
Comment Utility
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
 
LVL 35

Accepted Solution

by:
mrichmon earned 500 total points
Comment Utility
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
 

Author Comment

by:synergeticsoul
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now