Re: spliting keywords by GET method and SQL query
| From: | Frank Flynn | Date: | Thu, 30 Sep 2004 20:48:16 +0000 |
| Subject: | Re: spliting keywords by GET method and SQL query | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-36496@lists.php.net to get a copy of this message | ||
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.