Populate one dropdown from another

from the Artful MySQL Tips List


Every week or so in the MySQL PHP forum, someone asks how to populate a dropdown, then populate a second dropdown based on the user's choice in the first. Here's a stripped-down, self-documenting example of how to do that. Copy the code to a PHP file in your web server document tree, fill in your MySQL login values, and run it. To see the HTML code it generates, view the source whilst the script is running.

To see how to turn a very basic idea like this into a full-fledged database table browser with editing capabilities see http://www.artfulsoftware.com/theusualReadMe.html.

<?php 
// INITIALISE SESSION 
session_start(); 

// SUBMIT FUNC TO CAPTURE ONCHANGE EVENTS IN FORM 
echo "<html><head><script>function db_submit( f ) { f.submit(); }</script></head>\n<body>\n"

// INSERT YOUR MYSQL AUTHENTICATION VALUES: 
$host=''$usr=''$pwd=''

// WHEN USER MAKES A FORM SELECTION, FORM RETURNS RESULT TO THIS PAGE 
$thispage $_SERVER['PHP_SELF']; 

// GET USER'S CHOICES OF DB AND TABLE, IF ANY: 
if( isset($_GET['db']) ) { 
  
$db $_GET['db']; 
  
$_SESSION['db'] = $db;
  if( !isset(
$_SESSION['prevdb']) || $db != $_SESSION['prevdb'] ) {
    unset( 
$_SESSION['table'] );
  }

elseif( isset( 
$_SESSION['db'] )) { 
  
$db $_SESSION['db']; 

if( isset( 
$_GET['table'] )) { 
  if( 
$db == $_SESSION['prevdb'] ) {
    
$table $_GET['table']; 
    
$_SESSION['table'] = $table
  }
  else 
$table="";

elseif( isset( 
$_SESSION['table'] )) { 
  
$table $_SESSION['table']; 
}
$_SESSION['prevdb'] = $_SESSION['db'];

// CONNECT TO MYSQL 
$conn mysqli_connect$host$usr$pwd ) or exit( mysqli_connect_error() ); 
mysqli_set_charset$conn'utf8');       // BETTER THAN SET NAMES 

// GET LIST OF AVAILABLE DBS: 
$result mysqli_query$conn"SHOW DATABASES" )  
          or exit( 
mysqli_error($conn) ); 

// POPULATE DROPDOWN CONTROL FOR USER'S CHOICE OF A DB 
echo "<br /><FORM name='DbForm' id='prompt' action='$thispage' method='GET'>\n"
     
"  <b>Schema:</b>&nbsp;\n  <select name='db' onChange='db_submit(this.form)'>\n"
while( 
$row mysqli_fetch_row$result )) { 
  
$sel = ( $db === $row[0] ) ? "id='sel' selected" ""
  
printf"    <option %s value='%s'>%s</option>]\n"$sel$row[0], $row[0] ); 

echo 
"  </select>\n"
if ( empty(
$db) ) { 
  echo 
"  <input id='edit' type='button' value='Select a database' "
       
"onClick='db_submit(this.form)'>\n</FORM>\n"
  exit(); 

mysqli_select_db$conn$db ); 

// POPULATE DROPDOWN CONTROL FOR USER'S CHOICE OF TABLE IN CHOSEN DB: 
$res mysqli_query$conn"SHOW tables" 
       or exit( 
mysqli_error($conn) ); 
echo 
"  &nbsp;&nbsp;<b>Table:</b>&nbsp;\n"
     
"  <select name='table' onChange='table_submit(this.form)'>\n"
while( 
$row mysqli_fetch_row$res )) { 
  
$sel = ( $table === $row[0] ) ? "id='sel' selected" ""
  
printf"    <option %s value='%s'>%s</option>\n"$sel$row[0], $row[0] ); 

echo 
"  </select>\n"
     
"  <input id='edit' type='button' value='Select' onClick='db_submit(this.form)'>\n"

// FETCH TABLE ROWS
if( !isset($table) or empty($table) ) echo "<br>Nothing to browse."
else { 
  
$res mysqli_query$conn"SELECT * FROM `$db`.`$table` ORDER BY 1 LIMIT 50" 
         or exit( 
mysqli_error($conn) );
  if( !
$rows=mysqli_num_rows($res) ) echo "<br>No rows to browse.";
  else {
    echo 
"<br/><br />Showing "$rows" row", ($rows>1?"s":"")," from <i>$db.$table</i>:<br/><br />"
    
r2html$res ); 
  }

echo 
"</body>\n</html>"

// PAINT RESULT AS HTML TABLE  
function r2html$res$tblformat="border='1' cellspacing='5'" ) {  
  GLOBAL 
$conn
  echo 
"<table $tblformat>\n<tr>"
  for( 
$i 0$i mysqli_num_fields$res ); $i++ ) {
    
$o mysqli_fetch_field$res );
    echo 
"<td><b>"$o->name"</b></td>\n"
  }
  echo 
"</tr>\n"
  while( 
$row mysqli_fetch_row$res )) { 
    echo 
"<tr>"
    foreach( 
$row as $c ) echo "<td>",(empty($c) ? '&nbsp;' $c),"</td>"
    echo 
"</tr>\n"
  } 
  echo 
"</table>\n"

?>

Return to the Artful MySQL Tips page