太極者無極而生 太極者無極而生 - 2 months ago 19
PHP Question

How to divide a long article and store in the database for easy retrieval and with paging?

Suppose it is a long article (say 100,000 words), and I need to write a PHP file to display page 1, 2, or page 38 of the article, by

display.php?page=38


but the number of words for each page can change over time (for example, right now if it is 500 words per page, but next month, we can change it to 300 words per page easily). What is a good way to divide the long article and store into the database?

P.S. The design may be further complicated if we want to display 500 words but include whole paragraphs. That is, if we are showing word 480 already but the paragraph has 100 more words remaining, then show those 100 words anyway even though it exceeds the 500 words limit. (and then, the next page shouldn't show those 100 words again).

Answer

I would do it by splitting articles on chuks when saving them. The save script would split the article using whatever rules you design into it and save each chunk into a table like this:

CREATE TABLE article_chunks (
    article_id int not null,
    chunk_no int not null,
    body text
}

Then, when you load a page of an article:

$sql = "select body from article_chunks where article_id = "
    .$article_id." and chunk_no=".$page;

Whenever you want to change the logic of splitting articles into pages, you run a script thats pulls all the chunks together and re-splits them:

UPDPATE: Giving the advice I suppose your application is read-intensive more than write-intensive, meaning that articles are read more often than they are written