Dynamic dropdowns with Ajax

from the Artful MySQL Tips List


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>&nbsp; <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");
ini_set"error_log""theusual.err" );
ini_set"display_errors");
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 ) {
    
$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.

Return to the Artful MySQL Tips page