split database result over multiple pages

[ptbsG_Man]

Hello im am tring to write a scrip that i can use to create articles and be able to specify a new page using some kind of either char. or a string like <pbreak> and have it ll go into the DB as one entry this way editing can be done easley.

Thanks Don

[ptbsG_Man]

bump

any one have an idea??

Thanks

[Drakla]

You'll have to explain the problem more, I don't think people want to waste time coming up with a solution that might not be what you're after.

What do you exactly mean by 'new page'?

[ptbsG_Man]

Well i want it so that if i ws typing an article i can do this

this is a article
this is a article
this is a article
this is a article
this is a article
<pgbreak>
this is a article
this is a article
this is a article
this is a article
this is a article
<pgbreak>

and have that all inserted into the DB. Then when the page is loaded have it split the article on <pgbreak> and at the bottom have a

<prev 1 2 3 next>

at the bottom and have it only display one page at a time

Thanks for replying

[snodart]

I have been trying to figure out this same problem. I did get it to "sort of" work (PHP + MySQL), but in a different way. You can see it by clicking on one of the test articles/tutorials here. I racked my brian trying to figure out a way similar to the way that you mention since it would seem to allow for better database design, but I came up with nothing. I am still pretty new to PHP and MqSQL so take all of this with a grain of salt. This is what I came up with thus far...

I have a form in the admin area of my site. Included in the form are 8 HTML textareas. Each textarea represents a page of the article/tutorial.

when the data is sent to the database, the data from the first textarea is placed in the articles table in a column named "thearticle", the second textarea is also placed in the articles table but in its own column: "pg2", third textarea goes to "pg3" column, forth textarea goes to "pg4" column, and so on. Don't let the column name "thearticle" confuse you. I named it before adding the other pages. It should probably be renamed to "pg1". Just haven't bothered yet. At any rate, the pages are broken up before going into the database.

This set up has some issues of couse. Remember, I am new and tyring to solve this problem, so it is by no means the finalized/correct way... just some playing around with ideas.

One issue is that if an article/tutorial has only one page (which would go into "thearticle" column), then pg2, pg3, pg4, pg5, pg6, pg7, and pg8 columns would be empty in the table. Probably not the best database design.

I am currently working on a layout for the database to make it 2NF (second normal form) compliant. I'm not sure if making this database 2NF compliant is even possible or if it would make it all okay, but I'm tying to figure that out. Google, learn. Google, learn. Google, learn... its an endless process.

Another issue is that the articles/tutorials would be limited to 8 pages. I suppose there could be more, but agian I'm not sure about a clean database design using this method.

The PHP file that I use to display the article and the "prev 1 2 3 next" links is brought to a given page via PHP's "include" function. With "if" statements and the such, it is coded so that an article with 5 total pages will have the links displayed as "prev 1 2 3 4 5 next"... an article with 8 pages will display the links as "prev 1 2 3 4 5 6 7 8 next". You get the idea.
Here is the code of the included file:

//create connection
//require from outside public_html for security $conn is connect variable.
require_once('../PATH/FILE.php');

//select database
$db = mysql_select_db ("DATABASE"); 

//create sql statement
$sql = "SELECT * FROM articles WHERE id='$id'";

//execute sql query and get result

$sql_result = mysql_query($sql) or die(mysql_error());
if (!$sql_result) {
	echo '<p>Sorry, there has been a system error in retrieving the data. Please contact us at <a href="mailto:[e-mail]">[e-mail]</a> if this persists.</p>';
	} else {

$content = mysql_fetch_array($sql_result);

//converts MySql date to format:Month XXrd, YEAR
$date = date("F jS, Y", strtotime($content[date]));

echo "<p><span class=\"articleHeader\">$content[title]</span><br />\n";
echo "<span class=\"xofx\">Added on: $date</span></p>\n";

//if there is more than one page (if page 2 exists) build prev, next, and page links
if ($content[pg2]) {

	//set default links to the $pageX variables for the links
	$page1 = "<a href=\"$letter.php?id=$content[id]&amp;row=pg1\">1</a>\n";
	$page2 = "<a href=\"$letter.php?id=$content[id]&amp;row=pg2\">2</a>\n";
	$page3 = "<a href=\"$letter.php?id=$content[id]&amp;row=pg3\">3</a>\n";
	$page4 = "<a href=\"$letter.php?id=$content[id]&amp;row=pg4\">4</a>\n";
	$page5 = "<a href=\"$letter.php?id=$content[id]&amp;row=pg5\">5</a>\n";
	$page6 = "<a href=\"$letter.php?id=$content[id]&amp;row=pg6\">6</a>\n";
	$page7 = "<a href=\"$letter.php?id=$content[id]&amp;row=pg7\">7</a>\n";
	$page8 = "<a href=\"$letter.php?id=$content[id]&amp;row=pg8\">8</a>\n";

	//if clicked page X echo page X and set prev and next and dead link for page X
	if ($row == 'pg1') {
		echo "$content[thearticle]\n";
		$prev = "<span class=\"PNdead\"><img id=\"arowLeft\" src=\"images/arrowLeft.gif\" alt=\"\" />Prev</span>\n";
		$next = "<a href=\"$letter.php?id=$content[id]&amp;row=pg2\">Next<img id=\"arrowRight\" src=\"images/arrowRight.gif\" alt=\"\" /></a>\n";
		$page1 = "<span class=\"PNdead\">1</span>\n";

	} elseif ($row == 'pg2') {
			//if there is no next page make next link dead or esle make it live
			if (!$content[pg3]) {
				$next = "<span class=\"PNdead\">Next<img id=\"arrowRight\" src=\"images/arrowRight.gif\" alt=\"\" /></span>\n";
			} else {
				$next = "<a href=\"$letter.php?id=$content[id]&amp;row=pg3\">Next<img id=\"arrowRight\" src=\"images/arrowRight.gif\" alt=\"\" /></a>\n";
			}
			echo "$content[pg2]";
			$prev = "<a href=\"$letter.php?id=$content[id]&amp;row=pg1\"><img id=\"arowLeft\" src=\"images/arrowLeft.gif\" alt=\"\" />Prev</a>\n";
			$page2 = "<span class=\"PNdead\">2</span>\n";

	} elseif ($row == 'pg3') {
			//if there is no next page make next link dead or esle make it live
			if (!$content[pg4]) {
				$next = "<span class=\"PNdead\">Next<img id=\"arrowRight\" src=\"images/arrowRight.gif\" alt=\"\" /></span>\n";
			} else {
				$next = "<a href=\"$letter.php?id=$content[id]&amp;row=pg4\">Next<img id=\"arrowRight\" src=\"images/arrowRight.gif\" alt=\"\" /></a>\n";
			}
			echo "$content[pg3]";
			$prev = "<a href=\"$letter.php?id=$content[id]&amp;row=pg2\"><img id=\"arowLeft\" src=\"images/arrowLeft.gif\" alt=\"\" />Prev</a>\n";
			$page3 = "<span class=\"PNdead\">3</span>\n";

	} elseif ($row == 'pg4') {
			//if there is no next page make next link dead or esle make it live
			if (!$content[pg5]) {
				$next = "<span class=\"PNdead\">Next<img id=\"arrowRight\" src=\"images/arrowRight.gif\" alt=\"\" /></span>\n";
			} else {
				$next = "<a href=\"$letter.php?id=$content[id]&amp;row=pg5\">Next<img id=\"arrowRight\" src=\"images/arrowRight.gif\" alt=\"\" /></a>\n";
			}
			echo "$content[pg4]";
			$prev = "<a href=\"$letter.php?id=$content[id]&amp;row=pg3\"><img id=\"arowLeft\" src=\"images/arrowLeft.gif\" alt=\"\" />Prev</a>\n";
			$page4 = "<span class=\"PNdead\">4</span>\n";

	} elseif ($row == 'pg5') {
			//if there is no next page make next link dead or esle make it live
			if (!$content[pg6]) {
				$next = "<span class=\"PNdead\">Next<img id=\"arrowRight\" src=\"images/arrowRight.gif\" alt=\"\" /></span>\n";
			} else {
				$next = "<a href=\"$letter.php?id=$content[id]&amp;row=pg6\">Next<img id=\"arrowRight\" src=\"images/arrowRight.gif\" alt=\"\" /></a>\n";
			}
			echo "$content[pg5]";
			$prev = "<a href=\"$letter.php?id=$content[id]&amp;row=pg4\"><img id=\"arowLeft\" src=\"images/arrowLeft.gif\" alt=\"\" />Prev</a>\n";
			$page5 = "<span class=\"PNdead\">5</span>\n";

	} elseif ($row == 'pg6') {
			//if there is no next page make next link dead or esle make it live
			if (!$content[pg7]) {
				$next = "<span class=\"PNdead\">Next<img id=\"arrowRight\" src=\"images/arrowRight.gif\" alt=\"\" /></span>\n";
			} else {
				$next = "<a href=\"$letter.php?id=$content[id]&amp;row=pg7\">Next<img id=\"arrowRight\" src=\"images/arrowRight.gif\" alt=\"\" /></a>\n";
			}
			echo "$content[pg6]";
			$prev = "<a href=\"$letter.php?id=$content[id]&amp;row=pg5\"><img id=\"arowLeft\" src=\"images/arrowLeft.gif\" alt=\"\" />Prev</a>\n";
			$page6 = "<span class=\"PNdead\">6</span>\n";

	} elseif ($row == 'pg7') {
			//if there is no next page make next link dead or esle make it live
			if (!$content[pg8]) {
				$next = "<span class=\"PNdead\">Next<img id=\"arrowRight\" src=\"images/arrowRight.gif\" alt=\"\" /></span>\n";
			} else {
				$next = "<a href=\"$letter.php?id=$content[id]&amp;row=pg8\">Next<img id=\"arrowRight\" src=\"images/arrowRight.gif\" alt=\"\" /></a>\n";
			}
			echo "$content[pg7]";
			$prev = "<a href=\"$letter.php?id=$content[id]&amp;row=pg6\"><img id=\"arowLeft\" src=\"images/arrowLeft.gif\" alt=\"\" />Prev</a>\n";
			$page7 = "<span class=\"PNdead\">7</span>\n";

	} elseif ($row == 'pg8') {
			echo "$content[pg8]";
			$prev = "<a href=\"$letter.php?id=$content[id]&amp;row=pg7\"><img id=\"arowLeft\" src=\"images/arrowLeft.gif\" alt=\"\" />Prev</a>\n";
			$next = "<span class=\"PNdead\">Next<img id=\"arrowRight\" src=\"images/arrowRight.gif\" alt=\"\" /></span>\n";
			$page8 = "<span class=\"PNdead\">8</span>\n";
	}

	echo "<p id=\"page\">\n";
	echo "<span id=\"numbers\">\n";
	echo $prev;
	echo $page1;
	echo $page2;
	//if there is a page X then echo the link
	if ($content[pg3]) {
	echo $page3;
	}
	if ($content[pg4]) {
	echo $page4;
	}
	if ($content[pg5]) {
	echo $page5;
	}
	if ($content[pg6]) {
	echo $page6;
	}
	if ($content[pg7]) {
	echo $page7;
	}
	if ($content[pg8]) {
	echo $page8;
	}
	echo $next;
	echo "</span>\n";
	echo "</p>\n";
} else {
	echo "$content[thearticle]\n";
}
}

mysql_close();

The $letter variable is not set in the above code, but in the page that includes the above php code (via PHP "include" function).

Hope this helps your venture at least a small bit Don.

I would love to hear other solutions or how to make the database 2NF compliant. Thanks!

[snodart]

To clear up possible confusion caused by the code posted above, here is the PHP code that links the user to the full article. This is the code that displays the article title and tagline:

//create connection
//require from outside public_html for security $conn is connect variable.
require_once('../PATH/FILE.php');

//select database
$db = mysql_select_db ("DATABASE"); 

//create sql statement
$sql = "SELECT * FROM articles WHERE section = '$section' ORDER BY date DESC";

//execute sql query and get result
$sql_result = mysql_query($sql) or die(mysql_error());
if (!$sql_result) {
	echo '<p>Sorry, there has been a system error in retrieving the data. Please contact us at <a href="mailto:[e-mail]">[e-mail]</a> if this persists.</p>';
	} else {

//print the catagory/section header <h3>XXXXX - Articles</h3> variable from X_articles.php.	
echo $h3_section;

for($n=0;$n<mysql_numrows($sql_result);$n++) {

	$content=mysql_fetch_array($sql_result);

	//converts MySql date to format:Month XXrd, YEAR
	$date = date("F jS, Y", strtotime($content[date]));

	echo "<p><a href=\"$letter.php?id=$content[id]&amp;row=pg1\"><span 				class=\"articleHeader\">$content[title]</span></a><br />\n";
	echo "<span class=\"xofx\">Added on: $date</span><br />\n";
	echo "$content[tagline] <a href=\"$letter.php?id=$content[id]&amp;row=pg1\"><br /> &rarr; more</a></p>\n";
}
}


mysql_close ($conn);

[acrabadabra]

I am not completely sure or you are talking about one article that just have to be seperated into different pages....

But what you could do:

$article  = "
page 1 page 1page 1page 1page 1
page 1 page 1page 1page 1page 1
<pgbrake>
page 2 page 2 page 2 page 2 page 2 
page 2 page 2 page 2 page 2 page 2 
";

// Make array (count($pieces)) will get you the amount of pages)
$pieces = explode("<pgbrake>", $article);

// Page 1 (Or loop through the array
echo nl2br($pieces[0]);

Or, maybe even better, explode (split) the article by amount of lines. In this way you will never have to worry about where to split the article and to remove the <pgbrake> if you need the article somewhere else. Same principle, just easier.

Good luck! (Don't forget to close the thread when this is what you needed)

[snodart]

I am going to try and use this instead of the mess that I posted above. I didn't even consider making it an array. I know you posted this for the original poster, but thanks from me. Don, if you are interested, I will post how it works out. It would be great if you did the same (if you try this method).
Thanks again.

[ptbsG_Man]

yes the array would be easy only thing that would make it easyer would be start the array at 1 and then u could just pop the requested page from the url in there. ill have to play with it after work

[randallc]

I am also looking to setup a next page system for one of my sites as well. If you can keep me posted on the progress I would be greatful.

Thx
Randall

[SunnySideUp]

I am working on a similar project with my guestbook, which shall display only 10 entries per page, otherwise split into several pages.

I intend to perform a counting on the database entries. Something like: entries in database / 10 = result. result is number of pages. the result is split into an array and for each value of the array, a hyperlink with the value is created.

In case my intend does not work out as I would like to, I would be grateful for being informed on your solution as well.

Greetings from Germany

SunnySideUp

[snodart]

While searching for how to solve this problem I came across some tutorials and posts that relate more to Randall and SunnySideUp's problem. I don't think that the array solution would be suitable in these instances. The differece being that I (and I think Donn also) would use the array to break up a single field of informaion from the database (eg: an article), whereas Randall and SunnySideUp are attempting to break up multiple fields from the database over a given number of pages. The commanality among all of these would be creating the links for next and prev. At any rate, these links might be helpfull for Randall and SunnySideUp's problem:

A long thread of posts related to the problem

a brief post

a short turorial (shows code in action)

longer tutorial (I didn't read it all, but looks good)

Also, do a Google search for "PHP pagination" and you should find more.

I am rebuilding my database right now to get rid of the mess that I had. I can use a more simple database design now using an array to break up my article.

I will post how it works out.

[ptbsG_Man]

well i wrote a real simple no error checking workinging test script

<?
$test_page = ($_REQUEST['page'] != "" ? $_REQUEST['page'] : 1);
$article  = " 
page 1 page 1 page 1 page 1 page 1 
page 1 page 1 page 1 page 1 page 1 
<pgbrake> 
page 2 page 2 page 2 page 2 page 2 
page 2 page 2 page 2 page 2 page 2
<pgbrake>
page 3 page 3 page 3 page 3 page 3 
page 3 page 3 page 3 page 3 page 3 
<pgbrake> 
page 4 page 4 page 4 page 4 page 4 
page 4 page 4 page 4 page 4 page 4
<pgbrake>
page 5 page 5 page 5 page 5 page 5 
page 5 page 5 page 5 page 5 page 5 
<pgbrake> 
page 6 page 6 page 6 page 6 page 6 
page 6 page 6 page 6 page 6 page 6
"; 
$pieces = explode("<pgbrake>", $article);
$count = count($pieces);
$page = $test_page - 1;
echo $pieces[$page];
echo "<br />";
if ($test_page != 1) {
	echo sprintf("<a href=\"%s?page=%s\"><- PREV</a>", $_SERVER['PHP_SELF'], $test_page-1);
} else {
	echo "<- PREV";
}

$i = 1;
while ($i <= $count) {
	if ($i == $test_page) {
		echo "&nbsp;<b>$i</b>&nbsp;";
	} else  {
		echo "&nbsp;<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a>&nbsp;";
	}
	$i++;
}
if ($count > $test_page) {
	echo sprintf("<a href=\"%s?page=%s\">NEXT -></a>",$_SERVER['PHP_SELF'], $test_page+1);
} else {
	echo "NEXT ->";
}
?>

please because this code works, tell me what i can do to secure it. All you need to do is change the hardcoded $article into your DB return and it should work

Thanks Don

[snodart]

I'm still new to PHP, but this looks pretty good to me. I'm not sure what would need to be done to "secure it". By this did you mean error checking or database security? At any rate, I will play around with it this weekend and let you know what I come up with on this side.
Thanks for taking the time to post the code.

[ptbsG_Man]

i added some error checking and changed var names to non dev also commented it

<?
$user_page = ($_REQUEST['page'] != "" ? $_REQUEST['page'] : 1);
$article  = " 
page 1 page 1 page 1 page 1 page 1 
page 1 page 1 page 1 page 1 page 1 
<pgbrake> 
page 2 page 2 page 2 page 2 page 2 
page 2 page 2 page 2 page 2 page 2
<pgbrake>
page 3 page 3 page 3 page 3 page 3 
page 3 page 3 page 3 page 3 page 3 
<pgbrake> 
page 4 page 4 page 4 page 4 page 4 
page 4 page 4 page 4 page 4 page 4
<pgbrake>
page 5 page 5 page 5 page 5 page 5 
page 5 page 5 page 5 page 5 page 5 
<pgbrake> 
page 6 page 6 page 6 page 6 page 6 
page 6 page 6 page 6 page 6 page 6
"; 
$article_sections = explode("<pgbrake>", $article); //create array filled with the sections of article
$article_pages = count($article_sections); // the amount of 
$array_key = $user_page - 1; //match up the page number with array number
if ($user_page > $article_pages || $user_page < 1) { //check to see if the page requested is outside the page range
	die("Sorry, that is an invalid page number. Please try again, or goto <a href=\"".$_SERVER['PHP_SELF']."?page=1\">Page 1</a>");
}
echo $article_sections[$array_key]; // display the pages data 
echo "<br />"; // should not need to tell you
if ($user_page != 1) {
	echo sprintf("<a href=\"%s?page=%s\"><- PREV</a>", $_SERVER['PHP_SELF'], $user_page-1); //if the page requested is greater that 1 there should be pages behind it unless you dont know how to count 
} else {
	echo "<- PREV"; //this is page 1 and cant go back any farther
}

$i = 1; //$i=1 cool huh
while ($i <= $article_pages) { // the while loop to display the pages by number 1 2 3 4 .....
	if ($i == $user_page) {
		echo "&nbsp;<b>$i</b>&nbsp;";// most will bee this one
	} else  {
		echo "&nbsp;<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a>&nbsp;"; //this is the page ur on
	}
	$i++;//$i++ also cool huh
}
if ($article_pages > $user_page) { //check if the current page is less than the total pages
	echo sprintf("<a href=\"%s?page=%s\">NEXT -></a>",$_SERVER['PHP_SELF'], $user_page+1); // if it is link to next page
} else {
	echo "NEXT ->"; // if not donyt link 
}
?>

any other changes needed ??

Thanks
Don