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
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="de">
    <head>
        <title>Scrolling a very big database table</title>
        <script type="text/javascript" src="functions.js">
        </script>
        <style type="text/css">
            #gscr div {
                height: 1px;
                width: 1px;
            }
   
   #result tr td {
    cursor:pointer;
   }
        </style>
    </head>
    <body>
        <h2>Demonstration</h2>
        <p>Shows how to get an fast and precise result from scrolling a big table using Asynchronous Javascript with JSON.</p>
        <br/>
  <div id="loading" style="display:block;"><img src="loader.gif" /></div>
  <div id="container" style="display:none;">
         <div id="the_table">
             <div id="result_area" style="float:left; width:200px;">
                 <table id="result"> </table>
             </div>
             <div style="float:left;">
                 <a href="javascript:scroll('u')">previous 20</a>
                 <div id="gscr" style="width:40px; height:400px; overflow-y:scroll;" onmouseup="javascript:scrAction();"> </div>
                 <a href="javascript:scroll('d')">next 20</a>
             </div>
    <div id="collection" style="float:left; padding-left:80px;">
     <h4>Collected values</h4>
    </div>
    <div style="clear:both;"></div>
         </div><!-- the_table div -->
         <br/>
   <p>Search for a specific pattern:</p>
   <input type="text" id="suggest_field" onkeyup="javascript:make_suggestion(this.value,'string');"/>
   <input type="hidden" id="allEntries" value="" />
   <input type="hidden" id="mode" value="number" />
   <input type="hidden" id="running" value="no" />
   <input type="hidden" id="current" value="1" />
  </div><!-- container div -->
        <script type="text/javascript">
            make_suggestion("", "entries");
        </script>
    </body>
</html>



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
<?php
/* Strings 2 compare */
$suggest_string = $_GET["compValue"];
$how = $_GET["how"];

/* MySQL connection */
$hostname = 'localhost';
$username = 'root';
$password = '';

// SQL-Statement
if ($how == "number") {
    $sql = "SELECT * FROM meta ORDER BY random_word LIMIT ".$suggest_string.",20";
} else {
    $sql = "SELECT * FROM meta WHERE random_word LIKE '".$suggest_string."%' ORDER BY random_word LIMIT 20";
}

$result_string = array();
$i = 0;

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

    // Ergebnisse holen
    foreach ($db->query($sql) as $row) {
        $result_string[$i] = $row['random_word'];
  $i++;
    }

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

echo json_encode($result_string);

?>



entries.php
<?php
/* MySQL connection */
$hostname = 'localhost';
$username = 'root';
$password = '';

// höhe des scrolldivs hinzurechnen
$height = $_GET["height"]-22;

$sql = "SELECT * from meta";

$result_string = "";

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

    // get results
    $result_string = count($db->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. <br/>";
        }
    }

}
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: