SQL query question: Searching for multiple key words

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
abeall
Forum Commoner
Posts: 41
Joined: Sun Feb 04, 2007 11:53 pm

SQL query question: Searching for multiple key words

Post 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?
abeall
Forum Commoner
Posts: 41
Joined: Sun Feb 04, 2007 11:53 pm

Post 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.
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

Post 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
abeall
Forum Commoner
Posts: 41
Joined: Sun Feb 04, 2007 11:53 pm

Post 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.
Post Reply