Linking dynamic content.

Anon

Hi
if a page's content is created by a DB query how do you link to the next page (say in an series of articles) whthout putting a link in the text you are calling from the DB?

Or am I barking(howling?) up the wrong tree?

Many thanks

Nick

kparker

Well, you have to somehow know what the next article is. Let's image the following table structure:

create table article
(
id int,
next_in_series int, // references article.id of next article
...
);

Now, when you are simply listing articles, you can ignore 'follows'. But when you're actually displaying the contents of the article, can display a link for "Next Article" if article.next_in_series is not zero.

This scheme allows at most one followup article. If you turn it around (have a column for "this article is a followup to", you could have multiples.)

Anon

Hi Kirk
Could you have a unique number assigned to article one and in the article two record have a link feild with a value of unique id+1

id (used for all other joins etc)
unique page id
section (type of article)
next page (unique page id+1)

Do you know where I might find some practicle examples of table schema/architecture for a dynamic site?

I've been at it all day and all I have are some ideas with holes the size of australia and a headache!

Much appreciated

Nick

kparker

Every article has to have a unique ID.

The two ways to link things are (1) each article has a (possibly null) link to its successor, or (2) each article has a (possibly null) link to its predecessor.

Here's some sample data using #2 schema:

ID PREDECESSOR TITLE ...
1 null This is the first article
2 1 This is the second article
3 2 This is the final article
4 null Beginning article of another series

Note that schema #2 possibly has some advantages if you want to do queries that list only articles that are the beginning of series:

  select * from articles where predecessor is null;

The main disadvantage I see with #2 is you have to do a separate query to determine if their is a followup article:

  select * from article where id = $target_id;

gets the content, and then

  select id, title from article where predecessor = $target_id order

This does have the nice feature that you can list the title of the next article (which would require a separate query even using schema #1--it's just that with #1, if you're willing to tolerate a link that just says "Next Chapter" or something, you don't need an additional query to display the link.)

knickerlas

Thanks alot Kirk
that seems just the ticket, it also solves the problem of generating a list of articles by first page only as you said.
My solution wasn't quite so eloquent.

Here's the final schema for my articles DB let me know if you spot any obvious flaws!

TABLE: head
ID_NUM: primary key
LAST_MOD: date
PREVIOUS: can be NULL
TITLE:
KEYWORDS:
DESCRIPTION:

TABLE: content
ID_NUM: primary key
HEADLINE:
SUBHEAD:
PARA1
PARA2 (paragraphs)
PARA3

TABLE: sections
ID_NUM primary key
SECTION: (example: 'stories')
SUBSECTION (example: 'stories about...')

Once again much thanks Kirk your advice has proved invaluable. It's difficult to gain experience at this without asking more experienced folks the most basic of questions somtimes!

Nick

kparker

Trouble ahead:

TABLE: content
ID_NUM: primary key
HEADLINE:
SUBHEAD:
PARA1
PARA2 (paragraphs)
PARA3

This says that no story may have more than one paragraph.

I'd move the headline and subhead to the HEAD table, and then make your content table be:

ID_NUM refers to head.id_num
SEQUENCE (id_num+sequence is primary key for this table)
TEXT

That way you can have N paragraphs per story, where N is the largest number that can be stored in the column type used for SEQUENCE.

On the other hand, MySQL is pretty good at storing long text. Are you sure you need a separate row for each paragraph? Or is "paragraph" a bit of a misnomer, and what you're really storing here is a page-size chunk (that might actually have embedded tags if the author so chooses?)

Anon

Hi Kirk,
what I mean in this table is that each para feild will HAVE some text that will be put inside tags in a template.

headline NOT NULL
sub head NOT NULL
para1 NOT NULL
para2 NULL
para3 NULL

So each record must have a paragraph but also has the option for another two.

Do yo ustill see a problem?

Cheers

Nick

kparker

Do yo ustill see a problem?

Certainly, unless you are guaranteeing for all time that no article will ever have more than three paragraphs.

But the prior question is, what is the point of putting each paragraph into a separate column? Each paragraph is basically the same 'type' of information, is it not?

knickerlas

Hi
The reason for putting the paragraphs into seperate columns was that I need the text properly formatted between tags.
Example of template:

{para1}
{para2

I'm guessing I can find a newline char and replace it with the tags?

Nick

kparker

Sure, that's convenient, but since each paragraph is the "same kind" of data, they should all be instances of the same column. That is, instead of having

  id pp1 pp2 pp3 .....

You should have

  id seq pp
  id seq pp
  id seq pp
  ...

The ID references the 'article' table where the title, author, etc is stored, and the sequence numbers keep the paragraphs in correct order. Note, the sequence numbers don't have to be adjacent, as long as they are ascending in the correct order, so you can probably just use an autoincrement for the column and not have to do any extra work to get them in the right order. Now you can accomodate articles that have one paragraph or a zillion paragraphs with equal ease.

To retrieve all the paragraphs for a story, just say:

select pp from content where id = $article_id order by seq

knickerlas

Sounds most sensible,
couple of questions (suprise) though:
How will the results be returned/accessed, in an array like pp[0],pp[1],pp[3] ?
And how will I know what article_id to give them in this table? Presumably something like $article_id=mysql_insert_id($db_link);
$qry="INSERT INTO content VALUES ('$article_id','null','a paragraph')";

Thanks

Nick

kparker

Once you've issued the query:

$result = mysql_query("select pp from content where id = $art_id order by seq");

You can retrieve the results in a loop:

while (list($pp) = mysql_fetch_row($result))
    {
    echo "$pp<>";
    }

Note the only column you're retreiving is the paragraph content itself.

As far as the insert goes, it's far safer to explicitly list the column names, and then just omit the auto-numbered column.

INSERT INTO content (art_id, content) VALUES ( '$article_id', 'a paragraph')

knickerlas

Thanks Kirk,
Think I can put it all together now!
I'll post any other related questions openly so you can take a well earned rest from my stuff!

Mucho grassy arse

Nick