Re: spliting keywords by GET method and SQL query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx wrote:


From: "Murat BIYIKLI" <admin@xxxxxxxxxxxxxxxxxxxx> Date: September 29, 2004 8:47:28 AM PDT To: php-db@xxxxxxxxxxxxx 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.

[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux