SELECT query

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?

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 . "' ) ";

How do I do this correctly?

Ron


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: SELECT query

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?
>
> 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 . "' ) ";
>
> How do I do this correctly?
>
> Ron
--

Acts Ministries Christian Evangelism
Where People Matter
12 Burton Street
Belleville, Ontario, Canada
K8P 1E6

ron.piggott@actsministries.org
www.actsministrieschristianevangelism.org

In Belleville Phone: (613) 967-0032
In North America Call Toll Free: (866) ACTS-MIN
Fax: (613) 967-9963


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: SELECT query

------=_Part_38032_17601258.1229911433543
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Sun, Dec 21, 2008 at 7:56 PM, Ron Piggott > 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?
> >
> > 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 . "' ) ";
> >
> > How do I do this correctly?
> >
> > Ron
> --
>
> Acts Ministries Christian Evangelism
> Where People Matter
> 12 Burton Street
> Belleville, Ontario, Canada
> K8P 1E6
>
> ron.piggott@actsministries.org
> www.actsministrieschristianevangelism.org
>
> In Belleville Phone: (613) 967-0032
> In North America Call Toll Free: (866) ACTS-MIN
> Fax: (613) 967-9963
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
query code looks correct. are you getting an error? have you tried echoing
out the sql and testing in phpmyadmin or some other gui tool?

--

Bastien

Cat, the other other white meat

------=_Part_38032_17601258.1229911433543--

Re: Re: SELECT query

Bastien Koert wrote:
> On Sun, Dec 21, 2008 at 7:56 PM, Ron Piggott >> 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/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php