Adding Selection Criteria

Of course, so far, our dynamic query is not very dynamic. What we need to do is vary the query based on the selection criteria entered by the user. For instance, the value selected in the CategoryList is available via the %request object, using the syntax

%request.Data("CategoryList",1)

If this value is not an asterisk, then it identifies the film category that the user want to search and we will add text such as

AND Category = xxx

to our SQL query.

SearchResults.csp

<script language="sql" name=FilmList>
    SELECT ID, Description, Length, Rating, Title, Category->CategoryName
    FROM Cinema.Film
    WHERE PlayingNow = 1
    #($SELECT(%request.Data("CategoryList",1) '= "*":
    " AND Category = " _ %request.Data("CategoryList",1),    1:""))#
    ORDER BY Title
</script>

The Caché $SELECT function, which has the following syntax,

$SELECT(logical expression:value, ...)

tests each logical expression and, when it finds one that's true, returns the associated value that follows the colon. To ensure that the function always returns something, most programmers add a final entry whose logical expression is “1”, the value for true, followed by the default value to return, in this case a zero-length string.