Advanced MySQL Search Help

Question

I've been trying to come up with something for a while now to no avail. My MySQL knowledge is rudimentary at best so I could use some guidance on what I should use for the following:

I have 2 tables ('bible' and 'books') that I need to search from. Right now I am just searching 'bible' with the following query:

SELECT * 
  FROM bible 
 WHERE text LIKE '%" . $query . "%' 
 ORDER BY likes DESC 
    LIMIT $start, 10

Now, I need to add another part that searches for some pretty advanced stuff. Here is what I want to do in pseudocode which I am aware doesn't work:

SELECT * 
  FROM bible 
 WHERE books.book+' '+bible.chapter+':'+bible.verse = '$query'

$query would equal something like Genesis 1:2, Genesis coming from books.book, 1 coming from bible.chapter and 2 coming from bible.verse

Any help/guidance on this is much appreciated =)

Answer 1

You need to parse the query into book, chapter and verse in php first.

A regular expression should work:

preg_match("/(.+)([0-9]+):([0-9]+)/",$query,$matches);
$book    = trim(matches[1]); // use trim to remove extra spaces
$chapter = matches[2];
$verse   = matches[3];

Then your sql query becomes:

SELECT * FROM bible WHERE books.book = '$book' AND bible.chapter= '$chapter' AND bible.verse ='$verse' -- watch out for sql injection here! use prepared statements!

Answer 2

You're close. To concatenate the fields use the CONCAT() function:

SELECT * FROM bible WHERE CONCAT(books.book, ' ', bible.chapter, ':', bible.verse) = '$query'

Answer 3

You can use MySQL concatenation:

SELECT * 
FROM bible JOIN books
WHERE CONCAT(books.book, ' ', bible.chapter, ':', bible.verse) = '$query'

I'm not sure what your foreign key is linking books to bible, but that may need specification as well.

Answer 4

I would recommend designing a way in your application code to break up that query so that you search for the book, chapter, and verse separately from the keyword.

That means you need columns for book, chapter, and verse that are separate from the verse text.

You should also use a FULLTEXT index because the LIKE wildcard searches are extremely inefficient.

Here's how I'd run the query in PHP using PDO:

$quoted_query = $pdo->quote($query);
$sql = "SELECT * FROM bible 
        WHERE book = ? AND chapter = ? AND verse = ?
          AND MATCH(text) AGAINST ({$quoted_query})"
$stmt = $pdo->prepare($sql);
$stmt->execute(array($book, $chapter, $verse));

I'd rather use a parameter for the fulltext query too, but MySQL doesn't support that.