Home > Archive > PHP DB > April 2004 > Re: [PHP-DB] Multiple work mySql database search.
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: [PHP-DB] Multiple work mySql database search.
|
|
| Viorel Dragomir 2004-04-21, 10:37 am |
| Split your phrase with split or explode functions.
Then implode the new array as something like 'i', 'want', 'to', 'search' from 'i want to search'.
Then create your whereClause for each of the result. If you want to find exactly the words than you might use the IN keyword.. like SELECT * FROM table WHERE word IN ('i', 'want', 'to', 'search').
http://php.net/explode
http://php.net/implode
http://php.net/split
----- Original Message -----
From: Martin E. Koss
To: php-db@lists.php.net
Sent: Wednesday, April 21, 2004 4:42 PM
Subject: [PHP-DB] Multiple work mySql database search.
Hi all you experts who have help me often in the past, may I now ask
what is probably so simple for someone doing PHP everyday...
I have a product database which can be searched for a keyword in up to 3
fields; using this select query:
$sql_select = "SELECT * from productlist WHERE (Name LIKE
'%$SearchText%') OR (Description LIKE '%$SearchText%') OR (Keywords LIKE
'%$SearchText%') ORDER BY Name";
What I really want to achieve is if someone enters more than one work in
the search for, how can I get the select query to search for one, the
other, or both?
I've thought of putting an option on the search for the user to select
'All Words' or 'Any Word' so that the select query could deal with the
search based on the full phrase they entered or any of the words.
My problem is how to build the query.
Any help would be massively appreciated.
Martin
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.661 / Virus Database: 424 - Release Date: 19/04/2004
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
| |
| Bruno Ferreira 2004-04-21, 10:38 am |
| Martin E. Koss wrote:
>I have a product database which can be searched for a keyword in up to 3
>fields; using this select query:
>
>$sql_select = "SELECT * from productlist WHERE (Name LIKE
>'%$SearchText%') OR (Description LIKE '%$SearchText%') OR (Keywords LIKE
>'%$SearchText%') ORDER BY Name";
>
>What I really want to achieve is if someone enters more than one work in
>the search for, how can I get the select query to search for one, the
>other, or both?
>
>
WHERE (field LIKE '%word1%' OR field LIKE '%word2%' [etc etc])
>
>I've thought of putting an option on the search for the user to select
>'All Words' or 'Any Word' so that the select query could deal with the
>search based on the full phrase they entered or any of the words.
>
>My problem is how to build the query.
>
>
>
Join them either with ANDs or ORs like this:
Searching for all words in a field: WHERE (field LIKE '%something%
AND field LIKE '%other_thing')
Searching for any of the words in a field: [read above in the
previous question]
Bruno Ferreira
---
[This E-mail scanned for viruses by Declude Virus]
| |
| Uzi Klein 2004-04-21, 2:38 pm |
| Try this :
$string = "Hello World";
$TableName = "Table";
$FieldName = "Field";
$Field = "CONCAT_WS('', ' ', TRIM($TableName.$FieldName), ' ')";
$Op = $Type=="All" ? "AND" : "OR";
$where = "WHERE ($Field LIKE '% ".(preg_replace("/[\s]+/", " %' $Op $Field
LIKE '% ", $string))." %')";
$sql = "SELECT * FROM Table ". $where;
----- Original Message -----
From: "Bruno Ferreira" <blueroom@digitalmente.net>
To: <php-db@lists.php.net>
Sent: Wednesday, April 21, 2004 3:46 PM
Subject: Re: [PHP-DB] Multiple work mySql database search.
Martin E. Koss wrote:
>I have a product database which can be searched for a keyword in up to 3
>fields; using this select query:
>
>$sql_select = "SELECT * from productlist WHERE (Name LIKE
>'%$SearchText%') OR (Description LIKE '%$SearchText%') OR (Keywords LIKE
>'%$SearchText%') ORDER BY Name";
>
>What I really want to achieve is if someone enters more than one work in
>the search for, how can I get the select query to search for one, the
>other, or both?
>
>
WHERE (field LIKE '%word1%' OR field LIKE '%word2%' [etc etc])
>
>I've thought of putting an option on the search for the user to select
>'All Words' or 'Any Word' so that the select query could deal with the
>search based on the full phrase they entered or any of the words.
>
>My problem is how to build the query.
>
>
>
Join them either with ANDs or ORs like this:
Searching for all words in a field: WHERE (field LIKE '%something%
AND field LIKE '%other_thing')
Searching for any of the words in a field: [read above in the
previous question]
Bruno Ferreira
---
[This E-mail scanned for viruses by Declude Virus]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
| |
| Uzi Klein 2004-04-21, 3:32 pm |
| Or even better :
<?
// ...
$string = "Hello World";
$Fields = Array("Table.Field1", "Table.Field2", "Table.Field3");
$Op = $Type=="All" ? "AND" : "OR";
for( $i=0; $i<count($Fields); $i++ )
{
$Field = $Fields[$i];
$Field = "CONCAT_WS('', ' ', TRIM($Field), ' ')";
$where .= "\n". ($i==0 ? "WHERE " : " $Op ");
$where .= "(";
$where .= "$Field LIKE '% ".(preg_replace("/[\s]+/", " %' $Op $Field LIKE
'% ", $string))." %'";
$where .= ")";
}
$sql = "SELECT ...";
$sql .= "\nFROM ...";
$sql .= $where;
// ...
?>
Try this :
$string = "Hello World";
$TableName = "Table";
$FieldName = "Field";
$Field = "CONCAT_WS('', ' ', TRIM($TableName.$FieldName), ' ')";
$Op = $Type=="All" ? "AND" : "OR";
$where = "WHERE ($Field LIKE '% ".(preg_replace("/[\s]+/", " %' $Op $Field
LIKE '% ", $string))." %')";
$sql = "SELECT * FROM Table ". $where;
----- Original Message -----
From: "Bruno Ferreira" <blueroom@digitalmente.net>
To: <php-db@lists.php.net>
Sent: Wednesday, April 21, 2004 3:46 PM
Subject: Re: [PHP-DB] Multiple work mySql database search.
Martin E. Koss wrote:
>I have a product database which can be searched for a keyword in up to 3
>fields; using this select query:
>
>$sql_select = "SELECT * from productlist WHERE (Name LIKE
>'%$SearchText%') OR (Description LIKE '%$SearchText%') OR (Keywords LIKE
>'%$SearchText%') ORDER BY Name";
>
>What I really want to achieve is if someone enters more than one work in
>the search for, how can I get the select query to search for one, the
>other, or both?
>
>
WHERE (field LIKE '%word1%' OR field LIKE '%word2%' [etc etc])
>
>I've thought of putting an option on the search for the user to select
>'All Words' or 'Any Word' so that the select query could deal with the
>search based on the full phrase they entered or any of the words.
>
>My problem is how to build the query.
>
>
>
Join them either with ANDs or ORs like this:
Searching for all words in a field: WHERE (field LIKE '%something%
AND field LIKE '%other_thing')
Searching for any of the words in a field: [read above in the
previous question]
Bruno Ferreira
---
[This E-mail scanned for viruses by Declude Virus]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
|
|
|
|
|