Posted by Bob Stearns on 01/12/07 05:22

Da Dimmi de wit wrote:
> I have a database table that I need to query specific keywords from.
> I would like to do so using a + or -.
>
> Example:
>
> I have a table of :
> Dates Titles
> 1/1/07 The Bird Has Crashed Hard
> 1/5/07 Zalman IS Hot but Liquid N is Colder
>
> and I want to search on keywords in the Title.
>
> I understand I can use a:
> select * from Titles where Titles like '%Bird%'
>
> but would like to use a + / - . Is this possible in a SQL query, or is
> PHP required (using PHP code is not an issue if required)?
>
> Also any info on the speed of such a keyword query for 200K+ records
> or any special table attributes that would be required.
>
> TIA
> -DDW

Something like like the following should work, but the performance will
be horrible. If you do not need the left-hand wildcards, then processing
the table Titles to create another table Keywords ( keyword varchar(25),
title_id integer, primary key(keyword, title_id)) and changing the
select from a string of ANDs to a string of INTERSECTS would give a much
better performance profile.

$t = trim($_REQUEST[$query])." ";
while($t!="") {
%operator = substr($t,0,1);
$t = trim(substr($t,1));
if($operator=="+") $where .= $dlm . "Titles LIKE ";
else $where .= $dlm . "Titles NOT LIKE ";
$i = strpos($t, " ");
$where .= "'" . substr($t, 0, $i-1) . "'";
$dlm = "AND ";
$t = trim(substr($t,$i));
}
$query = "SELECT * FROM Titles WHERE $where";

[Back to original message]