how to select a chapter of records which have keywords

Discussion in 'MySQL' started by gilgalbiblewheel, Sep 28, 2008.

0
  1. #1
    I have a database of a chapter field a verse field and a text field:
    by looking at the text data, let's say I'm looking for the keywords:
    blablabasasda and sdcj.
    Looking at chapter 2 blablabasasda is found but not sdcj. So that chapter is disregarded. But in chapter 1 since words keywords are found then Chapter 1 is taken into account.
     
    gilgalbiblewheel, Sep 28, 2008 IP
  2. wootty

    wootty Peon

    Messages:
    447
    Likes Received:
    22
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Using the example above something like this might do it:

    select distinct(chapter) from tablename
    where text_date like '%blablabasada%' OR text_data like '%sdcj%'

    this is just an example - how you implement it depends on exactly what info you're after, how many search strings you're looking for etc.
     
    wootty, Sep 28, 2008 IP
  3. gilgalbiblewheel

    gilgalbiblewheel Well-Known Member

    Messages:
    435
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    101
    #3
    Yeah but that won't guarantee that it's going to find the combination of words within that chapter. It might find one or two of them or even all of them. But it HAS to find all within the chapter of records.

    This is the statement that I was working on. It's supposed to search the 3 names Peter, James and John within any chapter (of records) in the book that has the three:
    SELECT book, chapter, verse, text_data FROM bible WHERE chapter IN 
( 
SELECT chapter FROM bible WHERE
text_data IN ( '%peter%', '%james%', '%john%' )
GROUP BY chapter HAVING 
SUM(CASE WHEN text_data LIKE '%peter%'
THEN 1 ELSE 0 END) > 0 AND
SUM(CASE WHEN text_data LIKE '%james%'
THEN 1 ELSE 0 END) > 0 AND 
SUM(CASE WHEN text_data LIKE '%john%'
THEN 1 ELSE 0 END) > 0
)
    Code (markup):
    Is that the way it should be?
    The Heidi SQL is frozen.
    I waited for hours and no success. I had to restart my computer even when I was trying on HeidiSQL.
     
    gilgalbiblewheel, Sep 30, 2008 IP
  4. chisara

    chisara Peon

    Messages:
    141
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #4
    I don't have Heidi SQL but could you try the following statement.
    SELECT book, chapter, text_data FROM bible WHERE chapter IN
    ( SELECT chapter FROM bible WHERE text_data like '%peter%' and chapter IN
    ( SELECT chapter FROM bible WHERE text_data like '%james%' and chapter IN
    ( SELECT chapter FROM bible WHERE text_data like '%james%' )
    )
    )
    I omitted the verse from the first select since it has no relevance due to the query used. You are looking for chapters that contain these words.

    This statement is basically doing three subselects, keep in mind that in the absense of any usable indexes it will , depending on the intelligence of the Database server, perform three full table scans to resolve your query which depending on the sizeof the table can take some time.

    Some databases like Postgresql and Mysql can't use an index in conjuction with a like '%something' clause.(Wildcard at beginning of string) Since HeidiSQL is a frontend to Mysql your out of luck with regards to the regular index with Mysql. You might want to look into the Full Text Index functionality to see if this can be used.
    I have never used this in Mysql so I can't tell if this is applicable to your system.
     
    chisara, Oct 1, 2008 IP