1

My WordPress system contains posts with a field containing Bible references and ranges, eg.

John 5,1-10; Matthew 3,2; Mark 1,1-5

Users searching for a Bible references e.g. John 5,3 or Matthew 3,2 or Mark 1,2 would all need to find the above post.

From an SQL point of view is this even possible? If not what would I have to change in this approach?

  • If this is a crazy idea, I could consider putting book / chapter / verse range in separate fields, so we'd only have to deal with the question: source data in db: "1-10" and the search for "3" should bring up that source post, since 3 is between 1 and 10. Is this easier to solve? – Daniel S Sep 15 '19 at 16:42
1

This is very doable, assuming you setup the structure correctly.

create database dba248824;
use dba248824;

This post table is a placeholder to make the queries work. This table already exists in your project, under some name (I don't know what), and holds your posts that have references to Bible verses.

CREATE TABLE post (
`post_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`post_text` MEDIUMTEXT NOT NULL,
PRIMARY KEY (`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Next up, we have a table to define all the books of the bible. It is not strictly necessary to have a separate table, and purists will lash me for not making use of natural keys, but I choose to include this table to minimize the size of the indexes in other tables. This only needs to be populated once, with all the books of the Bible.

CREATE TABLE bible_book (
`bible_book_id` TINYINT UNSIGNED NOT NULL,
`bible_book_name` VARCHAR(40) NOT NULL,
PRIMARY KEY (`bible_book_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The bible_verse table also only needs to be populated once, and would contain every verse in the Bible. Actually, if you only need to reference the verses and not return the text of the verses you can completely skip this table.

If you choose to include this table, you may choose to add a FOREIGN KEY constraint from post_bible_verse to reference this bible_verse table.

CREATE TABLE bible_verse (
`bible_book_id` TINYINT UNSIGNED NOT NULL,
`chapter` TINYINT UNSIGNED NOT NULL,
`verse` TINYINT UNSIGNED NOT NULL,
`text` VARCHAR(255) NOT NULL,
PRIMARY KEY (`bible_book_id`,`chapter`,`verse`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The post_bible_verse table is the work-horse of this setup. Here, you recover each individual verse that is referenced in a post. You'll have to translate ranges into a list of individual verses using some server side code, but once done you can easily insert into here each time a post is made referencing some verses.

CREATE TABLE post_bible_verse (
`post_id` INT UNSIGNED NOT NULL,
`bible_book_id` TINYINT UNSIGNED NOT NULL,
`chapter` TINYINT UNSIGNED NOT NULL,
`verse` TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (`post_id`,`bible_book_id`,`chapter`,`verse`),
UNIQUE KEY (`bible_book_id`,`chapter`,`verse`,`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

To see how this setup works, lets populate some values.

First, the bible_book table. I used the "number" of the book in the Bible as the bible_book_id primary key. That is, John is the 43rd book of the bible, so it gets bible_book_id of 43. Etc.

(Remember, this only has to be done once in your case, to include all books.)

INSERT INTO bible_book
(bible_book_id,bible_book_name)
VALUES
(43,'John'),
(40,'Matthew'),
(41,'Mark');

Then, we populate all the Bible verses. As mentioned before, this is not necessary if you don't need to return the actual text of the verses.

This INSERT statement includes all the verses you mention in your example.

INSERT INTO bible_verse
(bible_book_id,chapter,verse,text)
VALUES
(43,5,1,'actual verse text here'),
(43,5,2,'actual verse text here'),
(43,5,3,'actual verse text here'),
(43,5,4,'actual verse text here'),
(43,5,5,'actual verse text here'),
(43,5,6,'actual verse text here'),
(43,5,7,'actual verse text here'),
(43,5,8,'actual verse text here'),
(43,5,9,'actual verse text here'),
(43,5,10,'actual verse text here'),
(40,3,2,'actual verse text here'),
(41,1,1,'actual verse text here'),
(41,1,2,'actual verse text here'),
(41,1,3,'actual verse text here'),
(41,1,4,'actual verse text here'),
(41,1,5,'actual verse text here');

Create a post to represent what is in your system. (You won't need to do anything in your real setup, as your blog posts already exist and presumably have some id column to use.)

INSERT INTO post
(post_id,post_text)
VALUES
(1,'some blog post here');

And finally, insert all the verses that correspond to this post. You'll have to include code to insert these verses when a post is made or updated, as a text range will not work.

INSERT INTO post_bible_verse
(post_id,bible_book_id,chapter,verse)
VALUES
(1,43,5,1),
(1,43,5,2),
(1,43,5,3),
(1,43,5,4),
(1,43,5,5),
(1,43,5,6),
(1,43,5,7),
(1,43,5,8),
(1,43,5,9),
(1,43,5,10),
(1,40,3,2),
(1,41,1,1),
(1,41,1,2),
(1,41,1,3),
(1,41,1,4),
(1,41,1,5);

Now lets see it in action.

User searches for John 5,3. Server parses that and runs the following query:

SELECT
post_id
FROM post
WHERE EXISTS (
  SELECT 1 FROM post_bible_verse pbv
  WHERE pbv.bible_book_id = 43
  AND pbv.chapter = 5
  AND pbv.verse = 3
  AND pbv.post_id = post.post_id
);

+---------+
| post_id |
+---------+
|       1 |
+---------+

And it returns post_id of 1 (which is the post that referenced those verses.)

User searches for Matthew 3,2:

SELECT
post_id
FROM post
WHERE EXISTS (
  SELECT 1 FROM post_bible_verse pbv
  WHERE pbv.bible_book_id = 40
  AND pbv.chapter = 3
  AND pbv.verse = 2
  AND pbv.post_id = post.post_id
);

+---------+
| post_id |
+---------+
|       1 |
+---------+

Again found our post.

Now Mark 1,2.

SELECT
post_id
FROM post
WHERE EXISTS (
  SELECT 1 FROM post_bible_verse pbv
  WHERE pbv.bible_book_id = 41
  AND pbv.chapter = 1
  AND pbv.verse = 2
  AND pbv.post_id = post.post_id
);

+---------+
| post_id |
+---------+
|       1 |
+---------+

And finally, search for Matthew 3,1, which is NOT referenced by your post.

SELECT
post_id
FROM post
WHERE EXISTS (
  SELECT 1 FROM post_bible_verse pbv
  WHERE pbv.bible_book_id = 40
  AND pbv.chapter = 3
  AND pbv.verse = 1
  AND pbv.post_id = post.post_id
);

Empty set

And we verify that the post is NOT found this time.

So, yes, it is very possible to create a database structure that allows you do search for posts based on Bible verses, but it does require an appropriate setup, and parsing of the ranges into specific queries.

| improve this answer | |
  • WOW Willem, you took so much time to show this solution step by step. It is easy to follow your explanation. Thank you VERY much!! I was already thinking along those lines, but I was hoping to avoid having to translate a range from John 5,1-10 to 10 entries in the database. But I can see why this is necessary. I will need to find help from a php developer to write such a function. And no, I don't need to have every verse of the bible as text in my system, only the references. – Daniel S Sep 15 '19 at 22:34
  • I'm using Toolset with WordPress which is supposedly a web development system without writing code... of course that is an illusion... I've set up all the fields (book, chapter, verse) in my database. Now I need to figure out the code to translate range to multiple entries. Can't be that hard, it must be some kind of loop. Thanks again for giving me such a detailed recipe!! – Daniel S Sep 15 '19 at 22:41
  • @DanielS Glad I could help! Good luck with the rest of it. – Willem Renzema Sep 16 '19 at 1:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.