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.<\/p>\n
index.html<\/div>\n
\r\n\r\n\r\n\r\n \r\n Scrolling a very big database table<\/title>\r\n <script type=\"text\/javascript\" src=\"functions.js\">\r\n <\/script>\r\n <style type=\"text\/css\">\r\n #gscr div {\r\n height: 1px;\r\n width: 1px;\r\n }\r\n\t\t\t\r\n\t\t\t#result tr td {\r\n\t\t\t\tcursor:pointer;\r\n\t\t\t}\r\n <\/style>\r\n <\/head>\r\n <body>\r\n <h2>Demonstration<\/h2>\r\n <p>Shows how to get an fast and precise result from scrolling a big table using Asynchronous Javascript with JSON.<\/p>\r\n <br\/>\r\n\t\t<div id=\"loading\" style=\"display:block;\"><img decoding=\"async\" src=\"loader.gif\" \/><\/div>\r\n\t\t<div id=\"container\" style=\"display:none;\">\r\n\t <div id=\"the_table\">\r\n\t <div id=\"result_area\" style=\"float:left; width:200px;\">\r\n\t <table id=\"result\"> <\/table>\r\n\t <\/div>\r\n\t <div style=\"float:left;\">\r\n\t <a href=\"javascript:scroll('u')\">previous 20<\/a>\r\n\t <div id=\"gscr\" style=\"width:40px; height:400px; overflow-y:scroll;\" onmouseup=\"javascript:scrAction();\"> <\/div>\r\n\t <a href=\"javascript:scroll('d')\">next 20<\/a>\r\n\t <\/div>\r\n\t\t\t\t<div id=\"collection\" style=\"float:left; padding-left:80px;\">\r\n\t\t\t\t\t<h4>Collected values<\/h4>\r\n\t\t\t\t<\/div>\r\n\t\t\t\t<div style=\"clear:both;\"><\/div>\r\n\t <\/div><!-- the_table div -->\r\n\t <br\/>\r\n\t\t\t<p>Search for a specific pattern:<\/p>\r\n\t\t\t<input type=\"text\" id=\"suggest_field\" onkeyup=\"javascript:make_suggestion(this.value,'string');\"\/>\r\n\t\t\t<input type=\"hidden\" id=\"allEntries\" value=\"\" \/>\r\n\t\t\t<input type=\"hidden\" id=\"mode\" value=\"number\" \/>\r\n\t\t\t<input type=\"hidden\" id=\"running\" value=\"no\" \/>\r\n\t\t\t<input type=\"hidden\" id=\"current\" value=\"1\" \/>\r\n\t\t<\/div><!-- container div -->\r\n <script type=\"text\/javascript\">\r\n make_suggestion(\"\", \"entries\");\r\n <\/script>\r\n <\/body>\r\n<\/html>\r\n<\/pre>\n<p><br class=\"spacer_\" \/><\/p>\n<p><br class=\"spacer_\" \/><\/p>\n<div class=\"code_title\">functions.js<\/div>\n<pre lang=\"javascript\">\r\n\/**\r\n * @author Blackbam\r\n *\/\r\nvar XMLHTTP = null;\r\nvar onetime = 1;\r\nvar onetime2 = 1;\r\n\r\n\/\/ Server-Request for data depending on parameters\r\nfunction make_suggestion(compValue, how){\r\n\r\n if (how == \"string\") {\r\n document.getElementById(\"mode\").value = \"string\";\r\n }\r\n\r\n \/\/ instantiate browser-independent XMLHttpRequest\r\n if (window.XMLHttpRequest) {\r\n XMLHTTP = new XMLHttpRequest();\r\n } else if (window.ActiveXObject) {\r\n try {\r\n XMLHTTP = new ActiveXObject(\"Msxml2.XMLHTTP\");\r\n } catch (ex) {\r\n try {\r\n XMLHTTP = new ActiveXObject(\"Microsoft.XMLHTTP\");\r\n } catch (ex) {\r\n }\r\n }\r\n }\r\n\t\r\n if (how == \"entries\") {\r\n\t\t\/\/ Get the whole number of entries when loading the page\r\n\t\tvar scrHeight = document.getElementById(\"gscr\").style.height;\r\n XMLHTTP.open(\"GET\", \"entries.php?height=\"+scrHeight);\r\n XMLHTTP.onreadystatechange = setEntries;\r\n } else {\r\n if (how == \"string\" && compValue == \"\") {\r\n document.getElementById(\"current\").value = 1;\r\n\t\t\tnumeric_request();\r\n } else {\r\n XMLHTTP.open(\"GET\", \"suggest.php?compValue=\" + compValue + \"&how=\" + how);\r\n XMLHTTP.onreadystatechange = MyRequestExecute;\r\n }\r\n }\r\n XMLHTTP.send(null);\r\n}\r\n\r\n\/\/ is called when the Server-Request reaches a new state\r\nfunction MyRequestExecute(){\r\n\t\/\/ the answer from the server is hier when the XMLHTTP.readyState = 4\r\n if (XMLHTTP.readyState == 4) {\r\n\t\t\r\n\t\t\/\/ remove old entries from the table\r\n\t\tvar cell = document.getElementById(\"result\");\r\n\t\tif ( cell.hasChildNodes() ){\r\n\t\t while ( cell.childNodes.length >= 1 ) {\r\n\t\t cell.removeChild( cell.firstChild ); \r\n\t\t } \r\n\t\t}\r\n\t\t\r\n\t\t\/\/ Create a valid table content with the Jason-object\r\n\t\tvar obj=eval('('+XMLHTTP.responseText+')');\r\n\t\tfor(val in obj){\r\n\t\t\tvar tr = cell.appendChild(document.createElement(\"tr\"));\r\n\t\t\tvar td = tr.appendChild(document.createElement(\"td\"));\r\n\t\t\ttd.setAttribute(\"onclick\",\"javascript:collect(this.firstChild.nodeValue);\");\r\n\t\t\ttd.appendChild(document.createTextNode(obj[val]));\r\n\t\t}\r\n\t\t\r\n\t\t\/\/ if first time, display the table to the user now\r\n\t\tif (onetime2 == 1) {\r\n\t\t\tdocument.getElementById(\"loading\").style.display = \"none\";\r\n\t\t\tdocument.getElementById(\"container\").style.display = \"block\";\r\n\t\t\tonetime2 = 0;\r\n\t\t}\r\n }\r\n}\r\n\r\n\/\/ this function is called when the page is loaded first\r\nfunction setEntries(){\r\n\t\r\n\t\/\/ when the server answers the number of table entries\r\n if (XMLHTTP.readyState == 4) {\r\n\t\tif (onetime == 1) {\r\n\t\t\tonetime = 0;\r\n\t\t\tvar entries = XMLHTTP.responseText;\r\n\t\t\tdocument.getElementById(\"allEntries\").value = entries;\r\n\t\t\t\r\n\t\t\t\/\/ a little hack: create as many 1px-divs as there are entries in the database\r\n\t\t\tfor (i = 0; i <= entries; i++) {\r\n\t\t\t\tdocument.getElementById(\"gscr\").appendChild(document.createElement(\"div\"));\r\n\t\t\t}\r\n\t\t\t\r\n\t\t\t\/\/ now fill the table\r\n\t\t\tnumeric_request();\r\n\t\t}\r\n }\r\n}\r\n\r\n\/\/ is used if a user scrolls the table using the scrollbar\r\nfunction scrAction() {\r\n\t\/\/ alert(document.getElementById(\"gscr\").scrollTop+1);\r\n\tdocument.getElementById(\"current\").value=(document.getElementById(\"gscr\").scrollTop+1);\r\n\tnumeric_request();\r\n}\r\n\r\n\/\/ is called every time a user scrolls the table\r\nfunction numeric_request(){\r\n\tdocument.getElementById(\"mode\").value = \"number\";\r\n\tvar startentry = document.getElementById(\"current\").value;\r\n\tmake_suggestion(startentry, \"number\");\r\n}\r\n\r\n\/\/ is called if a user scrolls the table by previous and next entries\r\nfunction scroll(wohin){\r\n\t\r\n\tvar curEntr = document.getElementById(\"current\");\r\n\tvar scrollbar = document.getElementById(\"gscr\");\r\n\r\n\tvar scrval = parseInt(curEntr.value);\r\n\tvar allEntries = parseInt(document.getElementById(\"allEntries\").value);\r\n\t\r\n\tif(wohin == 'u'){\r\n\t\tif(scrval < 20) {\r\n\t\t\tcurEntr.value = 1;\r\n\t\t\tscrollbar.scrollTop = 1;\r\n\t\t} else {\r\n\t\t\tcurEntr.value = (scrval-19);\r\n\t\t\tscrollbar.scrollTop = (scrval-19);\r\n\t\t}\r\n\t} else {\r\n\t\tvar scrHeight = parseInt(document.getElementById(\"gscr\").style.height)-2;\r\n\t\tvar whole = parseInt(allEntries-scrHeight);\r\n\t\tif(scrval >= whole) {\r\n\t\t\tcurEntr.value = whole;\r\n\t\t\tscrollbar.scrollTop = whole;\r\n\t\t} else {\r\n\t\t\tcurEntr.value = (scrval+19);\r\n\t\t\tscrollbar.scrollTop = (scrval+19);\r\n\t\t}\r\n\t}\r\n\tnumeric_request();\r\n}\r\n\r\n\/\/ stores the values the user has clicked\r\nfunction collect(value) {\r\n\tdocument.getElementById(\"collection\").appendChild(document.createTextNode(value));\r\n\tdocument.getElementById(\"collection\").appendChild(document.createElement(\"br\"));\r\n}\r\n<\/pre>\n<p><br class=\"spacer_\" \/><\/p>\n<p><br class=\"spacer_\" \/><\/p>\n<div class=\"code_title\">suggest.php<\/div>\n<pre lang=\"php\">\r\n<?php\r\n\/* Strings 2 compare *\/\r\n$suggest_string = $_GET[\"compValue\"];\r\n$how = $_GET[\"how\"];\r\n\r\n\/* MySQL connection *\/\r\n$hostname = 'localhost';\r\n$username = 'root';\r\n$password = '';\r\n\r\n\/\/ SQL-Statement\r\nif ($how == \"number\") {\r\n $sql = \"SELECT * FROM meta ORDER BY random_word LIMIT \".$suggest_string.\",20\";\r\n} else {\r\n $sql = \"SELECT * FROM meta WHERE random_word LIKE '\".$suggest_string.\"%' ORDER BY random_word LIMIT 20\";\r\n}\r\n\r\n$result_string = array();\r\n$i = 0;\r\n\r\ntry {\r\n \/\/ Datenbankverbindung\r\n $db = new PDO(\"mysql:host=$hostname;dbname=meta\", $username, $password);\r\n\r\n \/\/ Ergebnisse holen\r\n foreach ($db->query($sql) as $row) {\r\n $result_string[$i] = $row['random_word'];\r\n\t\t$i++;\r\n }\r\n\r\n}\r\ncatch(PDOException $e) {\r\n echo $e->getMessage();\r\n}\r\n\r\necho json_encode($result_string);\r\n\r\n?>\r\n<\/pre>\n<p><br class=\"spacer_\" \/><\/p>\n<p><br class=\"spacer_\" \/><\/p>\n<div class=\"code_title\">entries.php<\/div>\n<pre lang=\"php\">\r\n<?php\r\n\/* MySQL connection *\/\r\n$hostname = 'localhost';\r\n$username = 'root';\r\n$password = '';\r\n\r\n\/\/ h\u00f6he des scrolldivs hinzurechnen\r\n$height = $_GET[\"height\"]-22;\r\n\r\n$sql = \"SELECT * from meta\";\r\n\r\n$result_string = \"\";\r\n\r\ntry {\r\n \/\/ database connection\r\n $db = new PDO(\"mysql:host=$hostname;dbname=meta\", $username, $password);\r\n\r\n \/\/ get results\r\n $result_string = count($db->query($sql)->fetchAll())+$height;\r\n\r\n}\r\ncatch(PDOException $e) {\r\n echo $e->getMessage();\r\n}\r\n\r\necho $result_string;\r\n\r\n?>\r\n<\/pre>\n<p><br class=\"spacer_\" \/><\/p>\n<p><br class=\"spacer_\" \/><\/p>\n<div class=\"code_title\">The dummy – createTable.php<\/div>\n<pre lang=\"php\">\r\n\/*** mysql hostname ***\/\r\n$hostname = 'localhost';\r\n\r\n\/*** mysql username ***\/\r\n$username = 'root';\r\n\r\n\/*** mysql password ***\/\r\n$password = '';\r\n\r\ntry {\r\n $db = new PDO(\"mysql:host=$hostname;dbname=meta\", $username, $password);\r\n\r\n for ($i = 0; $i < 12000; $i++) {\r\n $random_word = randWord();\r\n try {\r\n $db->exec(\"INSERT INTO meta(random_word) VALUES ('$random_word')\");\r\n }\r\n catch(PDOException $e) {\r\n echo \"Eintrag \".$random_word.\" konnte nicht geschrieben werden. <br\/>\";\r\n }\r\n }\r\n\r\n}\r\ncatch(PDOException $e) {\r\n echo $e->getMessage();\r\n}\r\n\r\nfunction randWord() {\r\n $length = rand(5, 15);\r\n $characters = \"abcdefghijklmnopqrstuvwxyz\";\r\n $string = \"\";\r\n\r\n for ($p = 0; $p < $length; $p++) {\r\n $string .= $characters[mt_rand(0, strlen($characters))];\r\n }\r\n\r\n return $string;\r\n}\r\n\r\necho \"It is finally done without crashing the server!\";\r\n\r\n?>\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[173],"tags":[77,138,79],"class_list":["post-885","post","type-post","status-publish","format-standard","hentry","category-javascript-ajax","tag-database","tag-scripts","tag-very-big-table"],"_links":{"self":[{"href":"https:\/\/blog.blackbam.at\/de\/wp-json\/wp\/v2\/posts\/885","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.blackbam.at\/de\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.blackbam.at\/de\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.blackbam.at\/de\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.blackbam.at\/de\/wp-json\/wp\/v2\/comments?post=885"}],"version-history":[{"count":0,"href":"https:\/\/blog.blackbam.at\/de\/wp-json\/wp\/v2\/posts\/885\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.blackbam.at\/de\/wp-json\/wp\/v2\/media?parent=885"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.blackbam.at\/de\/wp-json\/wp\/v2\/categories?post=885"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.blackbam.at\/de\/wp-json\/wp\/v2\/tags?post=885"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}