Populate form text field from database based on dropdown field selection

Question

I've dug through many posts but can't find a scenario quite like this on here...

I have a PHP form that contains a dropdown selection containing option values from a MySQL database table. This part works fine. Here's where I need help...

There is a textarea field that I need to populate with data from the same database table, but a different field. This table contains a 'name' and a corresponding set of 'text'. The dropdown shows the 'name' as the options. So what I need is when a selection is made, a script runs (like onChange)... takes the selected value, queries the database to get the 'text' that is associated with the selected 'name'. That 'text' is then displayed inside of the textarea form field.

A section of this code for you to view is:

echo "<form action=$PHP_SELF method=\"post\">\n";
echo "<select id=\"conditions\" name=\"conditions\">\n";
echo "<option value=\"Select\" selected>Select a Message</option>\n";

$result = mysqli_query($link, "SELECT * FROM db_scripts");
while ($data = mysqli_fetch_object($result)) {
    echo "<option value=".$data->script_id.">".$data->script_name."</option>\n";
}

echo "</select>\n";

echo "<br><textarea name=\"message\" style=\"width:300px; height:130px\" data-maxsize=\"160\" data-output=\"status1\" wrap=\"virtual\" maxlength=\"160\"></textarea><br />\n";

So, like I said, this part works just fine. I have the dropdown with the 'script_name' as the options. Now I just need to get the corresponding message into the textarea field "message". Any assistance is most appreciated!

roshan, thanks for that link, but it doesn't help. It's pulling the textarea value from a POST request... but I need the value to come from the select field automatically... BEFORE the form is submitted... so the populated textarea value is included in the form POST. — DirectTech

Jared Gotte Jared Gotte · Accepted Answer · 2013-11-06T21:38:58

Create an associative javascript object literal array in PHP in this format:

<?php
echo "
<!-- this script goes in your <head> -->\n
<script>\n
var db_array = [";
$result = mysqli_query($link, "SELECT * FROM db_scripts");
$first = TRUE; // remove trailing comma from array
while ($data = mysqli_fetch_object($result)) {
  if ($first === TRUE) $first = FALSE;
  else echo ',';
  echo "
  {
    id: '$data->script_id',
    name: '$data->script_name'
    message: '$data->script_message'
  }";
}
echo "];\n
</script>";
?>

The catch is that if your message is not in the same row as the id, then you need to use JOIN in your query. The idea behind this part is that you're only querying the database once. You don't want the client to query the database every time they switch an option in the dropdown menu. The exception here is if you have megabytes+ of messages. In this case, consider implementing caching measures.

Moving on... once you have the array constructed, you can build your <option>s by iterating through the array like so:

<?php
echo "
<form action=\"$PHP_SELF\" method=\"post\">\n
<select id=\"conditions\" name=\"conditions\">\n
<option value=\"Select\" selected>Select a Message</option>\n
<script>\n
for (var i = 0; i < db_array.length; i++) {\n
  document.write('<option value=\"'+db_array[i].id+'\">'+db_array[i].name+'</option>');\n
}\n
</script>\n
</select>\n
<!-- notice the id attribute I added -->\n
<br><textarea id=\"message\" name=\"message\" style=\"width:300px; height:130px\" data-maxsize=\"160\" data-output=\"status1\" wrap=\"virtual\" maxlength=\"160\"></textarea><br />\n";
?>

...then have a script with access to these dynamically created DOM elements (e.g. at the end of the <body> tag)

<script>
// returns the db_array index of the object id supplied
function returnIndex (id) {
  for (var i = 0; i < db_array.length; i++) {
    if (db_array[i].id === id) return i;
  }
  return -1; // returns index of -1 if id is not found
}
// changes the <textarea> message. notice the id I added to the messages <textarea> above
function changeMessage (id) {
  // remember to handle the case of an id not being found within db_array (where returnIndex returns -1)
  document.getElementById('message').innerHTML = db_array[returnIndex(id)].message;
}
// on the event that we switch options, do this event. realize that you'll have to run the changeMessage function once the page loads if you want the first option's message to show
document.getElementById('conditions').onchange = function() {
  changeMessage(this.selectedIndex);
}
</script>

Populate form text field from database based on dropdown field selection

3 Answers