dougv.com | The Web home of Doug Vanderweide » Blog Archive » Paging A Single, Long MySQL Text Field Via AJAX / PHP

Paging A Single, Long MySQL Text Field Via AJAX / PHP

April 10, 2007, 11:17 PM

Recently asked on Yahoo! Answers:

PHP Pagination, not a list?

Currently I have a table that holds a text field with a lot of text in it. What is a good way to paginate the result of that field. Since I’m not pulling a list, I figure a character count may be the way to go. I am considering switching to where the news is entered in paragraphs so it can at least be broken at the correct spots easily, but I am still not sure what is the best way to find the spot. I have a page that uses absolute positioning, so the text on the page has to end before a certain point. Any suggestions?

This is one of the few questions I’ve seen on Yahoo! Answers where the person clearly demonstrates that he not only understands the problem at hand, but has the technical ability to understand any advice and instruction he might get.

Which is why, even though I knew it would be some work to answer this question — I made two aborted attempts previously, stymied both times by a failure to think the problem through the whole way — I decided to go ahead, put on my thinking cap and get this done.

The problem this questioner has is a common one: He’s got a database field that contains a ton of text for almost every record, and designing around it is a real bear. What he wants is to be able to fit his text on the page, then let you page through that long text field — not each record in the database, but each block of text in that field.

He could, in theory, paginate both the text field and the records themselves using PHP alone. But this is 2007, and posting back is so last decade.

We’ll go ahead and use PHP to retrieve each item in the database — but we’re going to use AJAX to paginate the long text field.

As always, I will have a working demo and downloadable source code at the end of this discussion.

Step 1: Create The MySQL Table

We need to have some data with some ludicrously long text in a TEXT field. So, we hop on over to http://www.lipsum.com and ask the Lorem Ipsum generator to cough up several long passages for us (say, 15-22 paragraphs each). But we also want a real short one, and some average-length passages, to test our code.

Our MySQL table will have five fields: A primary key / autonumber INT column; a VARCHAR column to hold a headline; a TEXT column to hold some static text; an INT column to hold the Unix timestamp of our item; and a TEXT column to hold our really long text passages.

CREATE TABLE `ajax_paged_text` (
  `item_id` int(6) UNSIGNED NOT NULL AUTO_INCREMENT,
  `item_title` varchar(150) NOT NULL,
  `item_subtitle` text NOT NULL,
  `item_date` int(10) NOT NULL,
  `item_text` text NOT NULL,
  PRIMARY KEY  (`item_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

I’ve included the needed SQL code to replicate the table used in the demo, complete with four test records, in the downloadable code. I’m not pasting the INSERT SQL here because you’d be scrolling for hours to get past it.

Step 2: Create A Database Connection Include Script

I’m creating an include file to connect to the MySQL database in PHP. I do that because I need to connect to the database both from the main display page and an AJAX helper page that will do the field paging, so it makes sense to put all my connection info in one file, then include it in all the PHP files that need to connect to the database.

If the connection info ever changes, I simply make my changes in the include file, and that populates to all the rest of my pages.

$host = "localhost";
$user = "db_user_name";
$pass = "db_user_password";
$db = "database_name";
 
$link = mysql_connect($host, $user, $pass) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());

Step 3: Create The Index.php Page

With our data ready to go and PHP primed to connect to our database, we need to make a page that will grab the static information from the database, and include a DIV where we will display the paged field.

We’ll navigate through the records themselves on this main page; so, we’ll determine which record to display from a querystring variable on this page.

To begin our page, we include our connection script, then see if we have a properly formatted page number in the querystring. If we don’t have a good number, we set the page number to be 1.

require_once("connect.inc.php");
 
if(!ereg('^[0-9]{1,6}$', $_GET['p'])) {
	$page = 1;
}
else {
	$page = $_GET['p'];
}

Next, we go ahead and get all the records from our database. Notice that we don’t bother getting the long text field; we’ll use AJAX for that.

If the page number we got earlier is larger than the total number of records, we set the page number to be the same as the total number of records; if the page number is 0, we set it to 1.

Then, we create a new variable, called $pointer, which is 1 less than the number of pages; that, in turn, is used by mysql_data_seek() to move our results set’s cursor to the record corresponding to the current page. Finally, we load the record to which we just moved into an associative array.

$sql = "SELECT item_id, item_title, item_subtitle, item_date FROM ajax_paged_text";
$rs = mysql_query($sql) or die(mysql_error());
$count = mysql_num_rows($rs);
 
if($count == 0) {
	die("No such article in database.");
}
else {
	if($page < 1) {
		$page = 1;
	}
	else if($page > $count) {
		$page = $count;
	}
 
	$pointer = $page - 1;
	mysql_data_seek($rs, $pointer);
	$row = mysql_fetch_array($rs);

In the body of the page itself, we have three DIVs: one (mainDiv) contains the other two, and the other two lay out, side-by-side, for display purposes.

In the left-hand DIV, named introDiv, we’ll echo out the item_title, the formatted item_date and the item_subhead, as well as navigation links for all the records.

We leave the right-hand DIV, named pagedDiv, blank; we’ll use AJAX to put copy into it.

<div id="mainDiv">
	<div id="introDiv">
		<h2><? echo $row['item_title']; ?></h2>
		<p>Posted <? echo date('F j, Y g:i:s a', $row['item_date']); ?></p>
		<p><? echo nl2br($row['item_subtitle']); ?></p>
		<div lang="small">
			<?
			echo "<p>This is record $page of $count records.</p>\n";
 
			if($page != 1) {
				$prev = $page - 1;
				echo "<a href=\"index.php?p=$prev\"><-- Previous Record</a> | ";
			}
 
			for($i = 1; $i <= $count; $i++) {
				if($i != $page) {
					echo "<a href=\"index.php?p=$i\">$i</a>";
				}
				else {
					echo $i;
				}
				if($i < $count) {
					echo " | ";
				}	
			}
 
			if($page < $count) {
				$next = $page + 1;
				echo " | <a href=\"index.php?p=$next\">Next Record --></a>";
			}
			?>
		</div>
	</div>
	<div id="pagedText"></div>
</div>

Step 4: Add The AJAX JavaScript to Index.php

Now, we’re ready for some AJAX. First, we add to the BODY tag an onload event, to get the first page of our long text field when the records are changed:

<body onload="getPagedText(1)";>

Next, we declare a JavaScript function named getPagedText. It will take, as an argument, the page number we want to display.

That script will first declare some variables: an HTTP Request object, for making a call to our soon-to-be-made helper page; the record ID of the current record, which we get from PHP; and a URL with query string, which we construct from our record ID and page number variables.

function getPagedText(pagenum) {
	var httpRequest;
	var rid = <?php echo $row['item_id']; ?>;
	var url = "getpagedtext.php?r=" + rid + "&amp;p=" + pagenum;

We go ahead and create a standard HTTP request object, then assign a function named readyStateChanged() to handle ready state changes in the HTTP object.

	if (window.XMLHttpRequest) { 
		httpRequest = new XMLHttpRequest();
	} 
	else if (window.ActiveXObject) {
		try {
			httpRequest = new ActiveXObject("Msxml2.XMLHTTP");
		} 
		catch (e) {
			try {
				httpRequest = new ActiveXObject("Microsoft.XMLHTTP");
			} 
			catch (e) {}
		}
	}
 
	if (!httpRequest) {
		alert('Cannot create an XMLHTTP instance');
		return false;
	}
 
	httpRequest.onreadystatechange = function() { 
			readyStateChanged(httpRequest); 
		};
 
	httpRequest.open('GET', url, true);
	httpRequest.send(null);
}

The readyStateChanged function waits for our helper page to reply. Once it does, we go ahead and assign the text it returns to the innerHTML property of the pagedText DIV. (This will make much more sense to you when we actually examine the helper page, which is coming up next.)

function readyStateChanged(httpRequest) {
	if (httpRequest.readyState == 4) {
		if (httpRequest.status == 200) {
			var theDiv = document.getElementById('pagedText');
			theDiv.innerHTML = httpRequest.responseText;
		} 
		else {
			alert('Error requesting page text from server');
		}
	}
}

Step 5: Make The Getpagedtext.php AJAX Helper Page

Our index.php page and the JavaScript on it have made a request to an as-yet-unmade page to send back a chunk of text to display. This helper page figures out what to send and sends it.

First, we set a basic size for each text block, in the form of a character count. I’m using 1,000 characters here, but you can use any number you like.

Important notes: $SIZE is the maximum number of characters that can be displayed in the index.php page’s pagedText DIV. The actual number of characters the helper page returns may be far less. Generally speaking, the shorter your paragraphs, the more likely the helper page will come close to filling your DIV at a given $SIZE.

Also, no paragraph in your long text field can have more characters than whatever number you declare $SIZE to be.

That is, there must be a newline (\n) character at most every $SIZE characters. For example, if $SIZE is 1,000, then no paragraph in your long text field can have more than 1,000 characters before a newline appears; if $SIZE is 50, then no paragraph can have more than 50 characters before the next newline.

Keep those things in mind. Now, back to the code.

We next check that we have good record ID and page number values in the query string the index.php page sent us. If not, we kill the page outright.

If so, we then open our database connection and see if we can get the requested long text field, based on the record ID. If we can’t, the script dies.

$SIZE = 1000;
 
if(!ereg('^[0-9]{1,6}$', $_GET['r']) || !ereg('^[0-9]{1,2}$', $_GET['p'])) {
	die("Bad parameters passed to page");
}
 
require_once('connect.inc.php');
 
$sql = "SELECT item_text FROM ajax_paged_text WHERE item_id = $_GET[r]";
$rs = mysql_query($sql) or die("Cannot retrieve requested record");
if(mysql_num_rows($rs) == 0) {
	die("No such article in database.");
}

If we can get the record, we first set the record to an associative array. Next, we set some variables to be the values of other variables that we normally can’t change, such as $_GET and results set arrays (technically, we could change these in code, but let’s not digress).

else {
	$row = mysql_fetch_array($rs);
	$page = $_GET['p'];
	$body = $row['item_text'];
	$chars = strlen($body);

What we’re going to do is break this long text field down into roughly equal parts. We do that by dividing the number of characters in the field ($chars) by the page size we declared earlier ($SIZE). We also make sure that the current page variable isn’t lower than 1 or greater than the number of pages we need, and set the $page variable accordingly if it is out of range.

	$count = ceil($chars / $SIZE);
 
	if($page < 1) {
		$page = 1;
	}
	if($page > $count) {
		$page = $count;
	}

We will use substr() to break the large field down into the proper page excerpts. To do that, we need a rough approximation of where the substr() cursor should start; we come up with that by multiplying the size of the page by the current page number, minus 1.

Then, we use a simple for loop to go backward from that point, looking for the first newline (\n) character in our long text field. When we find it, we know we’re at the paragraph where we last left off, so we pick up the text from that starting position.

Finally, we set the preliminary end point for our excerpt (page) to be $SIZE characters more than the current value of $start.

	if($start > 0) {
		for($i = $start; $i >= 0; $i--) {
			$temp = substr($body, $i, 1);
			if($temp == "\n") {
				$start = $i + 1;
				break;
			}
		}
	}
 
	$end = $start + $SIZE;

For the correct end point of our substring, we repeat the same process as we did with $start; we look backward until we find a newline, and that becomes $end, officially.

if($end < $chars) {
		for($i = $end; $i > $start; $i--) {
			$temp = substr($body, $i, 1);
			if($temp == "\n") {
				$end = $i;
				break;
			}
		}
	}
	else {
		$end = $chars;
	}

Now, we’re ready to get our substring. First, we get the length of the substring by subtracting $start from $end; then, we get the substring, strip off the trailing newlines, and convert all newlines to HTML breaks. Then, we send a text header, and echo out the substring (text page).

	$len = $end - $start;
	$body = substr($body, $start, $len);
	$body = rtrim($body);
	$body = nl2br($body);
 
	header('Content-type: text/plain');
	echo $body;

Lastly, we create the page navigation for our long text field, which is basically the same as the page navigation for our record set on index.php. Close the MySQL connection, and we”re done:

if($count > 1) {
		$nav = "<div lang=\"small\">";
		$nav .= "<p>This is page $page of $count pages.</p>\n";
		if($page > 1) {
			$prev = $page - 1;
			$nav .= "<a href=\"javascript:getPagedText($prev);\"><-- Previous page</a> | ";
		}
 
		for($i = 1; $i <= $count; $i++) {
			if($i != $page) {
				$nav .= "<a href=\"javascript:getPagedText($i);\">$i</a>";
			}
			else {
				$nav .= $i;
			}
			if($i < $count) {
				$nav .= " | ";
			}	
		}
 
		if($page < $count) {
			$next = $page + 1;
			$nav .= " | <a href=\"javascript:getPagedText($next);\">Next page --></a>";
		}
 
		$nav .= "</div>";
		echo $nav;
	}
}
 
mysql_close();