Read info from DB, display it as table or List items, depending... 1

[cherphas]

Hello

I have to create a database driven web site for a company so they can update the info without having to go through HTML code. They give me the content in a Microsoft word document.

All I have to do once I create the database is copy the content in the cells and it's done. But(of course there's a "but&quot, some of the content in the document has tables and some are displayed with bullet items and some have images. For example there is a paragraph explaning something, then bellow there is a list of items with bullets or a table. Since it's all part of the same information, I have to put all this in the same cell, right?

Is there a way for Coldfusion to read the data in a cell and it would recognize bullet Items so it would create <OL> <li> tags? Or if its information in tables? Can it recognize it and generate <table> tags for that specific info? I thought of putting the actual tags in the cell itself, but that will be confusing for the user when her reads the info in the database, plus if the user wanted to add a table he would have to put html code in the cells, and, there reason for using a database is so the user doesn`t have to use HTML code.

Is there an alternative to this?

Thanks in advance!

 

[r937]

either teach the users html for tables and lists, or implement a web editor like

http://ektron.com/ewebeditpro.cfm

rudy

 

[csteinhilber]

The Ektron control Rudy suggests does a fairly good job translating MS Word docs that are cut/pasted into the control. Word tables are automatically converted to HTML tables, formatting (bolding, right justify, etc) is maintained, and most bullets are translated to HTML lists. It's rather nice.

Though, if you're really looking for a true content management system, where content is oblivious to layout and UI, you'd actually want to separate each component (graphics, bulleted lists, etc) out as it's own entity. Then provide a form field for each entity (&quot;Specify the graphic to appear in position 1...&quot. Then you'd be able to adjust the content based on different layout types (browser, WAP, WebTV) without having to replicate data.


That being said... if Ektron is out of your budget (it's cheap, but it ain't free), you could certainly just have people save their Word docs as HTML... then you could either allow them to cut and paste the HTML into form field(s), or upload the HTML file to the server where you can have ColdFusion pull it apart and put components in their proper places. Word HTML is ugly... but sometimes it beats HTML created by folks who don't know what they're doing Hope it helps,
-Carl

 

[cherphas]

&quot;Though, if you're really looking for a true content management system, where content is oblivious to layout and UI, you'd actually want to separate each component (graphics, bulleted lists, etc) out as it's own entity. Then provide a form field for each entity (&quot;Specify the graphic to appear in position 1...&quot. Then you'd be able to adjust the content based on different layout types (browser, WAP, WebTV) without having to replicate data.&quot;

Could you explain what you mean by that? Do you mean separate each entity in it's own cell? Cause that's something i'm thinking about, having a table in the access database for bullet items and a table for <table> items. Then in the main db where all the definitions are, if Cell &quot;Bullet_Items&quot; is not empty, it will go in the &quot;Bullets&quot; table. In that table, each column of a row will contain the items. Did what I say make any sense?

 

[r937]

cherphas, yes, what you say makes sense

it's risky, though, because unless there is a clear distinction between form and content, you will forever be revising the structure, either the database content or the html that goes around it

here's a good example of the kind of situation you can get into --

suppose on one web page you have a paragraph, then some bulleted items, then a paragraph, then an image

on a different page, you have a paragraph, an image, and another paragraph

on yet another page, you have a paragraph only

it's easy to store the pieces, and it's easy to create a structure that says which pieces a page needs and in what order, but you must realize that if you design it this way, you should really give control over assembly of the pieces to the users in an &quot;admin&quot; page that lets them, for example, remove a paragraph and insert an image

tricky, eh


rudy

http://rudy.ca/

 

[cherphas]

Yeah your right. I'm thinking of creating a web page where the user will update the database. This way they don't have to work with the database itself. Is that possible though to create a a form in coldfusion to change the structure of a certain block of information like you said?

 

[r937]

yes, &quot;change the structure&quot; being the operative idea

the choices are: (1) use one great big honking textarea for the entire html page and get your users to learn html, (2) design (and code, no small matter) an administrative screen that lets users cherry-pick paragraphs, lists, images, etc. (although you are still in heaps o' trouble if the user wants to bold a word partway through a paragraph), and (3) get a comprehensive CMS (content management system) product, usually involving a client-side activex or java component that users with site update privileges must install in their browser

personally, i like 3 as the best solution to the challenge

i installed ektron three or four years ago and it was pretty good then, and i'm sure there are plenty of equally competent products, but i don't keep up with them, although i know there's a large community interested in them -- see

http://www.cmswatch.com/

rudy

 

[csteinhilber]

We installed a multi-million dollar CMS earlier this year (actually late last year)... after an exhaustive needs analysis and product evaluation.

We've since pulled it all out and we're in the process of growing our own using ColdFusion (mostly).

Even though CMS' are billed as solving all the world's web problems, to date they are a complete boondoggle. Anything you get from a 3rd party vendor is either going to be so generic as to not really fit your needs, or require so much custom code (usually in some odd proprietary scripting languages or XML extensions) that you might as well have built the darn thing from the ground up anyway.

Most systems out now were not designed as web content management systems, they were mostly designed as document management systems (not the same thing, no matter what anyone says)... and the vendors quickly tweaked code here and there to be able to jump on the CMS bandwagon. Thus, while they usually handle assets relatively well, not one adequately addresses the core underpinnings of a website. And most are bloated server plug-ins or java applets that aren't worth the resource drain associated with running them day-to-day.

Okay... now that I got that out

As for my earlier statement... what Rudy is saying is completely right... unless there is a clear distinction between form and content, you will forever be revising the structure, either the database content or the html that goes around it... but that's precisely the point I was trying to make. There should be a clear distinction between form/ui and content.

And, yes, if there is that clear distinction, you can change the UI templates as often and as much as you like, and you won't have to do a darn thing to the data.

Imagine your data something like:

asset_name      asset_type    asset_parent

missionTitle    headline      page1.html
missionStmnt    text          page1.html
10points        list          page1.html
founderPic      image         page1.html
missionRef      footnote      page1.html

etc. (of course, there'd also be a value column that would hold the actual data)

Then your layout templates would do something like:

<CFQUERY name=&quot;pageContent&quot; ...>
  SELECT * 
    FROM myTable
   WHERE asset_parent = '#thisPage#'
        :
</CFQUERY>

<CFQUERY name=&quot;findTitle&quot; dbtype=&quot;query&quot;>
   SELECT *
     FROM pageContent
    WHERE asset_type = &quot;headline&quot;
</CFQUERY>


<HTML>
<HEAD>
  <TITLE><CFOUTPUT QUERY=&quot;findTitle&quot;>#asset_value#</CFOUTPUT></TITLE>
    :
</HEAD>

<BODY>

<H1><CFOUTPUT QUERY=&quot;findTitle&quot;>#asset_value#</CFOUTPUT></H1>

<CFQUERY name=&quot;findText&quot; dbtype=&quot;query&quot;>
   SELECT *
     FROM pageContent
    WHERE asset_type = &quot;text&quot;
</CFQUERY>

<CFOUTPUT QUERY=&quot;findText&quot;>
<P>#asset_value#</P>
</CFOUTPUT>

<CFQUERY name=&quot;findLists&quot; dbtype=&quot;query&quot;>
   SELECT *
     FROM pageContent
    WHERE asset_type = &quot;list&quot;
</CFQUERY>

<CFLOOP QUERY=&quot;findLists&quot;>
<UL>
<CFLOOP INDEX=&quot;whichBullet&quot; LIST=&quot;#asset_value#&quot; DELIMITERS=&quot;#Chr(10)#&quot;>
<LI>#whichBullet#</LI>
</CFLOOP>
</UL>
</CFLOOP>

   :
etc

The above wouldn't win any awards, and it's very simplified... but you can see how easily you could simply leave out from the UI template the reference that calls in the image, and it simply wouldn't show up. It would still exist in the database... and you wouldn't have to touch the structure of the table at all.

This comes in especially handy if you need to share content across multiple pages or sites, or display the content in multiple ways. With the above data, for example, your standard template might call in headline elements, text elements, list elements, image elements, and footnotes. Yet your WAP template might just call in text elements, and maybe call in your list elements as simple text, instead of a bulleted list, but leave out the images altogether. Your intranet site might display the profit_margin elements, but they'd be left off the external internet site. But all pages, sites and templates could use the same data out of the same database.

Complete separation of content from UI should be the ultimate goal. How achievable that is, or in what timeframe and context, depends greatly on your existing content, and probably countless legacy issues.

Hope it helps,
-Carl

 

[r937]

&quot;hope it helps&quot; carl?

yeah, it does

great examples, deserving of yet another star, dude


rudy

 

[cherphas]

Before I start, thank you very much for your help you guys!!

Ok...I'll have to go with Coldfusion. I'm going to explain what I have in mind applying what you guys told me. Keep in mind that I want to use one to three template pages for this. It might be long and hard to explain due to my simple terms(i'm french so...):

I would have One database with Three tables:

Table 1: Category_Name

Table 2: Content

Table 3: Content_Structure

&quot;Category_Name&quot; will contain the titles of each category and will have its own ID number. The reason is because the way the documents are structured, and I don't want to repeat the tiles to save space:

1.General Things
1.1 First Thing
1.2 Second Thing
1.3 Third Thing

2. Second Category
2.1 bla bla bla
2.2 bla bla

so in the db: ID | title
1 | General Things
2 | Secong Category

etc.

&quot;Content&quot; will have content of course, seperating each element:

Category_ID|SubCat_Number| Title |Paragraph1|Bullets
1 1.1 First GeneralThing asdf items

etc.

&quot;Content_Structure&quot; will give the order of each elements:

SubCat_Number | Element1 | Element2 | Element3
1.1 Title Paragraph1 Bullets
1.2 Title Paragraph1 Images
1.3 Title Bullets Paragraph1


******************Sql in page:********************************

if the user clicked on "1.1"

Select all from Content_structure where SubCat_Number = '1.1'

<!--1st element, checks in Content Structure-->
if Element1 = &quot;title&quot; then
    go in &quot;Content&quot; table and get Title
elseif Element1 = &quot;Paragraph&quot;
    go in &quot;Content&quot; table and get Paragraph
etc.

same for other elements.

***************************************************************
Then if the user wants to add(or update) some content, I will create a html form and the can choose what type the first Item is(Title, Paragraph, Bullets, etc. dropdown menu) with TextArea beside it. Same for second item. If they need more item, there would be a &quot;Add element&quot; button. I didn't solve how I will display <table> elements form the database, maybe seperate each table item in its own cell?

Of course lots of details are missing, but it's just an idea...maybe not a good one but it's a start.
What do you think?

 

[csteinhilber]

The problem I see is you're still associating several types of content in a linked way (I think... it's not too clear, or maybe my brain is just mush from the eggnog).

If I look at your content table:
Category_ID|SubCat_Number| Title |Paragraph1|Bullets
1 1.1 First GeneralThing asdf items

What it looks like to me is &quot;Paragraph 1&quot; can never exist independently of &quot;Bullets&quot;, and vice-versa. In other words, what if SubCat 1.2 needed to use the same Bullets, but a different Paragraph 1. You'd have to duplicate the same Bullets data in the record for SubCat 1.2. It would be better if they were completely independent assets, so you could reuse Bullets as many times as you want. Though, as I said, you would have to assess the ROI you'd get on a design like that vs. how often, if ever, you'd need to share data.

The other problem I see (and this is a little bigger, IMHO) is that for each asset type you're going out and hitting the database. This can become a pretty big hit, performance-wise, as your site starts to scale and you get more and more users/page views.

In my initial solution, you'll notice that the only query that actually hits the database is the first one. This returns a &quot;master resultset&quot;. From there you pick it apart with &quot;queries-on-queries&quot; (which are entirely within memory and therefore much faster than hitting the database).

You'd might be able to do a single query with a join, but it's quite complicated with the different asset types in different columns... you never really know what you need to SELECT. For this reason, I'd suggest that all your content is in the same column, and you have a column that designates the content type. Then you have a consistant column structure that you can SELECT.

Imagine your &quot;Content&quot; table like:
Category_ID|SubCat_Number| Title | Content | Type
1 1.1 First GeneralThing asdf paragraph
1 1.1 First GeneralThing items bullets

and your &quot;Content_Structure&quot; like:
SubCat_Number | Element | Element_Order
1.1 paragraph 1
1.1 bullets 2

then your join query can be something like:

<CFSET mySubcat = &quot;1.1&quot;>
<CFQUERY NAME=myContent&quot; ...>
    SELECT C.Content as AssetContent,C.Type as AssetType
      FROM Content C, Content_Structure S
     WHERE S.SubCat_Number = '#mySubcat#'
       AND C.SubCat_Number = '#mySubcat#'
       AND S.Element = C.Type
  ORDER BY S.Element_Order
</CFQUERY>

<!--- myContent now contains all your content, in order, from a single query --->

<CFOUTPUT query=&quot;myContent&quot;>
<CFSWITCH expression=&quot;#lcase(myContent.AssetType)#&quot;>
<CFCASE value=&quot;paragraph&quot;>
   #myContent.AssetContent#
</CFCASE>
<CFCASE value=&quot;bullets&quot;>
   <UL>
   #Replace(myContent.AssetContent,Chr(10),&quot;<LI>&quot;,&quot;ALL&quot;)#
   </UL>
</CFCASE>
     :
</CFSWITCH>

</CFOUTPUT>

Hope it helps,
-Carl

 

[cherphas]

Cool!! Thank you very much Carl and Rudy. Carl, I just got of my holidays so I didn't have the chance to try your idea yet but it seems like the best idea. I'll let you know how it goes .

Thanks again
Joël