Re: [PHP-DB] Re: SELECT query

Bastien Koert wrote:
> On Sun, Dec 21, 2008 at 7:56 PM, Ron Piggott <[email protected]
>> wrote:
> 
>> One more thing ... Bible is stored by verses, t is the text of the verse
>>
>> On Sun, 2008-12-21 at 19:52 -0500, Ron Piggott wrote:
>>> I am working on a web based Bible searching query.  So far I am able to
>>> generate:
>>>
>>> SELECT * FROM `bible_verses` INNER JOIN `bible_books` ON
>>> `bible_books`.`id` = `bible_verses`.`b` WHERE `t` IN ( 'Lord' ,
>>> 'Jesus' ) LIMIT 0 , 10
>>>
>>> Is an "IN" the correct syntax to use?

This will work fine. An "IN" clause is like multiple OR's:

... where t = 'Lord' OR t = 'Jesus' ...

>>> I am trying to take what the user types in (variable is $keyword_search)
>>> and allow a search where if the same 2 or 3 words are in the verse of
>>> the Bible that verse would be a match, but not necessarily be beside
>>> each other.
>>>
>>> $keyword_search_string  = str_replace(" ", "' , '", $keyword_search);
>>> $query .= "IN ( '" . $keyword_search_string . "' ) ";

This will seem a little long winded but you need to check for sql 
injection. As it stands, you'll get an error when you search for a name 
with a quote (o'reilly). Might not be in the bible (I have no idea) but 
doesn't mean someone won't try it ;)


// look at the keywords one by one - they are space separated
$keywords = explode(' ', $keyword_search);

// now go through them all and escape them.
$keyword_searches = array();
foreach ($keywords as $keyword) {
   $keyword_searches[] = mysql_real_escape_string($keyword);
}

// then turn it all into an "IN" string.
$keyword_search_string = "IN ('" . implode("','", $keyword_searches) . "')";


You may want to eventually look at full text searching, depending on how 
slow this is and how important search is to your site. The syntax is 
completely different.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

-- 
Postgresql & php tutorials
http://www.designmagick.com/