Multi-value variables in SQL Like clause?

[Thunderstone]

Hi, I have tried to modify the basic search script to let people
select which directories on our site to search
I have a checkbox
group that fills a variable called $section with things like /dir1/ ,
/dir2/, etc.

then I changed runquery so it looks like this:

select id, Url, Title, Body, length(Body) Size
from html
where Title\Meta\Body like $query
and Url like $section

BUT... if $section has more than one value, I get an empty result.
What am I doing wrong?

Multi-value Variables
A string variable with multiple values becomes a comma-separated list
when used as an argument, so that any of the values match with the
Metamorph like operator. For example, the following query:

<$states = "AL", "MS", "GA", "FL">
<SQL "select capital from State where state like $states">
$capital,
</SQL>
would match any of the given states, because the $states argument
becomes ``(AL,MS,GA,FL)''.


Wade Leftwich <wade@demographics.com>
American Demographics / Marketing Tools, a division of Cowles Business Media
tel 607-273-6343 fax 607-273-3196 http://www.demographics.com/

[Thunderstone]

You probably don't want to use the LIKE clause to match directories.
At the Thunderstone we do sections by swicthing the <db=> variable
and maintaining multiple databases.

The other way to do this is to use MATCHES instead of LIKE.

Try this: "select id, Url, Title, Body, length(Body) Size
from html
where Title\Meta\Body like $query
and and Url matches $section"

$section should look like this : "www.mysite.com/dir1/%"

The '%' in MATCHES behaves somewhat like an '*' in a shell.

Using MATCHES will not allow you to have multiple directories selected
within your interface though.

[Thunderstone]

Solved my own problem, I think.

when I changed the values of $section from /dir1/, /dir2/, /dir3/ to
dir1/, dir2/, dir3, multi-value subsstitution into a like clause
seemed to work fine.

The leading slash on the value is OK, if the variable has only a
single value. But if multiple values begin with slashes, you get no
results.

Does this have to do with peculiarities of Metamorph syntax? I
couldn't find anything about this in the online Texis docs.

-- Wade Leftwich

[Thunderstone]

Thanks for the quick reply. Right now I like the idea of users being
able to select multiple directories to search all at the same time.
Are there serious performance reasons for breaking things up into
multiple databases?

I did consider using the MATCHES operator. I suppose a person could
loop through the $section variable and string together something
like "where Url matches %dir1% or Url matches %dir2% or Url matches
%dir4%". Would there be major performance benefits to doing that? Is
there an index on Url that MATCHES would use?

-- Wade



Wade Leftwich <wade@demographics.com>
American Demographics / Marketing Tools, a division of Cowles Business Media
tel 607-273-6343 fax 607-273-3196 http://www.demographics.com/