match against

but only if the against string starts with the string to match

[lethal0r]

I need to use match against in the following circumstances:

MATCH ('widget') AGAINST ('widget text is here')

this should succeed as the string in the against function starts with widget.

MATCH ('widget') AGAINST ('text for widget is here')

this should fail as the string in the against function does not start with widget. right now they both return results.

any ideas anyone?

[Demaestro]

Sounds like you need something like a "startswith" function.

Search your db user guide for a "startswith" or "starts with" function.

[rocknbil]

Well, I'll use mySQL as an example (state your DB type as inferred . . . )

Front end form:

<input type="radio" name="match_type" id="match_type_any" value="0" checked> <label for="match_type_any">Anywhere</label>
<input type="radio" name="match_type" id="match_type_exact" value="1"> <label for="match_type_exact">Exact</label>
<input type="radio" name="match_type" id="match_type_start" value="2"> <label for="match_type_start">Starts With</label>
<input type="radio" name="match_type" id="match_type_end" value="3"> <label for="match_type_end">Ends With</label>

Then in your search script, set the "comparator" based on what's submitted (perl example shown, convert as required . . . of course filter data before you get to this point.)

$search_type=$data{'match_type'};

if ($search_type == 1) {
$comparator = '=';
$value = $data{'search_term'};
}
elsif ($search_type == 2) {
$comparator = ' like ';
$value = qq(%$data{'search_term'});
}
if ($search_type == 3) {
$comparator = ' like ';
$value = qq($data{'search_term'}%);
}
## Zero or null
else {
$comparator = ' like ';
$value = qq(%$data{'search_term'}%);
}

$select = qq(select * from table where field $comparator '$value';);

Which gives you one of four:
1, exact
select * from table where field = 'search term';
2, starts with
select * from table where field like '%search term';
3, ends with
select * from table where field like 'search term%';
0 or null, anywhere
select * from table where field like '%search term%';

I used a specific field, you could use match against or any variation of allowing user-selected fields.

[lethal0r]

many thanks.

mysql! - hours spent desperately trying to sort it then a rushed posting to WebmasterWorld lol.

[rocknbil]

Does this mean you solved it? For later visitors the solution would be helpful . . . and which database type you're working with . . .

Noted an error in my sample, this

if ($search_type == 3) {

S/B

elsif ($search_type == 3) {

[lethal0r]

yeah I got there in the end.

its a MyISAM db.

the "startswith" comment made me completely re-think and I ended up with:

SELECT widget FROM widgets WHERE LOCATE(widget, 'text to match') = 1

for some reason I was thinking it was going to be a really complex query.