Wildcards and retrieving variables

[programmher]

I have a list of movies I want to retrieve via a CF form.

If the users select the letter "B" from the form, I want the information they view to be a list of every movie that has a title that begins with "b".

I am experiencing difficulty in getting my query to correctly return my data. If I hard code the letter within the query, it works. If I use the following code, it does not work:

<cfquery name=&quot;movies&quot; datasource=&quot;MovieList&quot; dbtype=&quot;ODBC&quot;>

SELECT *

FROM tbl_movies

WHERE tbl_movies.title LIKE '%#title#%'
ORDER BY tbl_movies.title

</cfquery



There is probably a simple solution I am overlooking. I would appreciate any assistance!

 

[DarkMan]

Your query will return every movie that has the letter &quot;b&quot; in it... Try it like:

<cfquery name=&quot;movies&quot; datasource=&quot;MovieList&quot; dbtype=&quot;ODBC&quot;>

SELECT *

FROM tbl_movies 

WHERE tbl_movies.title LIKE '#title#%'
ORDER BY tbl_movies.title

</cfquery>

removing the first percent sign in your LIKE clause. This should return the proper result set...

 

[programmher]

Darkman,

Welcome back! Glad you're back!!!

And thanks for the insight. One more question - I've included an alpha-listing that allows my users to select the alphabet of the movie they want to search. What am I missing with this one line of code? I have tried several variations of this code; but, to no avail...


<th><A HREF=&quot;searchmovies.cfm?Title=Z%&quot;>Z</A></th>

OR

<th><A HREF=&quot;searchmovies.cfm?TitleLIKE'Z%'&quot;>Z</A></th>

OR

<th><A HREF=&quot;searchmovies.cfm?Title='Z%'&quot;>Z</A></th>

 

[kingjes]

The way you are passing the z% is making the browser think its just a place holder kinda like a space.
Try:
<A HREF=&quot;searchmovies.cfm?Title=#URLEncodedFormat(Title)#&quot;>#Title#&quot;</A>

If you are running it from a dB query to get your title but it looks to me like you are trying to pass your WHERE Clause via a url param.
Try this:
1) Pass the url var you need i.e
<A HREF=&quot;searchmovies.cfm?Title=z&quot;>Z</A>
2)On your &quot;Action&quot; page have you Query use the URL var being passed into it i.e.
<CFQUERY NAME=&quot;qGetMovie_Info&quot; DATASOURCE=&quot;#APPLICATION.DSN#&quot;>
SELECT Movie_Title,
Director,
Ect
FROM Movie_dB
WHERE Title = '#URL.Title#'
</CFQUERY>

That should do it for you. Have you read Ben Fortas books yet? If not try 'em out they are great!
-KingJes

 

[DarkMan]

Programmher,

Thanks Glad to be back... (I've had 25 hours per day worth of work lately, and you know how that goes)

How about:

<cfoutput>
<th>
  <A HREF=&quot;searchmovies.cfm?Title=#urlencodedformat(&quot;Z%&quot;)#&quot;>Z</A>
</th>
</cfoutput>

Hope this helps..
DarkMan