2 Replies - 1432 Views - Last Post: 22 August 2008 - 11:52 PM Rate Topic: -----

#1 HowdeeDoodee   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 17-June 08

Display a range of records from one table while displaying only one re

Posted 22 August 2008 - 06:41 PM

I do text conversions. Conversions are made on documents originally written in the 1800's into Mysql db format.

The first effort in the present conversion series has Bible reference citations with no verse range.

Example: Genesis 1:10

Here is a web page example of the output from the db using no verse range. (Display distortions are to be corrected.)

My Webpage

Processing the above url using the present script was not a problem.

However, the second series of documents to be converted in the conversion series had Bible citations with verse ranges. This is a problem because my present script does not accomodate a range of verses.

Example: Genesis 1:10-16

One table I am using now (called BibleTextInserter) is like this:

Version | VerseRef | VerseText | LNum
KJV | Gen 1:1 | In the beginning God created.... 31104
KJV | Gen 1:2 | And the earth was without form... 31105

A second table I am using now is called Comm_Clarke holding commentary text is like this:

PrevNextNum | Source | VerseRef | Text | PNLinks | Index
2001 | BRN | Gen 1:1 | xyz xyz | htlm code | 1
2002 | BRN | Gen 1:2 | xyz xyz | htlm code | 2
2003 | BRN | Gen 1:3 | xyz xyz | htlm code | 3

My query statement and other script info are as follows:

$query = "SELECT * FROM Comm_Clarke , BibleTextInserter WHERE Comm_Clarke.VerseRef = BibleTextInserter.VerseRef AND (BibleTextInserter.VerseRef = '$SeeAlso') AND (Comm_Clarke.Source = $BRN)
$result = mysql_query($query) or die(mysql_error());
if (mysql_num_rows($result) > 0) {			  //moved from down below
while ($row = mysql_fetch_row($result)) {
echo "<br />";
//$row[1] = Source (ACL, etc)
//$row[2] = VerseRef
//$row[3] = Text of Commentary
//$row[4] = Previous Next Links
//$row[5] = LNUM???
//$row[6] = Version or KJV
//$row[7] = VerseRef
//$row[8] = VerseText
//set variables
$PNLinks = $row[4];
$Text = $row[3];
$Source = $row[1];
$VerseText = $row[8];
$ShowActualVerseRef = $row[02];
$ShowSpaces = "  ";
$ShowAllVerseRefAndHyperlink = $ShowActualVerseRef.$ShowSpaces.$ShowVerseLink;
$VerseRefOne = $row[02];
$ShowVerseRefAndTrailingSpaces = $ShowActualVerseRef.$ShowSpaces;

//echo statements showing content of $row elements go here



I need to use the basic script I am using now but I also need to reterieve a range of Bible reference records from the table BibleTextInserter while retrieving only one record from Comm_Clarke.

In achieving the above perhaps I need to...

1. Disaemble the verse reference into book, chapter, and verse range (if any).

2. Access BibleTextInserter db to retrieve records based upon book, chapter, and verse numbers (using a range or no range.)

If need be, I can change the BibleTextInserter table to the following:

index | book | chapter | verse text
1 | 1 | 1 | 1 | In the beginning...
2 | 1 | 1 | 2 | And the earth was without form...

OR

index | book | chapter | verse | text
1 | Gen | 1 | 1 | In the beginning...
2 | Gen | 1 | 2 | And the earth was without form...

Thank you in advance for your replies.

Is This A Good Question/Topic? 0
  • +

Replies To: Display a range of records from one table while displaying only one re

#2 Martyr2   User is offline

  • Programming Theoretician
  • member icon

Reputation: 5237
  • View blog
  • Posts: 14,031
  • Joined: 18-April 07

Re: Display a range of records from one table while displaying only one re

Posted 22 August 2008 - 09:23 PM

Your thoughts about breaking the field up is absolutely on the right track. What your book/chapter/verse field represents is a "non-atomic" field in the database world. That is, a field which represents more than one piece of data. Following normalization rules to the 3rd form has you remove any fields which are seen as non-atomic. To do this you split the field up into its component parts so that each are atomic.

This does a few things for you. It will help you make other relationships later, it helps in searching, it helps with queries when you don't need to pull all the fields (like perhaps you just want to pull verse 1 despite the book it is from).

So you could certainly go that route and get a lot of benefits out of it. It is probably the wisest move too for future development with it.

:)
Was This Post Helpful? 0
  • +
  • -

#3 HowdeeDoodee   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 17-June 08

Re: Display a range of records from one table while displaying only one re

Posted 22 August 2008 - 11:52 PM

View PostMartyr2, on 22 Aug, 2008 - 09:23 PM, said:

Your thoughts about breaking the field up is absolutely on the right track. What your book/chapter/verse field represents is a "non-atomic" field in the database world. That is, a field which represents more than one piece of data. Following normalization rules to the 3rd form has you remove any fields which are seen as non-atomic. To do this you split the field up into its component parts so that each are atomic.

This does a few things for you. It will help you make other relationships later, it helps in searching, it helps with queries when you don't need to pull all the fields (like perhaps you just want to pull verse 1 despite the book it is from).

So you could certainly go that route and get a lot of benefits out of it. It is probably the wisest move too for future development with it.

:)


Thank you for your comments. I will followup with further questions related to code implementation.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1