高级MySQL搜索帮助

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!