18. October 2010
von Blackbam

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.



index.html



    
        Scrolling a very big database table
        
        
    
    
        

Demonstration

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




functions.js
/**
 * @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"));
}



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

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

echo json_encode($result_string);

?>



entries.php
query($sql)->fetchAll())+$height;

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

echo $result_string;

?>



The dummy - createTable.php
/*** 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!"; ?>
Share

Dieser Eintrag wurde am 18. October 2010 um 17:17 in der Kategorie JavaScript / Ajax veröffentlicht. You can book the comments for this article RSS 2.0. Feedback, discussion, commendation and critics are welcome: Write a comment or trackback.


Tags: , ,

No comments yet

Kommentare abonnieren (RSS) or URL Trackback

Leave a comment: