a query that drives me bananas - a help is appreciated

[Stefano Mtangoo]

Hi,
I know this is not PHP issue but we here writes a lot of database SQL querries so I hope for a help. I have a bible script that is supposed to query the scriptures between two intervals. My table is named Bible and have columns: ID (int), Book (int), Chapter(int), Verse (int) and Scripture(text).

Now the books are unique ie 1-66 but chapters keep repeating, and so do verses. Now suppose I want to get scripture between book 1 chapter 1 verse 1 and book 2 chapter 3 Verse 1, how do I go about? My knowledge of SQL have taken me to a dead end!

You can check how bible have its chapters and verses arranged at www.biblegateway.com.

Thanks friend :)

[pritaeas]

This might help.

[sudeepjd]

Are your ID's in the order as per the sequence of the Books, chapters and verses in the bible? If so, then you can pull the ID for from where you want to start and the ID till where you want to get the Scripture till and then pull all the scriptures in between the 2 ID's.

[Stefano Mtangoo]

This might help.

thanks pritaeas, i'm checking the link and I will be back

[Stefano Mtangoo]

Are your ID's in the order as per the sequence of the Books, chapters and verses in the bible? If so, then you can pull the ID for from where you want to start and the ID till where you want to get the Scripture till and then pull all the scriptures in between the 2 ID's.

sudeep,
id is autoincrement and I don't programatically know where new book or chapter begins at as far as ID is concerned.
Thanks for replying

[Stefano Mtangoo]

pritaeas,
isn't this a link to this same post?

[quasipickle]

sudeep,
id is autoincrement and I don't programatically know where new book or chapter begins at as far as ID is concerned.
Thanks for replying

Can you do 1 query to get the IDS (say, Genesis 1:1 = ID 1, Psalms 2:30 = ID 4562), then ask for anything between IDs 1 and 4562? I think that's what ~sudeep was thinking.

As long as the verses were entered in order, it should work.

Otherwise, I think you'll have to do a book-by-book query.

[somedude3488]

I would say this is one of those problems that can't be solved with one query.

Couldn't you just select the first id:

SELECT `id` FROM `bible` WHERE `book` = 1 AND `chapter` = 1 AND `verse` = 1 LIMIT 1

and the second:

SELECT `id` FROM `bible` WHERE `book` = 2 AND `chapter` = 3 AND `verse` = 1 lIMIT 1

Then get the results via:

SELECT `scripture` FROM `bible` WHERE `id` BETWEEN [id_1] AND [id_2] ORDER BY `book` ASC,`chapter` ASC,`verse` ASC

[edit]
Sorry I pretty much said what the others above me said. I guess I should of read the other posts before responding.

[sudeepjd]

Apart from what I have said above, there is another way if you if you do not have the ID's in sequence. An autoincrement would usually put the ID's in sequence as long as you have added the data in the proper order.

But if you have not, here is another solution. Create a new column in the table and give it a name say `id_ord`.

You will have to create formula so that the book 1, Chapter 2 and Verse 4 are integrated. e.g. id_ord = Book * 100000 + Chapter * 1000 + Verse Store the above value in the 'id_ord' in the DB. Using this, you can create the values from your PHP code for the from and the to id_orders when a request is given. for e.g. A user asks for scriptures between book 1 chapter 1 verse 1 and book 2 chapter 3 Verse 1.

$idOrd_from = 1*100000+1*1000+1 = 101001 and

$idOrd_to = 2*100000+3*1000+1 = 203001

You should now be able to pull the scriptures between these 2 id_ord

To add this into your DB use the following query UPDATE bible SET id_ord=100000*book+1000*chapter+verse; Hope this helps.

[pritaeas]

I'm very sorry. I thought I was posting this link:
http://stackoverflow.com/questions/1365678/selecting-a-range-of-rows-using-3-different-indices

[mschroeder]

Just a general observation, feel free to ignore the following.

In your table you're using a surrogate primary key, the auto-incrementing column. When you clearly have a natural and practical composite primary key in (book, chapter, verse) as they are always provided and the combination of three digits may not ever repeat.

The glaring issue I see with using a surrogate key in this scenario is that a record can be mistakenly added with duplicate information.

e.g.
ID, Book, Chapter, Verse, Scripture
1, 1, 1, 1, ...
2, 1, 1, 2, ...
3, 1, 1, 1, ...

By eliminating the ID column it becomes impossible to insert the third record with the duplicate information as the composite pk fails.

Book, Chapter, Verse, Scripture
1, 1, 1, ...
1, 1, 2, ...
1, 1, 1, ...

[Stefano Mtangoo]

Can you do 1 query to get the IDS (say, Genesis 1:1 = ID 1, Psalms 2:30 = ID 4562), then ask for anything between IDs 1 and 4562? I think that's what ~sudeep was thinking.

As long as the verses were entered in order, it should work.

Otherwise, I think you'll have to do a book-by-book query.

Noted, thanks a lot!

[Stefano Mtangoo]

I would say this is one of those problems that can't be solved with one query.

Couldn't you just select the first id:

SELECT `id` FROM `bible` WHERE `book` = 1 AND `chapter` = 1 AND `verse` = 1 LIMIT 1

and the second:

SELECT `id` FROM `bible` WHERE `book` = 2 AND `chapter` = 3 AND `verse` = 1 lIMIT 1

Then get the results via:

SELECT `scripture` FROM `bible` WHERE `id` BETWEEN [id_1] AND [id_2] ORDER BY `book` ASC,`chapter` ASC,`verse` ASC

[edit]
Sorry I pretty much said what the others above me said. I guess I should of read the other posts before responding.

Thanks KK,
I missed you all the times you were not here!

[Stefano Mtangoo]

Apart from what I have said above, there is another way if you if you do not have the ID's in sequence. An autoincrement would usually put the ID's in sequence as long as you have added the data in the proper order.

But if you have not, here is another solution. Create a new column in the table and give it a name say `id_ord`.

You will have to create formula so that the book 1, Chapter 2 and Verse 4 are integrated. e.g. id_ord = Book * 100000 + Chapter * 1000 + Verse Store the above value in the 'id_ord' in the DB. Using this, you can create the values from your PHP code for the from and the to id_orders when a request is given. for e.g. A user asks for scriptures between book 1 chapter 1 verse 1 and book 2 chapter 3 Verse 1.

$idOrd_from = 1*100000+1*1000+1 = 101001 and

$idOrd_to = 2*100000+3*1000+1 = 203001

You should now be able to pull the scriptures between these 2 id_ord

To add this into your DB use the following query UPDATE bible SET id_ord=100000*book+1000*chapter+verse; Hope this helps.

Thanks, but I admit I haven't understood

[Stefano Mtangoo]

I'm very sorry. I thought I was posting this link:
http://stackoverflow.com/questions/1365678/selecting-a-range-of-rows-using-3-different-indices

Ahha! I get it!

[Stefano Mtangoo]

Just a general observation, feel free to ignore the following.

In your table you're using a surrogate primary key, the auto-incrementing column. When you clearly have a natural and practical composite primary key in (book, chapter, verse) as they are always provided and the combination of three digits may not ever repeat.

The glaring issue I see with using a surrogate key in this scenario is that a record can be mistakenly added with duplicate information.

e.g.
ID, Book, Chapter, Verse, Scripture
1, 1, 1, 1, ...
2, 1, 1, 2, ...
3, 1, 1, 1, ...

By eliminating the ID column it becomes impossible to insert the third record with the duplicate information as the composite pk fails.

Book, Chapter, Verse, Scripture
1, 1, 1, ...
1, 1, 2, ...
1, 1, 1, ...

Thanks, but I have not grasped what you want to say!

[Stefano Mtangoo]

I'm very sorry. I thought I was posting this link:
http://stackoverflow.com/questions/1365678/selecting-a-range-of-rows-using-3-different-indices

The Link have exactly same problem as mine but the man didn't said if he conquered!

[Stefano Mtangoo]

Thanks all friends!
I think I missed UNION to concatenate SELECT queries as you haves shown.
Thanks everybody and I assume the problem is finished

[mschroeder]

-evstevemd

My suggestion was regarding the structure of your table.
The three columns Book, Chapter and Verse together make a perfect primary key eliminating the need to create a surrogate primary key (ID in your table).

My other suggestion, would be to split the actual scripture text out into a second table and join it to the Bible table. Reason being, mysql will use a dynamic row format when it encounters columns with a variable length, like TEXT or BLOB, so sort operations have to occur on disk instead of within memory. This is exponentially slower. Since your sort operations would most likely be against book, chapter or verse columns, this would drastically speed up your queries.

[Stefano Mtangoo]

-evstevemd

My suggestion was regarding the structure of your table.
The three columns Book, Chapter and Verse together make a perfect primary key eliminating the need to create a surrogate primary key (ID in your table).

My other suggestion, would be to split the actual scripture text out into a second table and join it to the Bible table. Reason being, mysql will use a dynamic row format when it encounters columns with a variable length, like TEXT or BLOB, so sort operations have to occur on disk instead of within memory. This is exponentially slower. Since your sort operations would most likely be against book, chapter or verse columns, this would drastically speed up your queries.

thanks MS,
so what schema are you suggesting? Remember Book is Unique but Chapters and verses are repeatable!

[mschroeder]

--
-- Table structure for table `Bible`
--

CREATE TABLE IF NOT EXISTS `Bible` (
  `Book` int(11) NOT NULL,
  `Chapter` int(11) NOT NULL,
  `Verse` int(11) NOT NULL,
  `Scripture` int(11) NOT NULL,
  PRIMARY KEY (`Book`,`Chapter`,`Verse`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `Scriptures`
--

CREATE TABLE IF NOT EXISTS `Scriptures` (
  `Scripture` int(11) NOT NULL AUTO_INCREMENT,
  `ScriptureText` text CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`Scripture`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

In this schema, Bible.Scripture is a Foreign Key to Scriptures.Scripture. I'm assuming Book, Chapter and Verse all join to other tables in your database already.

The primary key becomes a composite primary key of the columns Book, Chapter and Verse. So it doesn't matter if Chapter and Verse are not unique as long as the three columns combined are unique. This prevents inserting any duplicate records accidentally, which using just an auto-incrementing primary key does not.

Splitting it out into two tables is probably overkill considering you have a hard limit to how much data will actually be contained in the table, but it is an reasonable optimization for MyISAM tables. As per my last post the end result is the bible table can now be sorted in memory instead of on disk. You can read about the difference between fixed with and dynamic row formats here:
http://dev.mysql.com/doc/refman/5.0/en/static-format.html
http://dev.mysql.com/doc/refman/5.0/en/dynamic-format.html

[Stefano Mtangoo]

--
-- Table structure for table `Bible`
--

CREATE TABLE IF NOT EXISTS `Bible` (
  `Book` int(11) NOT NULL,
  `Chapter` int(11) NOT NULL,
  `Verse` int(11) NOT NULL,
  `Scripture` int(11) NOT NULL,
  PRIMARY KEY (`Book`,`Chapter`,`Verse`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `Scriptures`
--

CREATE TABLE IF NOT EXISTS `Scriptures` (
  `Scripture` int(11) NOT NULL AUTO_INCREMENT,
  `ScriptureText` text CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`Scripture`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

In this schema, Bible.Scripture is a Foreign Key to Scriptures.Scripture. I'm assuming Book, Chapter and Verse all join to other tables in your database already.

The primary key becomes a composite primary key of the columns Book, Chapter and Verse. So it doesn't matter if Chapter and Verse are not unique as long as the three columns combined are unique. This prevents inserting any duplicate records accidentally, which using just an auto-incrementing primary key does not.

Splitting it out into two tables is probably overkill considering you have a hard limit to how much data will actually be contained in the table, but it is an reasonable optimization for MyISAM tables. As per my last post the end result is the bible table can now be sorted in memory instead of on disk. You can read about the difference between fixed with and dynamic row formats here:
http://dev.mysql.com/doc/refman/5.0/en/static-format.html
http://dev.mysql.com/doc/refman/5.0/en/dynamic-format.html

Mhh!
Let me digest this first!
Thanks

[Stefano Mtangoo]

I resolved it with

SELECT * FROM Bible WHERE ID BETWEEN
(SELECT ID FROM Bible WHERE Book=64 AND Chapter=1 AND Verse=1) 
AND
(SELECT ID FROM Bible WHERE Book=66 AND Chapter=1 AND Verse=3);