PHP SQL - How do I select data from MySQL using a + or

Author

How do I select data from MySQL using a + or - qualifier ?

Da Dimmi de wit

2007-01-11, 10:00 pm

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

Bob Stearns

2007-01-12, 4:01 am

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";

Paul Lautman

2007-01-12, 7:01 pm

Da Dimmi de wit

2007-01-12, 7:01 pm

On Fri, 12 Jan 2007 15:15:39 -0000, "Paul Lautman"
<paul.lautman@btinternet.com> wrote:

>Da Dimmi de wit wrote:
>
>Look at the FULLTEXT facilities
>

Ah yes, Boolean mode in FULL Text.

mysql> SELECT E.entryID,E.title,C.name
-> FROM blog_entries AS E, blog_categories AS C
-> WHERE E.categoryID=C.categoryID AND
-> MATCH (E.title,E.entry) AGAINST ('+vacation -washington'
IN BOOLEAN MODE) AND
-> E.categoryID=1;

http://www.onlamp.com/pub/a/onlamp/...6/fulltext.html

I believe this is what I was searching for.
Now I just need to confirm BOOLEAN works with words like 'the' and
'when' and doesn't drop them like MATCH does in FULLTEXT.

-Thanks.

Sponsored Links