根据之前的选择选择字段

Question

I am trying to populate a select dropdown from a previous select field (on the same page)

The first select, shows table names from a MYSQL db. The second select should show column names from the selected table

Here is my code

page.php

<label class="">Select a Database Table</label>
 <select class="full-width" data-placeholder="Select Table" data-init-plugin="select2" onchange="showColumn(this.value);">
  <optgroup label="Table">

<?php

   $table_count = mysql_query("SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = 'cl49-vclients'"); 

    $tables = array();
    while($row = mysql_fetch_array($table_count))
    { ?>
  <option value="<?php echo $row["TABLE_NAME"]; ?>"><?php echo $row["TABLE_NAME"]; ?></option>
<?php
   }
?>
 <!-- </optgroup>-->
</select>
<script>
function showColumn(tablename)
{
    if(tablename !='')
    {
        $.ajax({ 
            type:"POST",
            url :"ajax.php"; 
            data:{tablename:tablename},
            success:function(data){
                alert(data); //This will alert column name comma separated  
            }       
        });
    }
}
</script>

ajax.php

<?php
//make database connection
$column_count = mysql_query("SHOW COLUMNS FROM '".$_POST['tablename']."'"); 
    $columns = array();
    while($columnRow = mysql_fetch_array($column_count))
    {
        $columns[] = $columnRow['Field'];
    }   
    echo implode(',',$columns); //This will return column name comma separated 
?>

<label class="">Table</label>
  <select class="full-width" data-placeholder="Select Table" data-init-plugin="select2" onchange="showColumn(this.value);">
  <optgroup label="Table">

<?php
$table_count = mysql_query("SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = 'cl49-vclients'"); 

    $tables = array();
    while($row = mysql_fetch_array($table_count))
    {
?>
  <option value="<?php echo $row["TABLE_NAME"]; ?>"><?php echo $row["TABLE_NAME"]; ?></option>
<?php
   }
?>
  </optgroup>
</select>

The first select seems to be showing the table names fine, however when I select one another select field should appear but it doesnt, can anyone help?

Answer 1

In the second query you are not selecting any database. You need

mysqli_select_db($con,"information_schema");

SHOW COLUMNS FROM '".$_POST['tablename']."'

Alternatively, Replace your 2nd query like this

SELECT COLUMN_NAME FROM information_schema.columns 
WHERE table_schema = 'cl49-vclients' AND TABLE_NAME = '".$_POST['tablename']."';