Database maintenance tools (like TheUsual) often offer dropdown choices of databases and tables, then various possible ways of changing or deleting those databases and tables, or creating others.
That is, the user can easily do something to make that dropdown content obsolete. It's not practical to repaint the whole page every time this happens.
Enter Asynchronous Javacsript And XML, aka AJAX—a set of methods for asynchronously sending data to a server and retrieving data from it without disturbing the display and behaviour of the rest of a web page.
Such methods have three parts:
- a PHP script to retrieve the required database info and output it in a way that a Javascript listener can anticipate
- Javascript to invoke the PHP script and deposit the PHP result where it belongs on the page
DIVs in the dropdowns to define where the info is to be written
We'll take them is reverse order.
Declaration of DIVs in dropdowns where AJAX updates are to be written
To make dropdown content visible to a Javascript function, enclose <select>...</select> lists in inline DIVs and give them unique IDs. Here is such a database selection dropdown (a function call has populated the array $adbs with database names):
echo "
<FORM name='DbForm' id='prompt' action='$thispage' method='GET'>\n
<b>Schema:</b> <div id='dbdiv' style='display:inline'>
<select name='db' id='dbsel' onChange='dbonly_submit(this.form)'>";
foreach( $adbs as $d ) {
$sel = ( $db === $d ) ? "id='dsel' selected" : "";
printf( " <option %s value='%s'>%s</option>\n", $sel, $d, $d );
}
echo " </select>\n </div>\n";
The DIV exactly encapsulates dropdown content. As you'll see in the next section, the Javascript function updateAll() uses this fact to rewrite the innerHTML of those DIVs.
Javascript to invoke the PHP script and rewrite the dropdowns
In TheUsual, there are three dropdowns to update—one for databases, one for tables belonging to the selected database, and one for saved queries that have been defined for the selected database. Could we invoke an AJAX method three times in succession?
No, there we run into an AJAX limitation—with multiple consecutive asynchronous calls, all except one are trampled. Instead, the PHP script needs to concatenate the three <select>...</select> blocks; the Javascript function will have to parse the blocks:
<script language="Javascript">
function refresh( s ) {
xmlHttp=GetXmlHttpObject()
if (xmlHttp==null) {
alert( "Browser does not support AJAX." )
return false;
}
xmlHttp.onreadystatechange=updateAll; // NAME OF FUNC TO UPDATE DROPDOWNS
xmlHttp.open("GET","refresh.php?_sess=" + s,true); // ASYNC CALL TO PHP SCRIPT
xmlHttp.send(null);
return true;
}
function updateAll() {
if( (xmlHttp.readyState==4 || xmlHttp.readyState=="complete")
&& xmlHttp.status==200
) {
var s = xmlHttp.responseText;
// FIND START OF TABLE DROPDOWN DATA
var i = s.indexOf( " <select name='table'" );
if( i == -1 ) {
return false;
}
else {
document.getElementById("dbdiv").innerHTML = s.substring( 0, i-1 );
s = s.substring( i );
// FIND START OF SAVED QUERIES DATA
var j = s.indexOf( " <select name='view'" );
if( j == -1 ) {
document.getElementById("tbldiv").innerHTML = s;
}
else {
document.getElementById("tbldiv").innerHTML = s.substring(0,j-1 );
if( document.getElementById("viewdiv") in window ) {
document.getElementById("viewdiv").innerHTML = s.substring( j );
}
}
return true;
}
}
}
function GetXmlHttpObject() {
var objXMLHttp=null;
if( window.XMLHttpRequest ) {
objXMLHttp=new XMLHttpRequest();
}
else if (window.ActiveXObject) {
objXMLHttp=new ActiveXObject("Microsoft.XMLHTTP");
}
return objXMLHttp;
}
</script>
PHP background script to update dropdown lists
The PHP script has to retrieve connection parameters from TheUsual's $_SESSION whose name is passed to it by the Javascript function updateAll(), connect to the MySQL server, run queries for databases, tables and saved queries, and from the data it retrieves concatenate <select>...</select> blocks that the Javascript function updateAll() will parse:
<?php
ini_set( "log_errors", 1 );
ini_set( "error_log", "theusual.err" );
ini_set( "display_errors", 0 );
require_once( "session_continue.php" );
$h = $_SESSION['host'];
$u = $_SESSION['user'];
$p = $_SESSION['pswd'];
$db = $_SESSION['db'];
$v = ( isset($_SESSION['viewsdb']) ? $_SESSION['viewsdb'] : null );
$conn = mysqli_connect($h, $u, $p, $db ) or exit( "No connection" );
// DATABASES
$res = mysqli_query( $conn, "show databases" );
$adbs = array(); $asysdbs = array();
$html = " <select name='db' id='dbsel' "
"onChange='dbonly_submit(this.form)'>\n";
while( $row = mysqli_fetch_row( $res )) {
if( $row[0]=="information_schema"||$row[0]=="performance_schema" )
$asysdbs[] = $row[0];
else $adbs[] = $row[0];
}
foreach( $asysdbs as $c ) $adbs[] = $c;
foreach( $adbs as $c ) {
$sel = ( $db === $c ) ? "id='dsel' selected" : "";
$html .= sprintf( "<option %s value='%s'>%s</option>\n",$sel,$c,$c );
}
$html .= " </select>\n";
// TABLES
$qry = "SHOW TABLES FROM ".idcleanup($db);
$res = mysqli_query( $conn, $qry );
$html.= " <select name='table' id='tblsel' " .
"onChange='table_submit(this.form)'>\n";
while( $row = mysqli_fetch_row( $res )) {
$c = $row[0];
$sel = ( $db === $c ) ? "id='tsel' selected" : "";
$html .= sprintf( "<option %s value='%s'>%s</option>\n",$sel,$c,$c );
}
$html .= " </select>\n";
// SAVED QUERIES
if( isset($v) && !empty($v) ) {
$v = idcleanup($v);
$qry = "SELECT name FROM $v.theusualviews WHERE db='$db' ORDER BY name";
$res = mysqli_query( $conn, $qry );
$rows = mysqli_num_rows( $res );
if( $rows > 0 ) {
$viewname =
( (isset( $_SESSION['view']) && !empty($_SESSION['view']) )
? $_SESSION['view'] : null );
$html .= " <select name='view' id='viewsel' " .
"onChange='view_submit(this.form)'>\n";
while( $row = mysqli_fetch_row( $res )) {
$c = $row[0];
if( isset($viewname) )
$sel = ((strcasecmp($viewname,$c)== 0)
? "id='vsel' selected" : "" );
else $sel = "";
$html .=
sprintf( "<option %s value='%s'>%s</option>\n", $sel, $c, $c );
}
$html .= " </select>\n";
}
}
mysqli_close($conn);
echo $html; // JAVASCRIPT FUNC WILL SUCK UP THIS OUTPUT
function idcleanup( $idref ) {
$q = $_SESSION['idquote'];
$parts = explode( ".", $idref );
$n = count( $parts );
for( $i=0; $i<$n; $i++ ) {
if( strpos( $parts[$i], $q ) === FALSE )
$parts[$i] = $q . $parts[$i] . $q;
}
return implode( ".", $parts );
}
?>
To see this in action, download all the source code from here.Last updated 8 Dec 2024 |
|