spliting keywords by GET method and SQL query

I need to split the keyword on search input and generate an sql query,
for ex:   the input value is:   europe+america,asia
so I want to generate an sql like this:
SELECT * FROM mytable WHERE message LIKE %europe% AND message LIKE %america%
OR message LIKE %asia%

The + (plus) means AND and , (comma) means OR. Also I need to control input
variables to prevent error on sql query forexample an input value like:
,,,europe+america,+asia+    should not generate an error.

The method doesn't matter, use whatever you like.

Did this quick just for the fun, it seems to work.  Set $search to
whatever you'd like to try or to your GET or POST argument.

" ,,, eurpoe" won't cause a problem but " + + + europe" will generate
some odd SQL (although it will work as expected - perhaps slowly).

There are better ways to solve the problem, this will not scale to a
huge database because searching a table with a WHERE foo LIKE "%bar%"
means the database cannot use any indexes and will have to do a table
scan (read every record).  If your serious about searching a large
number of records I suggest indexing the words and searching the index;
there are several other advantages to that method too for example you
can make a search for "europe" find "europe", "european", "e.u." and
"europian" (misspelled) without any wildcards.

That's more work up front (indexing the records) but the search queries
are simple.

Good Luck.

<?

$search = "europe, america + asia, india, , china";

$myQuery = "SELECT * FROM mytable WHERE 1 = 1 ";

$orWords = explode(",",$search);

for ($i=0; $i < count($orWords); $i++)
{
if ( substr_count($orWords[$i], "+") > 0)
{
$andWords = explode("+",$orWords[$i]);
$myQuery = $myQuery . " OR ( ";
for ($j=0; $j < count($andWords); $j++)
{
if ($j == 0)
{
$myQuery = $myQuery . ' message like "%' . trim($andWords[$j]) .
'%" ';
} else {
$myQuery = $myQuery . ' AND message like "%' . trim($andWords[$j])
. '%" ';
}
}
$myQuery = $myQuery . " ) ";
} else {
if (trim($orWords[$i]) > "")
{
$myQuery = $myQuery . ' OR message like "%' . trim($orWords[$i]) .
'%" ';
}
}
}

echo "<BR><BR> $myQuery\n";

?>


On Sep 30, 2004, at 5:03 AM, php-db-digest-help@lists.php.net wrote:

>
> From: "Murat BIYIKLI" <admin@muratbiyikli.name.tr>
> Date: September 29, 2004 8:47:28 AM PDT
> To: php-db@lists.php.net
> Subject: spliting keywords by GET method and SQL query
>
>
> I need to split the keyword on search input and generate an sql query,
> for ex:   the input value is:   europe+america,asia
> so I want to generate an sql like this:
> SELECT * FROM mytable WHERE message LIKE %europe% AND message LIKE
> %america%
> OR message LIKE %asia%
>
> The + (plus) means AND and , (comma) means OR. Also I need to control
> input
> variables to prevent error on sql query forexample an input value like:
> ,,,europe+america,+asia+    should not generate an error.