Populate one dropdown from another

from the Artful MySQL Tips List


Every few days 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='YOUR MYSQL HOSTNAME'$usr='YOUR MYSQL USERNAME'$pwd='YOUR MYSQL PASSWORD';

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

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

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

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

// POPULATE DROPDOWN CONTROL FOR USER'S CHOICE OF A DB
echo "n<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$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";

// BROWSE THE TABLE
if( !isset( $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) );
  echo 
"<br/>Found "mysql_num_rows$res ), " rows in $db.$table:<br/>";
  
r2html$res );
}
echo 
"</body>n</html>";

// TABLE BROWSER
function r2html$res$tblformat="border='1'" ) {  // PAINT RESULT AS HTML TABLE
  
GLOBAL $conn;
  echo 
"<table $tblformat>n<tr>";
  for( 
$i 0$i mysql_num_fields$res ); $i++ )
    echo 
"<td><b>"mysql_field_name$res$i), "</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