Link to home
Start Free TrialLog in
Avatar of Caliguian
Caliguian

asked on

*difficult* Dynamic Drop Down Lists (involves php, mysql and javascript)

I couldn't decide whether to put this in the javascript section or in the php section, so I thought I would try it here first and see what happens.

(i know the points are low, but i promise to up them if a solution is given, [money coming tommorow :)])

This is pretty complicated, but I would like to ask for some help, so I will be as clear as possible:

I have two tables: product and product_option.
The product table has two fields: product_id and product_name
The product_option table has four fields:  product_id, product_option_id, product_option_parent_id, product_option_name

The product and product_option tables are linked together by the product_id.

With the product_option table as it is, there can be many product options to a single product and the products can be mulitple layers deep(because of the parent id).  For example, someone picks a t-shirt (which is a product); the t-shirt comes in short sleeve and long sleeve;  the short sleeve shirt comes in red, green and blue and the long sleeve shirt comes in green and yellow;  the short sleeve red shirt comes in small, medium and large the short sleeve green shirt comes in large and xtra large... etc. (many layers deep)

I would like be able to create a bunch of dynamic drop down lists (depending on the number of layers deep that the options go [one list for each layer]), and then have the lists populate according to the selections made in the other lists.   The lists will only need to be created for one product at a time (the product id will always be known beforehand).

It would work something like this if (using the example from above) if they were to pick a t-shirt as the product:
drop down list 1:  Short Sleeve, Long Sleeve
drop down list 2: (populated after an appropriate selection is made in the list 1, or pre-populated to the first option in list 1)
              If short sleeve is selected:  Red, Green, Blue
              If long sleeve is selected: Green, Yellow
drop down list 3: (populated after an appropriate selection is made in the list 1 and 2, or pre-populated to the first option in lists 1 & 2)
              If list 1 selection is short sleeve and red is selected in list 2: Small, Medium, Large
              If list 1 selection is short sleeve and green is selected in list 2: Large, xtra-Large
etc...

The number of options available to a product is dynamic in size and thus the number of drop down lists is also dynamic.

So... what I am looking for is a solution that would allow me to create an html page with these lists created and working correctly based on the two tables of information mentioned above.

I promise more points to anyone that can give me a solution (i just need to buy them first :) )
Avatar of VGR
VGR

nobody there for this nice guy's question ?
Avatar of Zvonko
Are you nobody?
Ok niceguy, we do it this way. You post here the example output list of, let say, thirty of your options and I compose for you a dynamic dropdown <select> options generator in JavaScript for the browser side.

Put it in that format as it comes out from the database.
Like this:

 product_id, product_option_id, product_option_parent_id, product_option_name

Avatar of Caliguian

ASKER

That sounds great.   This data is only 3 levels deep, but it could be more....

this is not quite 30 options (more like 20), but i think it should be okay.

72        4        0        'Short'
72       5       0       'Long'
72       6       4       'Red'
72       7       4       'Green'
72       8       4       'Orange'
72       9       5       'Blue'
72       10       5       'Navy'
72       11       5       'Purple'
72       12       6       'Small'
72       13       6       'Medium'
72       14       6       'Large'
72       15       7       'X-Small'
72       16       7       'XX-Large'
72       17       8       'Small'
72       18       8       'Tiny'
72       19       9       'Medium'
72      20      9        'Large'
72      21      9        'X-Large'
72       22       10       'Super Small'
72       23       10       'Super Large'

Thanks for your help.  If you need any more information, just let me know.
oh ya, the 0 for the product_option_parent_id means that it is a root option (lowest level)
Ok, here is only the front-end part.

<html>
<head>
<script>
var oAr = [
[72,  4,  0,  'Short'],
[72,  5,  0,  'Long'],
[72,  6,  4,  'Red'],
[72,  7,  4,  'Green'],
[72,  8,  4,  'Orange'],
[72,  9,  5,  'Blue'],
[72, 10,  5,  'Navy'],
[72, 11,  5,  'Purple'],
[72, 12,  6,  'Small'],
[72, 13,  6,  'Medium'],
[72, 14,  6,  'Large'],
[72, 15,  7,  'X-Small'],
[72, 16,  7,  'XX-Large'],
[72, 17,  8,  'Small'],
[72, 18,  8,  'Tiny'],
[72, 19,  9,  'Medium'],
[72, 20,  9,  'Large'],
[72, 21,  9,  'X-Large'],
[72, 22,  10, 'Super Small'],
[72, 23,  10, 'Super Large'],
[72, 24,  16, 'Transparent'],
[72, 25,  16, 'Fluorescent']];

var selOp = new Array();

function sOpt(theSel){
  sOnr = theSel.options[theSel.selectedIndex].onr;
  Sid = theSel.name.replace(/[^\d]/g,'')*1;
  oA = document.getElementById('optArea').innerHTML.split("<ES"+Sid+">")[0];
  Sid++;
  oA+="<ES"+(Sid-1)+"><select name=S"+Sid+" onChange='sOpt(this)'></select><ES"+Sid+">"
  document.getElementById('optArea').innerHTML=oA;
  Sno = document.forms[0]['S'+Sid].options;  
  for(i=0;i<oAr.length;i++){
    if(oAr[i][2]==sOnr){
      Sno[Sno.length]= new Option(oAr[i][3],oAr[i][3]);
      Sno[Sno.length-1].onr = oAr[i][1];
    }
  }
  if(Sno.length==0){
    Sid--;
    oA = document.getElementById('optArea').innerHTML.split("<ES"+Sid+">")[0];
    document.getElementById('optArea').innerHTML=oA+"<ES"+Sid+">"
  } else {
   Sno[0].selected=true;
   Sn = document.forms[0]['S'+Sid];
   Sn.onchange(Sn);    
  }
}

function initOpt(){
  sel1 = "<select name=S1 onChange='sOpt(this)'></select><ES1>"
  document.getElementById('optArea').innerHTML=sel1;
  S1o = document.forms[0].S1.options;
  for(i=0;i<oAr.length;i++){
    if(oAr[i][2]==0){
      S1o[S1o.length]= new Option(oAr[i][3],oAr[i][3]);
      S1o[S1o.length-1].onr = oAr[i][1];
    }
  }
  S1o[0].selected=true;
  S1 = document.forms[0].S1;
  S1.onchange(S1);
}
</script>
</head>
<body onLoad="initOpt()">
<form>
<div id="optArea">
</div>
<input type=submit>
</form>
</body>
</html>


To show you the dynamic part I added a fourth level for this selection:
?S1=Short&S2=Green&S3=XX-Large&S4=Transparent

Give it a try.

So long,
Zvonko


ASKER CERTIFIED SOLUTION
Avatar of Zvonko
Zvonko
Flag of North Macedonia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Oh, I forgot to insert the root password :-)
If your product option names contain single quotas, then change the print statement to this:

   while($col = mysql_fetch_row($result)) {
      print '[ $col[0], $col[1], $col[2], "$col[3]"],';
   }


Sorry, take better this:

    while($col = mysql_fetch_row($result)) {
      print "[ $col[0], $col[1], $col[2], \"$col[3]\"],\n";
    }

That is awsome.  That would've taken me quite a while to come up with and my code probably would've been pretty ugly. The extra points are coming... give me a day and you will get them.  Thanks.
this doesn't seem to work in netscape.  any ideas about that?
Thanks for the points.

Which Netscape version? Do not say 4.x :(

This method is working with browser's DOM capabilities. I create the html code for the <select>s on the fly. If you want this for browsers without DOM, then we have to put hard coded <select>s in layers and hide the empty <selects>s. Empty selects are selects without options.

What do you say?

using netscape 7 or mozilla 5.0.  i don't know what the DOM capabilities are in these browsers, but it would be best to have this working for all current browsers (Netscape, Safari, Opera, IE, Mozilla, etc).  I have found that once it works in Netscape, it usually will work on all of them.  If you could provide a way for it to work in all browsers, I would be willing to dish out some more points if you would like them.  Thanks for the speedy replys.
Hello,

the problem was that I created own <option> attributes which was not supported by Netscape.

Try now this version:

<html>
<head>
<script>
var optArray = [
<?php
   /* connect to mysql */
   $link = mysql_connect("cs.removed.this.net", "root", "")
       or die("Connection failed: " . mysql_error());
   mysql_select_db("prodopt") or die("database 'prodopt' access failed.");
   $query = "SELECT  product_id, product_option_id, product_option_parent_id, product_option_name FROM  product_option";
   $result = mysql_query($query) or die("Query failed: " . mysql_error());
   while($col = mysql_fetch_row($result)) {
      print "[ $col[0], $col[1], $col[2], \"$col[3]\"],\n";
   }
   mysql_free_result($result);
   mysql_close($link);
?>
   []];
optArray.length = optArray.length-1;

function switchOptions(theSel){
 sonr = theSel.value;
 sid = theSel.name.replace(/[^\d]/g,'')*1;
 selArea = document.getElementById('optArea').innerHTML.split("<ES"+sid+">")[0];
 sid++;
 selArea+="<ES"+(sid-1)+"><select name=S"+sid+" onChange='switchOptions(this)'></select><ES"+sid+">"
 document.getElementById('optArea').innerHTML=selArea;
 selopt = document.forms[0]['S'+sid].options;  
 for(i=0;i<optArray.length;i++){
   if(optArray[i][2]==sonr){
     selopt[selopt.length]= new Option(optArray[i][3],optArray[i][1]);
   }
 }
 if(selopt.length==0){
   sid--;
   selArea = document.getElementById('optArea').innerHTML.split("<ES"+sid+">")[0];
    document.getElementById('optArea').innerHTML=selArea+"<ES"+sid+">"
 } else {
  selopt[0].selected=true;
  Sn = document.forms[0]['S'+sid];
  Sn.onchange(Sn);    
 }
}

function initOpt(){
 sel1 = "<select name=S1 onChange='switchOptions(this)'></select><ES1>"
 document.getElementById('optArea').innerHTML=sel1;
 selopt = document.forms[0].S1.options;
 for(i=0;i<optArray.length;i++){
   if(optArray[i][2]==0){
     selopt[selopt.length]= new Option(optArray[i][3],optArray[i][1]);
   }
 }
 selopt[0].selected=true;
 S1 = document.forms[0].S1;
 S1.onchange(S1);
}
</script>
</head>
<body onLoad="initOpt()">
<form>
<div id="optArea">
</div>
<input type=submit>
</form>
</body>
</html>


Now is also the response to web server no more this:
?S1=Short&S2=Green&S3=XX-Large&S4=Transparent

But this:
?S1=4&S2=6&S3=15&S4=24

That mean, the selected values are no more option names, but product_option_id numbers.

Check it.

We tried it on Mozilla and it would not work.  We are trying to modify it ourselves a little bit, but so far it does not work.  Do you have Mozilla or Netscape that you could test it on?

The change to ?S1=4&S2=6&S3=15&S4=24 is a good change (we were going to change that anyway), so thanks for doing that.
I can not analyze what's going wrong from your statement "it does not work".
I tested in Netscape 7.02 without problems.
Tell me what version you are testing, what behavior you see and what errors you see when you enter javascript: in URL field of Netscape browser.

Also look into generated browser html code for MySql errors.

sorry about the non-descript error report.  i figured that you would have the same problem and that i would not need to be more descriptive.  well, here is the problem:(in netscape 7.02):  When the page first loads everything appears to be correct, and all the drop down lists are populated.  But when you click on another option on any of the lists, it seems to keep the first option selected no matter what you click.  the other boxes do seem to populate with the other data, but the original option still apears in the drop down list you have just selected from.

It is kind of odd that it would work correctly for you in the same version of netscape that it doesn't work for me in.  I wonder what is up.  Here is the source code from the page after the data has been pulled from the database(a little bit different data than before, but still just nonesense test data):

<html>
<head>
<script>
var optArray = [
[ 1, 1, 0, "BBs"],
[ 1, 2, 0, "Pellets"],
[ 1, 3, 0, "Shot"],
[ 1, 4, 1, "Round"],
[ 1, 5, 1, "Square"],
[ 1, 6, 1, "Curvy"],
[ 1, 7, 2, "Round"],
[ 1, 8, 2, "Cubed"],
[ 1, 9, 2, "Boxed"],
[ 1, 10, 2, "Cylindar"],
[ 1, 11, 4, "Aluminum"],
[ 1, 12, 4, "Brass"],
[ 1, 13, 4, "Alloy"],
[ 1, 14, 5, "Wood"],
[ 1, 15, 7, "Glass"],
[ 1, 16, 7, "Pebbles"],
[ 1, 17, 8, "Wood"],
[ 1, 18, 8, "Splinters"],
  []];
optArray.length = optArray.length-1;

function switchOptions(theSel){
sonr = theSel.value;
sid = theSel.name.replace(/[^\d]/g,'')*1;
selArea = document.getElementById('optArea').innerHTML.split("<ES"+sid+">")[0];
sid++;
selArea+="<ES"+(sid-1)+"><select name=S"+sid+" onChange='switchOptions(this)'></select><ES"+sid+">"
document.getElementById('optArea').innerHTML=selArea;
selopt = document.forms[0]['S'+sid].options;  
for(i=0;i<optArray.length;i++){
  if(optArray[i][2]==sonr){
    selopt[selopt.length]= new Option(optArray[i][3],optArray[i][1]);
  }
}
if(selopt.length==0){
  sid--;
  selArea = document.getElementById('optArea').innerHTML.split("<ES"+sid+">")[0];
    document.getElementById('optArea').innerHTML=selArea+"<ES"+sid+">"
} else {
 selopt[0].selected=true;
 Sn = document.forms[0]['S'+sid];
 Sn.onchange(Sn);    
}
}

function initOpt(){
sel1 = "<select name=S1 onChange='switchOptions(this)'></select><ES1>"
document.getElementById('optArea').innerHTML=sel1;
selopt = document.forms[0].S1.options;
for(i=0;i<optArray.length;i++){
  if(optArray[i][2]==0){
    selopt[selopt.length]= new Option(optArray[i][3],optArray[i][1]);
  }
}
selopt[0].selected=true;
S1 = document.forms[0].S1;
S1.onchange(S1);
}
</script>
</head>
<body onLoad="initOpt()">
<form>
<div id="optArea">
</div>
<input type=submit>
</form>
</body>
</html>
no more comments?
Sorry, I get to much notifications from EE and so overlooked yours.

The problem is simple: I forgot to add two mandatory parameters for the method: new Option()
Simply add two parameters in the new Option() calls like this:

for(i=0;i<optArray.length;i++){
 if(optArray[i][2]==sonr){
   selopt[selopt.length]= new Option(optArray[i][3],optArray[i][1],false,false);
 }
}


Good luck,
Zvonko

Sorry, that false options were not enough.
The last selected state has to be reselected in Netscape.

Here the reselect version:

<html>
<head>
<script>
var optArray = [
[ 1, 1, 0, "BBs"],
[ 1, 2, 0, "Pellets"],
[ 1, 3, 0, "Shot"],
[ 1, 4, 1, "Round"],
[ 1, 5, 1, "Square"],
[ 1, 6, 1, "Curvy"],
[ 1, 7, 2, "Round"],
[ 1, 8, 2, "Cubed"],
[ 1, 9, 2, "Boxed"],
[ 1, 10, 2, "Cylindar"],
[ 1, 11, 4, "Aluminum"],
[ 1, 12, 4, "Brass"],
[ 1, 13, 4, "Alloy"],
[ 1, 14, 5, "Wood"],
[ 1, 15, 7, "Glass"],
[ 1, 16, 7, "Pebbles"],
[ 1, 17, 8, "Wood"],
[ 1, 18, 8, "Splinters"],
 []];
optArray.length = optArray.length-1;

function switchOptions(theSel){
sonr = theSel.value;
sid = theSel.name.replace(/[^\d]/g,'')*1;
var lastSel = new Array();
for(i=0;i<sid;i++)
  lastSel[i]=document.forms[0]['S'+(i+1)].selectedIndex;
selArea = document.getElementById('optArea').innerHTML.split("<ES"+sid+">")[0];
sid++;
selArea+="<ES"+(sid-1)+"><select name=S"+sid+" onChange='switchOptions(this)'></select><ES"+sid+">"
document.getElementById('optArea').innerHTML=selArea;
selopt = document.forms[0]['S'+sid].options;  
for(i=0;i<optArray.length;i++){
 if(optArray[i][2]==sonr){
   selopt[selopt.length]= new Option(optArray[i][3],optArray[i][1],false,false);
 }
}
if(selopt.length==0){
 sid--;
 selArea = document.getElementById('optArea').innerHTML.split("<ES"+sid+">")[0];
   document.getElementById('optArea').innerHTML=selArea+"<ES"+sid+">"
} else {
selopt[0].selected=true;
Sn = document.forms[0]['S'+sid];
Sn.onchange(Sn);    
}
for(i=0;i<lastSel.length;i++)
  document.forms[0]['S'+(i+1)].options[lastSel[i]].selected=true;
}

function initOpt(){
sel1 = "<select name=S1 onChange='switchOptions(this)'></select><ES1>"
document.getElementById('optArea').innerHTML=sel1;
selopt = document.forms[0].S1.options;
for(i=0;i<optArray.length;i++){
 if(optArray[i][2]==0){
   selopt[selopt.length]= new Option(optArray[i][3],optArray[i][1],false,false);
 }
}
selopt[0].selected=true;
S1 = document.forms[0].S1;
S1.onchange(S1);
}
</script>
</head>
<body onLoad="initOpt()">
<form>
<div id="optArea">
</div>
<input type=submit>
</form>
</body>
</html>


great!  thanks for doing that.  how do i assign you more points once the question is already answered?
No extra points necessary.
The offer is honor enough for me :-)

See you,
Zvonko

thanks.