Storing article text in a database

[Mark]

I have a website with an increasing amount of articles and news reports and
so I am thinking of moving away from storing each article as a seperate page
to having a single page and storing articles in a databasewhich are
retrieved using a GET parameter.

I see the advantage to me in using this approach as being making use of
MySQL's fulltext search capability and less work needed when updating the
design of the page. I'm not sure of a few things though:

1. The exact benefits of fulltext. Can someone provide a brief explanation
or a good website

2. I imagine that storing just the plain text of the article in the database
will result in a single block paragraph when retrieved into the page. Do I
need to include the HTML markup with the text in the database or is there a
different approach?

3. Will the HTML markup in the article interfere with the ability to search
for words i.e. if I have a word "recipes" in an article that is surrounded
tags like <b>recipes</b>, will a search on this article pick out the word
from in between the tags?

Thanks.
Mark

[Geoff Berrow]

Re: Storing article text in a database

I noticed that Message-ID: <kJfDc.47016$ly 2.5926@doctor.c ableinet.net>
from Mark contained the following:
[color=blue]
>1. The exact benefits of fulltext. Can someone provide a brief explanation
>or a good website[/color]

Not sure what you mean by 'fulltext'?[color=blue]
>
>2. I imagine that storing just the plain text of the article in the database
>will result in a single block paragraph when retrieved into the page. Do I
>need to include the HTML markup with the text in the database or is there a
>different approach?[/color]

See http://uk.php.net/nl2br[color=blue]
>
>3. Will the HTML markup in the article interfere with the ability to search
>for words i.e. if I have a word "recipes" in an article that is surrounded
>tags like <b>recipes</b>, will a search on this article pick out the word
>from in between the tags?[/color]

If you include wildcards you can search for words or parts of words
e.g. SELECT * FROM table WHERE fieldname LIKE%searchterm %;
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[CJ Llewellyn]

Re: Storing article text in a database

"Geoff Berrow" <blthecat@ckdog .co.uk> wrote in message
news:hv8rd01ieb gmr906935h2mloj algufmigj@4ax.c om...[color=blue]
> I noticed that Message-ID: <kJfDc.47016$ly 2.5926@doctor.c ableinet.net>
> from Mark contained the following:
>[color=green]
> >1. The exact benefits of fulltext. Can someone provide a brief[/color][/color]
explanation[color=blue][color=green]
> >or a good website[/color]
>
> Not sure what you mean by 'fulltext'?[/color]

http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html

Seaching for fish will match fishing, fished, fishes and is therefore more
"natural" and powerful.
[color=blue][color=green]
> >2. I imagine that storing just the plain text of the article in the[/color][/color]
database[color=blue][color=green]
> >will result in a single block paragraph when retrieved into the page. Do[/color][/color]
I[color=blue][color=green]
> >need to include the HTML markup with the text in the database or is there[/color][/color]
a[color=blue][color=green]
> >different approach?[/color][/color]

I suggest that you follow the bb code scheme from various message board
applications like phpbb, vbulletin and IPB.

The idea is that instead of html markup you have your own quasi-markup and
you can then parse the text more efficiently to ensure the user is not upto
naughty shanigans like inserting links to porn sites.

so <b> becomes [b] etc
[color=blue][color=green]
> >3. Will the HTML markup in the article interfere with the ability to[/color][/color]
search[color=blue][color=green]
> >for words i.e. if I have a word "recipes" in an article that is[/color][/color]
surrounded[color=blue][color=green]
> >tags like <b>recipes</b>, will a search on this article pick out the word
> >from in between the tags?[/color][/color]

Yes, but see about on why you shouldn't allow html mark in your data.

[Mark]

Re: Storing article text in a database


"CJ Llewellyn" <satest@tmslife line.com> wrote in message
news:cbkiu4$65i $1@slavica.ukpo st.com...[color=blue]
> "Geoff Berrow" <blthecat@ckdog .co.uk> wrote in message
> news:hv8rd01ieb gmr906935h2mloj algufmigj@4ax.c om...[color=green]
> > I noticed that Message-ID: <kJfDc.47016$ly 2.5926@doctor.c ableinet.net>
> > from Mark contained the following:
> >[color=darkred]
> > >1. The exact benefits of fulltext. Can someone provide a brief[/color][/color]
> explanation[color=green][color=darkred]
> > >or a good website[/color]
> >
> > Not sure what you mean by 'fulltext'?[/color]
>
> http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html
>
> Seaching for fish will match fishing, fished, fishes and is therefore more
> "natural" and powerful.[/color]

Thanks. I've read this - I was also wondering if there were any negative
aspects of using fulltext searching or any benefits in using a different
approach. Someone suggested just using a third party search engine but I'd
rather keep everything in-house - it just looks more professional.
[color=blue]
>[color=green][color=darkred]
> > >2. I imagine that storing just the plain text of the article in the[/color][/color]
> database[color=green][color=darkred]
> > >will result in a single block paragraph when retrieved into the page.[/color][/color][/color]
Do[color=blue]
> I[color=green][color=darkred]
> > >need to include the HTML markup with the text in the database or is[/color][/color][/color]
there[color=blue]
> a[color=green][color=darkred]
> > >different approach?[/color][/color]
>
> I suggest that you follow the bb code scheme from various message board
> applications like phpbb, vbulletin and IPB.
>[/color]

I'm not sure why you suggest this as I'm not building a bbs.
[color=blue]
> The idea is that instead of html markup you have your own quasi-markup and
> you can then parse the text more efficiently to ensure the user is not[/color]
upto[color=blue]
> naughty shanigans like inserting links to porn sites.
>[/color]

Can you elaborate a but more on why you say this? It sounds very bad and
porn would not be good for my site but I'm not sure how this is relevant to
my site which contains mostly articles written by a few people and uploaded
by me - I have no users posting on my site.
[color=blue]
> so <b> becomes [b] etc
>[color=green][color=darkred]
> > >3. Will the HTML markup in the article interfere with the ability to[/color][/color]
> search[color=green][color=darkred]
> > >for words i.e. if I have a word "recipes" in an article that is[/color][/color]
> surrounded[color=green][color=darkred]
> > >tags like <b>recipes</b>, will a search on this article pick out the[/color][/color][/color]
word[color=blue][color=green][color=darkred]
> > >from in between the tags?[/color][/color]
>
> Yes, but see about on why you shouldn't allow html mark in your data.
>[/color]

See where? Is there something wrong with using html markup in my data? I'm
just only using markup to separate paragraphs and for image links. I was
wondering if this was the only way to do it if you wanted to store article
data in a database that you don't want to come out as a big block of text
(or is there a way of adding markup after retrieving the text data?). The
downside I see is just editing the text in phpAdmin instead of Dreamweaver
if I want to make an update to articles. Just want to make sure I am abreast
of all potential issues before changing the way I do things. Knowledge &
suggestions from experienced php developers is helpful.

Thanks
Mark