populating a dropdown dynamically

[digitalgod]

Hey guys,

I've been trying to populate a dropdown box dynamically depending on what the user selected in another dropdown.

What I want it to do is the user selects an element from the first dropdown, the scripts checks the database and if there's one or more sub-categories associated to that element it displays them in the second dropdown. But I don't want the page to refresh itself for that to happen.

This is what I've done so far

[code]
<script language="JavaScript">


function check()
{
var selObj = document.getElementById('night');
if (document.getElementById('club').value != '' )
{

<?php  $checkClub = $_POST["club"];
$result=mysql_query("SELECT * FROM clubnights WHERE club='". $checkClub ."'") or die(query_error());
$row=mysql_fetch_array($result);
?>
        }
}
</script>


<div id="article">
<form action="admin.php?a=editclubnight" method="post" name="form" id="form">
<input type="hidden" name="process" value="yes" />
    <table border="0">
      <tr>
        <td>Club:</td>
        <td><select name="club" id="club" onchange="check()">
          <option>Choose one</option>
          <?php
$sql='SELECT * FROM clubs';
$req=mysql_query($sql) or die(query_error());
while($data = mysql_fetch_assoc($req)) {
echo '<option value="'.$data['name'].'">'.$data['name'].'</option>';
}
?>
        </select></td>
      </tr>
      <tr>
        <td>Night:</td>
        <td><select name="night" id="night">
          <option>Choose one</option>
          <?php while($row = mysql_fetch_assoc($result)) {
echo '<option value="'.$row['night'].'">'.$data['night'].'</option>';
  }?>
        </select></td>
      </tr>
    </table>
<br />
<input type="submit" value="Edit Night" />
</form>
</div>[/code]

so I'm really not sure what to do next... any help would be greatly appreciated!

[jworisek]

try something like this... its what I use:

[code]
<SCRIPT LANGUAGE="JavaScript">
<!-- Original:  Jerome Caron (jerome.caron@globetrotter.net) -->
<!-- This script and many more are available free online at -->
<!-- The JavaScript Source!! http://javascript.internet.com -->
<!--  new Array("display", value)  -->
colors = new Array(
new Array(
new Array("Blue", 638),
new Array("Yellow", 945),
),
new Array(
new Array("Red", 638),
new Array("Orange", 945),
new Array("pink", 634)
);

function fillSelectFromArray(selectCtrl, itemArray, goodPrompt, badPrompt, defaultItem) {
var i, j;
var prompt;
// empty existing items
for (i = selectCtrl.options.length; i >= 0; i--) {
selectCtrl.options[i] = null;
}
prompt = (itemArray != null) ? goodPrompt : badPrompt;
if (prompt == null) {
j = 0;
}
else {
selectCtrl.options[0] = new Option(prompt);
j = 1;
}
if (itemArray != null) {
// add new items
for (i = 0; i < itemArray.length; i++) {
selectCtrl.options[j] = new Option(itemArray[i][0]);
if (itemArray[i][1] != null) {
selectCtrl.options[j].value = itemArray[i][1];
}
j++;
}
// select first item (prompt) for sub list
selectCtrl.options[0].selected = true;
  }
}
//  End -->
</script>
[/code]

[code]
<form method="POST" action="two.php" name="Main">
  <h1>New Order</h1>
  <h2>Process</h2>
<SELECT NAME="process_id" onChange="fillSelectFromArray(this.form.color_id, ((this.selectedIndex == -1) ? null : colors[this.selectedIndex-1]));">
<OPTION VALUE="-1">Select Process</option>
  <? echo $process_list; ?>
</SELECT>
<h2>Enhancement</h2>
<SELECT NAME="color_id">
  <option value="0">Select Enhancement</option>
</SELECT>
  <input type="submit" value="submit" name="submit" />
</form>
[/code]

So depending on which process chosen, the correct enhancements are placed in the dropdown menu.  Unfortunately this way I need to preload all enhancements.  If you want to do it on the fly look into using AJAX to access a php script and bring the data back without refreshing.

[digitalgod]

that's my problem though I can't add the elements manually, they have to be retrieved from a DB since they're added/removed from an admin panel....

[jworisek]

well I populate mine with php into a bunch of arrays (I just put the static arrays there as an example)...  like I said though, if you want to do it without refreshing and dynamically check the database, look into using AJAX.

[Guest devans]

Hi.  maybe this will help.  I'm no expert, but this works for me.  I found the javascript on the web (here: http://www.quirksmode.org/js/options.html), then altered it to dynamically query a db.  The thing I like about it is that it allows both a option value and name in your selection dropdown box.

First, I build the option fields for the first selection box.  This one is dynamic, but you could just as well make it static down in your html code, as the javacript author had done.

[code]
<?php
$sql = 'SELECT foo_value, foo_name FROM bar;
$result = mysql_query($sql) or die (mysql_errno().": select ".mysql_error()."<BR>" . $sql);
$numRows = mysql_num_rows($result);
while ($row = mysql_fetch_array($result)) {
$select1_drop .= '<OPTION VALUE="' . $row['foo_value'] . '">' . $row['foo_name'] . '</OPTION>';
}
?>
[/code]

Next, after your <HEAD> html tag, query the db to build the second selection box (name and value).  Each js array represents a different set of selections, depending on the <option value> of the first box.  The number of arrays is equal to the number of items in the first selection box.  It's probably totally goofy to echo <SCRIPT>, but I had a problem escaping to insert php code in the middle of <javascript>

[code]
<?php
echo '<SCRIPT language=JavaScript type=text/javascript> ';
echo 'var store = new Array();';
for ($i=0; $i<$numRows; $i++){ //for each item
$js_array = "";
$sql = 'SELECT foo2_name, foo2_value FROM bar2;
$result = mysql_query($sql) or die (mysql_errno().": select ".mysql_error()."<BR>" . $sql);
while ($row = mysql_fetch_array($result)) {
$js_array .= "'" . $row['foo2_name'] . "','" . $row['foo2_value'] . "',";
}
$js_array = rtrim( $js_array , ","); // trim final comma
echo 'store['.$i.'] = new Array('; // start javascript array
echo $js_array . "); ";
} // end for
echo '</SCRIPT>';
?>
[/code]

The next section tests that the browser will work, and populates the dropdown.  If you have more than one form on your page, remember to change the forms[number]!

[code]
<SCRIPT language=JavaScript type=text/javascript>
<!-- Begin

/* Execute init() function */
window.onload = initialize;
function initialize () {
if (self.init) self.init();
}

function init()
{
optionTest = true;
lgth = document.forms[0].select1.options.length - 1;
document.forms[0].select1.options[lgth] = null;
if (document.forms[0].select1.options[lgth]) optionTest = false;
if (optionTest) self.populate();
}

function populate()
{
if (!optionTest) return;
var box = document.forms[0].select2;
var number = box.options[box.selectedIndex].value;
if (!number) return;
var list = store[number];
var box2 = document.forms[0].select1;
box2.options.length = 0;
for(i=0;i<list.length;i+=2)
{
box2.options[i/2] = new Option(list[i],list[i+1]);
}
}
//  End -->
</SCRIPT>
[/code]

And lastly, we have the actual form elements that go in your html code...

[code]
<FORM NAME="yourform" METHOD=POST>
<SELECT NAME="select1" onChange="populate()">&gt;
<?php echo $select1_drop; ?>
</SELECT>

<SELECT NAME="select2">&gt;
<OPTION VALUE="">- - - - - - - - - - - - - - -</OPTION>
<optionyour browser can't handle this script</option>
</SELECT>
</FORM>
[/code]

Remember to make the select names match html<->jscript.  Hope this gets you started.  I've trimmed down the example, but it should work with only minor modifications to match your env.

daniel

[digitalgod]

Hi daniel,

I couldn't try this before because I was moving out and didn't have internet yet.. I jus gave it a shot but was unsucessful. I keep getting an error in IE saying

Line: 42
Char: 10
Error: 'length' is null or not an object
Code: 0

Do you think it's because not all of the elements have a subcategory?? What needs to be changed in the script to fix that? I tried changing a few things but it didn't really help.

[code]<?php

$sql = 'SELECT * FROM clubs';
$result = mysql_query($sql) or die (mysql_errno().": select ".mysql_error()."<BR>" . $sql);
$numRows = mysql_num_rows($result);
while ($row = mysql_fetch_array($result)) {
$select1_drop .= '<OPTION VALUE="' . $row['name'] . '">' . $row['name'] . '</OPTION>';
}

echo '<SCRIPT language=JavaScript type=text/javascript> ';
echo 'var store = new Array();';
for ($i=0; $i<$numRows; $i++){ //for each item
$js_array = "";
$sql = 'SELECT * FROM clubnights';
$result = mysql_query($sql) or die (mysql_errno().": select ".mysql_error()."<BR>" . $sql);
while ($row = mysql_fetch_array($result)) {
$js_array .= "'" . $row['night'] . "','" . $row['night'] . "',";
}
$js_array = rtrim( $js_array , ","); // trim final comma
echo 'store['.$i.'] = new Array('; // start javascript array
echo $js_array . "); ";
} // end for
echo '</SCRIPT>';
?>

<SCRIPT language=JavaScript type=text/javascript>
<!-- Begin

/* Execute init() function */
window.onload = initialize;
function initialize () {
if (self.init) self.init();
}

function init()
{
optionTest = true;
lgth = document.forms[0].nightDrop.options.length - 1;
document.forms[0].nightDrop.options[lgth] = null;
if (document.forms[0].nightDrop.options[lgth]) optionTest = false;
}


function populate()
{
if (!optionTest) return;
var box = document.forms[0].clubDrop;
var number = box.options[box.selectedIndex].value;
if (!number) return;
var list = store[number];
var box2 = document.forms[0].nightDrop;
box2.options.length = 0;
for(i=0;i<list.length;i+=2)
{
box2.options[i/2] = new Option(list[i],list[i+1]);
}
}

//  End -->
</SCRIPT>

<div id="article">
<form action="admin.php?a=editclubnight" method="post" name="form" id="form">
<input type="hidden" name="process" value="yes" />
    <table border="0">
      <tr>
        <td>Club:</td>
        <td><select name="clubDrop" id="club" onchange="populate()">
          <option>Choose one</option>
          <?php echo $select1_drop; ?>
        </select></td>
      </tr>
      <tr>
        <td>Night:</td>
        <td><select name="nightDrop" id="night">
          <OPTION VALUE="">- - - - - - - - - - - - - - -</OPTION>
<option>your browser can't handle this script</option>

        </select></td>
      </tr>
    </table>
<br />
<input type="submit" value="Edit Night" />
</form>
</div>[/code]


I'm not sure what to do, I have a table that contains all of the clubs and another table that contains all of the nights and infos. So if I create a night for a club then in 'clubnights' there will be the name of the night, for instance friday and the club name.

so the clubnights table looks like

ID | Club     | Night |....
1  | System | Friday |....
2  | System | Saturday |...
etc
Right now when I select a club in the dropdown, the second dropdown becomes blank

[devans]

hi.  I've changed your code a little...
[code]
$sql = 'SELECT id, name FROM clubs';
$result = mysql_query($sql) or die (mysql_errno().": select ".mysql_error()."<BR>" . $sql);
$numRows = mysql_num_rows($result);
while ($row = mysql_fetch_array($result)) {
$select1_drop .= '<OPTION VALUE="' . $row['id'] . '">' . $row['name'] . '</OPTION>';
}
[/code]

which should get you something in your dropdown, but you might want to also alter your second query such that it only returns appropiate items for that club...

daniel

[digitalgod]

ok I got it to display something now my problem is that i display all the nights that have info, it doesn't matter what club is chosen.

[code]
echo '<SCRIPT language=JavaScript type=text/javascript> ';
echo 'var store = new Array();';
for ($i=0; $i<$numRows; $i++){ //for each item
$js_array = "";
$sql = 'SELECT * FROM clubnights'; // I know I have to add a WHERE here

$result = mysql_query($sql) or die (mysql_errno().": select ".mysql_error()."<BR>" . $sql);
while ($row = mysql_fetch_array($result)) {
$js_array .= "'" . $row['night'] . "','" . $row['night'] . "',";
}
$js_array = rtrim( $js_array , ","); // trim final comma
echo 'store['.$i.'] = new Array('; // start javascript array
echo $js_array . "); ";
} // end for
echo '</SCRIPT>';
[/code]

I know I have to add a WHERE not sure how, since the value it gets from the first dropdown is the ID of the club table, but that idea doesn't match the ID in the clubnights table.

Any ideas what I could do?

*edit*

actually forget what I said it doesn't work at all  :(

I'm really lost here...