Tables are stored in the MyBayCity.com database.

Sample query:

<cfquery name="SPIq_Stories" datasource="MyBayCity" >
      SELECT * FROM mbc_tsportspage_idx
      WHERE SPI_Type = 'P'
      ORDER-BY SPI_IssueDate, SPI_Issue_Page
</cfquery>

The primary data used by the Sports Page system is a single table in the MyBayCity.com database.

This single table contains TWO types of records:
      Type P     One record for every PAGE.
      Type T     One record for every THUMBNAIL / CONTACT PAGE on the server.

Each record contains
      Unique ID
      Issue and the page number,
      Type (P or T)
      Text name of the issue
      LINK
          Type P link points to the Thumbnail / contact page image file, and the matching T record.
          Type T link points to the URL where the issue is sold.
      Data which is searched.




DataSource: MyBayCity

Table: mbc_tsportspage_idx

The raw data files with files made by scanning the printed pages to make files of some sort. That process was done by a commercial service.

OJ then used OCR software to take those scans and convert them to plain text files. He used 2 or 3 methods to do the various "generations" of conversions we've used.

The 2023 version was done about a year ago for Vickie to try to use. When SGK got the files OJ wasn't sure how he had made them. It was either "Notepad 2" or perhaps WORD.

Regardless of method, there were at least two formats. The first, which we experimented with in 2021 and 2022, were HTML renditions. The conversion programs tried to identify HTML patterns and clean those up. The cleaned data was then packed into a block and that block was saved in the database in a large text field.

This method of just saving the text from an entire page works. Our "search" algorythm uses the SQL "LIKE" operator to search. The query uses a constructed query that reads:
  WHERE SPI_Type = 'P' and SPI_Year = '1978' and SPI_Data like '%David LaPraire%'
That does not produce perfect searches, but it's very close.

The method used in 2023 is a pure text file. It does have a lot of special characters embedded, but we just take most of them on face value and save in the database.

The "pure text" sometimes violates the MySQL database when updated from ColdFusion. The Db operation will throw an error and not update the record. Fortunately, the error messages shows about 50 characters in the area of the problem and that's enough to find the error using notepad.

Our methods is this:

• OJ runs the SPI-0760.cfm script. It shows a list of all the raw files. Each has an UPDATE and a VIEW button.
  Clicking the UPDATE reads the raw data file for one page of an issue. It combines all the data lines into a large block and updates the database.
  
  
• When the data offends MySQL's ColdFusion update, the original raw file needs to be cleaned up so it works.
  
  
• We first download the bad raw file to the local machine appending "-ORIG" to the file name. This is just so we can keep an original copy handy.
  
  Next we open that ORIG file in Notepad. We look at the MySQL update error show in SPI-0760.cfm and pick a few letters from the error message.
  
  Using Notepad's CTRL-F (find) function we search the file for that offending string. When we find it we just select and delete all of the special characters that, inevitably, surround it.
  
  Note that we'll usually clean out some of the other "special character" code that we find in the file. We don't need to, but as long as we're there we might as well get rid of it.
  
  We SAVE AS removing the ORIG so that we have a new file with the standard filename.
  
  Leaving all the stuff open, we Ftp that repaired file back to the web server.
  
  We use the UPDATE button in SPI-0760 again to convert the file. If there are more errors we just repeat the fix. If the convert works, we're done.

Based on use, we've deduced how the current OCF conversion operates. Basically it takes the scan snd works left to right, top to bottom.

(Note that the HTML OCR conversion seemed to try to make a page that actually worked and duplicated the page. It was not perfect, but it did a fair job. But we never used the HTML code to try to produce the pages.)

This method works, well enough, but it does not produce a file that can be easily read. The newspaper is typically printed in columns with display ads included.

The results of this can be a challenge to read. You may get some text from column1, some from colum 2, 3, and 4. Then theres a break and you get more from those same columns.

To confound the problems, there may be different fonts and different size fonts. Lines may not "line up". There can be pictures almost anywhere. Some pictures will include captions. And then there are the display ads. Those can be any size and any orientation. They can include images of their own and multiple fonts too.

It's best to just not worry about these issues. The objective of the Sports Page search program is just to identify which pages contain a search string. We don't care where the words are. We just want to sell the entire page.








 

It was not entirely clear exactly what will be sold.
The plan has evolved and as of Jan 2024, It is understood that we will sell one Kindle book for each issue (normally 16 pages).

A secondary format would be the same type of book prepared and printed by Thumb Printing in Bay City.

A scaned issue of every page of each issue is on the web site. When a customer makes a search, a table will be shown of every page containing the search results.

The table will include a link to purchase the Full book for each collection of pages.
Following the table, ALL of the pages will be shown. The page image is large enough to understand but not to actually read. Blowing the image up will begin to pixelate the image.


OLD NOTES:

A single image (JPG) "contact sheet" containing thumbnail sized images of each of the 16 pages in that issue will be stored on the server. A description of that image will be found in the database as record type "T".

That contact sheet image entry will include a link pointing to wherever that issue is sold.
(For testing, they all point to Kent's "James Milton" book.)

SO... let's says that the name "Steve" appears on four individual pages. Two of those pages are in the 7/5/76 issue and two are in the 7/19/76 issue.

Following the search the system will show a table of the four references and the page and issue the reference appears in.

Following that table of references the system will show the contact sheet image for each of the two issues.

Each contact page image will be a link, which will to to the internet address where the "book" of that issue can be purchased.

The customer will click the link of the issue/book they want and make the purchase.

We sell one BOOK for each issue.

An issue typically has 14 to 16 pages. So the associated book has that many pages.

Most pages were printed in a larger, newspaper format. These had the 14 to 16 pages.

Another group was printed in 8.5 x 11 inch books. Each page was smaller to there were many more pages for each issue.

The book sales link will not distinguish the different styles.

The SEARCH asks the user select a year and enter a search key.

The year originally offered the option to search a single year. That was thought to be necessary due to the time required for a full search.

After testing it was found that the full search of every page for every year was fast enough.

The YEAR list was left in the program, but it only offers an option for ALL years. When we're sure it works we'll remove the YEAR option.

The key word is a single string. If the customer enteres "JOHN JONES" that exact string will be found.

Letter case is not significant. John is the same as JOHN and john.

Warning... the original scan of the page was submitted to an OCR program which changed it to text. There may be cases where a name or phrase was split across 2 lines. If that happened with a case of "john jones", the reference may not be found because there would be a page break between the two name pargs.

The results of the search will be shown in a table including the issue date and the page number.

A "Buy" button will be provided for the FIRST link for the book. That is, if there are 3 hits in the same book (see example below) a "Buy" only shows on the FIRST hit for the book.


 

The KINDLE book requires a unique identifier for each book. We need a place to keep that code.

The database has an entry for every page in every issue. We put the associated KINDLE CODE on those records for each issue.

If there are 14 pages for a specific issue, The program presents a single link where you can enter that code for the entire book. When you click the "UPDATE-KINDLE" button, the code is automatically stored on every page record for that issue.

UPDATE PROGRAM:

A link in the menu shows 780: Update Amazon/Kindle codes

The 780 program initially just shows a list of YEAR buttons. When you click on one of those buttons the screen will reload and show that table of issues for that book.

Once you select a year, you will see that list of pages for the issue.

The FIRST line shows the issue year-month-day followed by all of the page numbers.
(If you hover the mouse over a page number, a tooltip will show some details.)

The SECOND line will ask you to enter kindle book code then click an "UPDATE-KINDLE" button. (To the right are some details about the page as well as the current value of the kindle code for the first page.)

After you run the "UPDATE-KINDLE", the list will show the code that you entered on the particular page. You can also check the info to the right and see the code for the first page.

To update another page just enter it's code and click the "UPDATE-KINDLE". The returned screen will only show the last code that you did. You just enter the next code and click its button. The button only updates the one line that it appears on. If you leave the previous code it will be ignored.

ColdFusion makes it easy to read a file using the <cfloop> tag.
By using the file attribute, you can tell <cfloop> to iterate over each line of a file.
This sample reads in a text file and displays each line:


<cfset myfile = expandPath("./dump.txt")>

      <cfloop index="line" file="#myfile#">
          <cfoutput>
              The current line is #line#
          </cfoutput>
      </cfloop>

This question was written by Hal Helms
It was last updated on July 1, 2008.
Found at:
      https://www.coldfusioncookbook.com/entries/How-can-I-read-a-simple-text-file-processing-each-line-of-the-file.html