This tutorial shows how it is possible to scroll a very big database table using Asynchronous Javascript and JSON with an easy extendable and customizable script. We created four files – index.html (contains the Ajax table), functions.js (contains all the Ajax work), suggest.php (the server-side work), and entries.php (server-side script for instantiating number of table values). There is also a dummy file to create and fill the database with values fast, which is called “createTable.php”.

Watch the example here

Function: When a user types in a certain pattern, the twenty nearest values are fetched from the database. When a user scrolls the table, the values are fetched depending on the position of the cursor or by fetching the next or previous entries from the database.

    
        
        
        
    
    
        
Demonstration
        
Shows how to get an fast and precise result from scrolling a big table using Asynchronous Javascript with JSON.
        

		

		

	        

	            

	                
	            
	            

	                previous 20
	                
 
	                next 20
	            
				

					
Collected values
				
				

	        

	        

			
Search for a specific pattern:
			
			
			
			
			
		

        
    

/**
 * @author Blackbam
 */
var XMLHTTP = null;
var onetime = 1;
var onetime2 = 1;

// Server-Request for data depending on parameters
function make_suggestion(compValue, how){

    if (how == "string") {
        document.getElementById("mode").value = "string";
    }

    // instantiate browser-independent XMLHttpRequest
    if (window.XMLHttpRequest) {
        XMLHTTP = new XMLHttpRequest();
    } else if (window.ActiveXObject) {
            try {
                XMLHTTP = new ActiveXObject("Msxml2.XMLHTTP");
            } catch (ex) {
                try {
                    XMLHTTP = new ActiveXObject("Microsoft.XMLHTTP");
                } catch (ex) {
                }
            }
        }
	
    if (how == "entries") {
		// Get the whole number of entries when loading the page
		var scrHeight = document.getElementById("gscr").style.height;
        XMLHTTP.open("GET", "entries.php?height="+scrHeight);
        XMLHTTP.onreadystatechange = setEntries;
    } else {
        if (how == "string" && compValue == "") {
            document.getElementById("current").value = 1;
			numeric_request();
        } else {
                XMLHTTP.open("GET", "suggest.php?compValue=" + compValue + "&how=" + how);
                XMLHTTP.onreadystatechange = MyRequestExecute;
        }
    }
    XMLHTTP.send(null);
}

// is called when the Server-Request reaches a new state
function MyRequestExecute(){
	// the answer from the server is hier when the XMLHTTP.readyState = 4
    if (XMLHTTP.readyState == 4) {
		
		// remove old entries from the table
		var cell = document.getElementById("result");
		if ( cell.hasChildNodes() ){
		    while ( cell.childNodes.length >= 1 ) {
		        cell.removeChild( cell.firstChild );       
		    } 
		}
		
		// Create a valid table content with the Jason-object
		var obj=eval('('+XMLHTTP.responseText+')');
		for(val in obj){
			var tr = cell.appendChild(document.createElement("tr"));
			var td = tr.appendChild(document.createElement("td"));
			td.setAttribute("onclick","javascript:collect(this.firstChild.nodeValue);");
			td.appendChild(document.createTextNode(obj[val]));
		}
		
		// if first time, display the table to the user now
		if (onetime2 == 1) {
			document.getElementById("loading").style.display = "none";
			document.getElementById("container").style.display = "block";
			onetime2 = 0;
		}
    }
}

// this function is called when the page is loaded first
function setEntries(){
	
	// when the server answers the number of table entries
    if (XMLHTTP.readyState == 4) {
		if (onetime == 1) {
			onetime = 0;
			var entries = XMLHTTP.responseText;
			document.getElementById("allEntries").value = entries;
			
			// a little hack: create as many 1px-divs as there are entries in the database
			for (i = 0; i <= entries; i++) {
				document.getElementById("gscr").appendChild(document.createElement("div"));
			}
			
			// now fill the table
			numeric_request();
		}
    }
}

// is used if a user scrolls the table using the scrollbar
function scrAction() {
	// alert(document.getElementById("gscr").scrollTop+1);
	document.getElementById("current").value=(document.getElementById("gscr").scrollTop+1);
	numeric_request();
}

// is called every time a user scrolls the table
function numeric_request(){
	document.getElementById("mode").value = "number";
	var startentry = document.getElementById("current").value;
	make_suggestion(startentry, "number");
}

// is called if a user scrolls the table by previous and next entries
function scroll(wohin){
	
	var curEntr = document.getElementById("current");
	var scrollbar = document.getElementById("gscr");

	var scrval = parseInt(curEntr.value);
	var allEntries = parseInt(document.getElementById("allEntries").value);
	
	if(wohin == 'u'){
		if(scrval < 20) {
			curEntr.value = 1;
			scrollbar.scrollTop = 1;
		} else {
			curEntr.value = (scrval-19);
			scrollbar.scrollTop = (scrval-19);
		}
	} else {
		var scrHeight = parseInt(document.getElementById("gscr").style.height)-2;
		var whole = parseInt(allEntries-scrHeight);
		if(scrval >= whole) {
			curEntr.value = whole;
			scrollbar.scrollTop = whole;
		} else {
			curEntr.value = (scrval+19);
			scrollbar.scrollTop = (scrval+19);
		}
	}
	numeric_request();
}

// stores the values the user has clicked
function collect(value) {
	document.getElementById("collection").appendChild(document.createTextNode(value));
	document.getElementById("collection").appendChild(document.createElement("br"));
}

query($sql) as $row) {
        $result_string[$i] = $row['random_word'];
		$i++;
    }

}
catch(PDOException $e) {
    echo $e->getMessage();
}

echo json_encode($result_string);

?>

query($sql)->fetchAll())+$height;

}
catch(PDOException $e) {
    echo $e->getMessage();
}

echo $result_string;

?>

/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'root';

/*** mysql password ***/
$password = '';

try {
    $db = new PDO("mysql:host=$hostname;dbname=meta", $username, $password);

    for ($i = 0; $i < 12000; $i++) {
        $random_word = randWord();
        try {
            $db->exec("INSERT INTO meta(random_word) VALUES ('$random_word')");
        }
        catch(PDOException $e) {
            echo "Eintrag ".$random_word." konnte nicht geschrieben werden. 
";
        }
    }

}
catch(PDOException $e) {
    echo $e->getMessage();
}

function randWord() {
    $length = rand(5, 15);
    $characters = "abcdefghijklmnopqrstuvwxyz";
    $string = "";

    for ($p = 0; $p < $length; $p++) {
        $string .= $characters[mt_rand(0, strlen($characters))];
    }

    return $string;
}

echo "It is finally done without crashing the server!";

?>