Page 1 of 1
SQL query question: Searching for multiple key words
Posted: Fri Feb 09, 2007 12:55 am
by abeall
I have a basic SQLite table with various short entries. I am creating a simple search by doing something like:
Code: Select all
$term = $_POST['search'];
$query = "SELECT * FROM Posts WHERE title LIKE '%".$term."%'";
$result = $db->arrayQuery($query,SQLITE_ASSOC);
It works great, but what I would like to do is be able to search for multiple keywords at once. How would this be done? Do I need multiple LIKE commands, or can it be combined? I figured I would convert commas to spaces, then explode the string by spaces to determine the unique keywords, something like:
Code: Select all
$searchquery = $_POST['search'];
$keywords = explode(' ',$searchquery);
But then I'm not sure how to search for Posts which contain any one of those keywords, other than create a bunch of OR title LIKE $keywords[x]. Any better suggestions?
Posted: Fri Feb 09, 2007 1:20 am
by abeall
Here's what I did. Someone clue me in if it's idiotic:
Code: Select all
$terms = sqlite_escape_string($_GET['searchquery']);
$terms = str_replace(',',' ',$terms);
$terms = explode(' ',$terms);
$search = "";
foreach($terms as $t){
if(strlen($t)<3) continue; // skip search terms of 3 chars or less
$search .= ($search==''?'':' OR ')."title LIKE '%".$t."%' OR body LIKE '%".$t."%'";
}
//echo $search;
$query = "SELECT * FROM Posts WHERE (".$search.")";
It works, but creates one ugly query.
Posted: Fri Feb 09, 2007 7:23 am
by ryuuka
it's not ideotic to do this but inputting stuff in an sql query can be rather dangerous
i think this is called a sql injection: meaning yo can input any query into the database according to the user submitted query.
your server can be hacked this way in other words.
visit the php - security forum for more information on this
another idea to do this would be this:
Code: Select all
$query = "SELECT * FROM Posts WHERE title like %'.$term 1.'% or title like %'.$term 1.'%
and so on this way the you can limit the number of searches for terms and the database will act on the information within the fields as if they were database entries
hope it helps
Posted: Fri Feb 09, 2007 9:08 am
by abeall
If I understand correctly about your reference to SQL injection, I believe I'm safe by using:
Code: Select all
$terms = sqlite_escape_string($_GET['searchquery']);
Or were you referring to that fact that, in my current code, a user could input a million search terms and PHP would create a massive overloaded query? That's a good point, I'll limit/truncate the array to something like 10. The textbox users enter search for is only 15 characters long anyway.