Dynamically populated drop-down menus from database

Question

I have an html+php page. There are 3 drop down menus. Values for the menus come from an sqlite database. I'd like to make a selection from the first menu, and based on that choice, have the second drop down menu dynamically populated. And then again the same for the 3rd menu.

I have seen Dynamically populate drop down menu with selection from previous drop down menu but: 1. I would like to have this done with php alone, if possible 2. Without any need for external plugins/resources, because the page will run on the intranet without access to the web.

I have tried the following code, with post/get methods, but when the 2nd post is called it clears the data from the 1st post.

<form action="" method="get" name="proj_form"> 
<?php 
  $db = new SQLite3('FEINT_DB.db');  
  $sql="SELECT project FROM synthesis_metrics WHERE is_project=1"; 
  $query = $db->query($sql); // Run your query  
  echo '<select name="project" id="project">';     
  while ($row = $query->fetchArray()) {  
     echo '<option value="'.$row['project'].'">'.$row['project'].'</option>';  
    } 
   echo "</select>"; 
$project =$_GET['project']; 
?> 
<input type="submit" name="projbutton" value="Submit"/></form> 
<?php  echo "You chose $project <br>"; ?> 

<form action="" method="post"> 
<?php 
  $sql="SELECT CL FROM synthesis_metrics WHERE is_CL=1"; 
  $query = $db->query($sql); // Run your query  
  echo '<select name="CL" id="CL">';     
  while ($row = $query->fetchArray()) {  
     echo '<option value="'.$row[CL].'">'.$row['CL'].'</option>';  
    } 
   echo "</select>"; 
$CL =$_POST['CL']; 
?> 
  <input type="submit" name="button" value="Submit"/></form> 
<?php  echo "You chose $CL <br>"; ?> 

<form action="" method="get" name="tile_form"> 
<?php 
   $sql="SELECT tile FROM synthesis_metrics WHERE CL=$CL AND is_t=1"; 
  $query = $db->query($sql); // Run your query  
  echo '<select name="tiel" id="tiel">';     
  while ($row = $query->fetchArray()) {  
     echo '<option value="'.$row[tiel].'">'.$row['tiel'].'</option>';  
    } 
  echo "</select>";

Image: after pressing the 3rd submit:

@MasivuyeCokile Can you suggest a way with Ajax, without a need for external resources? — yorgo, Jun 10 '19 at 13:45
@MasivuyeCokile: That's not true at all. This can be done with PHP. It's probably easier and cleaner with AJAX (and doesn't require re-loading the page), but there's no reason why PHP can't be used to receive form values and output the updated page based on those values. — David, Jun 10 '19 at 13:47

David · Accepted Answer · 2019-06-10T15:33:26.547

3

when the 2nd post is called it clears the data from the 1st post

Because you're not outputting that data to the form when you re-render it. When you render the <option> elements there's no indication of which one should be selected:

echo '<option value="'.$row['project'].'">'.$row['project'].'</option>';

Provide that indication:

if ($row['project'] == $_GET['project']) {
  echo '<option value="'.$row['project'].'" selected>'.$row['project'].'</option>';
} else {
  echo '<option value="'.$row['project'].'">'.$row['project'].'</option>';
}

This simply compares the value of the current rendering <option> with the value of the posted one. If it's the same, include the selected attribute on that <option> element.

Update: I also just noticed you're using three different <form> elements, and some are POST while others are GET. This is going to cause unnecessary confusion. It would probably be easier to wrap all three of these in a single <form> element so they're all included in the same request.

Side note: Your code is wide open to SQL injection. You should start by reading this page and take a look at some solutions here.

edited Jun 10 '19 at 15:33

answered Jun 10 '19 at 13:46

David

208,112
36
198
279

I tried adding the control in each of the while loops. The result is the same as before, I'll try to add a 2nd image in the description above, of how it looks after I submit the 3rd dropdown button. Is there anything else that need to be added to keep the selection? – yorgo Jun 10 '19 at 15:27
@yorgo: I just updated the answer. It looks like you're also using three different forms, but should probably just be using one. That way subsequent form posts include all of the relevant information, because you want it all included each time. Wrap all three of your ` – David Jun 10 '19 at 15:34
The 2nd form needs to be populated based on the choice in the 1st form. Can this be done if it is in the same "select"? Update: meant in the same "form". – yorgo Jun 10 '19 at 15:40
@yorgo: It not only can but probably should all be in the same form, since it's all posting to the same page anyway. Basically each form post needs its selected value and the values that came before it. If you keep them in separate forms then you'd need to add hidden inputs to the second and third forms to include the previous values. – David Jun 10 '19 at 17:31

score 0 · Answer 2 · answered Jun 10 '19 at 13:53

At first glance, the code should work. But you are storing all query references in the same variable. Try storing query results in different variables. Try this one-

<form action="" method="get" name="proj_form"> <?php $db = new SQLite3('FEINT_DB.db'); $sql="SELECT project FROM synthesis_metrics WHERE is_project=1"; $query1 = $db->query($sql); echo '<select name="project" id="project">'; while ($row = $query1->fetchArray()) { echo '<option value="'.$row['project'].'">'.$row['project'].'</option>'; } echo "</select>"; $project =$_GET['project']; ?> <input type="submit" name="projbutton" value="Submit"/></form> <?php echo "You chose $project <br>"; ?> <form action="" method="post"> <?php $sql="SELECT CL FROM synthesis_metrics WHERE is_CL=1"; $query2 = $db->query($sql); echo '<select name="CL" id="CL">'; while ($row = $query2->fetchArray()) { echo '<option value="'.$row[CL].'">'.$row['CL'].'</option>'; } echo "</select>"; $CL =$_POST['CL']; ?> <input type="submit" name="button" value="Submit"/></form> <?php echo "You chose $CL <br>"; ?> <form action="" method="get" name="tile_form"> <?php $sql="SELECT tile FROM synthesis_metrics WHERE CL=$CL AND is_t=1"; $query3 = $db->query($sql); echo '<select name="tiel" id="tiel">'; while ($row = $query3->fetchArray()) { echo '<option value="'.$row[tiel].'">'.$row['tiel'].'</option>'; } echo "</select>";

Sorry for the bad formatting. :(

Dynamically populated drop-down menus from database

2 Answers2