Home > Archive > PHP DB > September 2004 > spliting keywords by GET method and SQL query
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 |
spliting keywords by GET method and SQL query
|
|
| Murat Biyikli 2004-09-30, 8:56 pm |
| 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.
| |
| Frank Flynn 2004-09-30, 8:56 pm |
| 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.
|
|
|
|
|