Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Dec 2012
    Posts
    22
    Thanks
    1
    Thanked 0 Times in 0 Posts

    How to show column B value based on column A value

    Hi,

    I need to create a form, with 2 columns (A & B). By default, column A will show all vendors in a drop-down box and only upon user selection, then the list of products that belong to that vendor will appear in column B drop-down box for user to select.
    - Column A contains vendor's name (eg. Vendor1, Vendor2, Vendor3 etc)
    - Column B contains product name for each vendor.

    Database that I created:
    - 1 Table (vendor), 1 column (vendorname)
    - 1 Table (product), 2 columns (productname, vendorname)

    Can someone please kindly help?

    Thanks.

    Below are the code I wrote but if there is alot of vendors, I have to create a lot of div id and under function showfield(), I have to check alot condition. Thus, I need a simple way.

    PHP Code:
    <script type="text/javascript">

     function hidefield(){
     document.getElementById('div1').style.display='none';
     document.getElementById('div2').style.display='none';
     }

     function showfield(name){
     if (name=='vendor1'){
     document.getElementById('div1').style.display='block';
     }else{
     document.getElementById('div1').style.display='none';
     }

     if (name=='vendor2'){
     document.getElementById('div2').style.display='block';
     }else{
     document.getElementById('div2').style.display='none';
     }
     }

     </script>

     <html>
     <body onload="hidefield()">

     <td>
     <select name="vendor" onchange="showfield(this.options[this.selecte"].value)">
     <option value=""></option>
     <?php
     $result1
    =mysql_query("SELECT * FROM vendor");

     while (
    $row1=mysql_fetch_array($result1)){
     
    ?>
     <option value='<?php echo $row1["vendorname"]; ?>'><?php echo $row1["vendorname"]; ?></option>
     <?php ?>
     </select>
     </td>

     <td>
     <div id="div1">
     <select name="product"> 
    <option value=""></option> 
    <?php 
    $result2
    =mysql_query("SELECT * FROM product WHERE vendorname='vendor1'");
    while (
    $row2=mysql_fetch_array($result2)){ 
    ?> 
    <option value='<?php echo $row2["productname"]; ?>'><?php echo $row2["productname"]; ?></option>
     <?php ?>
     </select>
     </div>

     <div id="div2">
     <select name="product"> 
    <option value=""></option> 
    <?
    php $result2
    =mysql_query("SELECT * FROM product WHERE vendorname='vendor2'"); 
    while (
    $row2=mysql_fetch_array($result2)){ 
    ?> 
    <option value='<?php echo $row2["productname"]; ?>'><?php echo $row2["productname"]; ?></option>
     <?php ?>
     </select>
     </div>

     </td>
     </body>
     </html>

  • #2
    Regular Coder
    Join Date
    Jan 2013
    Location
    Germany
    Posts
    578
    Thanks
    4
    Thanked 77 Times in 77 Posts
    One way to do it is to not name the divs div1, div2, … but rather divVendor1, divVendor2, … (assuming "VendorX" is only a placeholder name) and then do

    Code:
    function hideAllFields () {
        // you can implement this yourself
    }
    
    function showField (vendorName) {
        hideAllFields();
        document.getElementById( 'div' + vendorName ).style.display = 'block';
    }
    Of course there are many (and frankly, better) ways to do it, but this is a way that is similar to yours. As for the hideAllFields function, don't you dare do the same mistake again and write the hiding command out for every vendor. An easy way would be assigning a css class to every div and then find all divs with that class and hide them. Alternatively, wrap all these divs into a parent div and then hide all direct descendants. Whatever you do, just stay flexible – no hardcoding!

    As for the other thing: If you really have a lot of vendors, your PHP/MySQL code is, sorry, terribly slow. The simple answer is to create the divs for each vendor in a loop – this will at least save you having to write out the code for every vendor. However, your code will still be slow because you're still querying your database way more often than you have to.

    The better way is to improve your SQL statements. The two most important ones being:
    1) Do NOT use the * selector. Select only the fields you actually need.
    2) Do NOT query the database again and again for every vendor. Select all products at once, sorting them by the vendor and then display the result accordingly. A hint: The sorting can already be done in MySQL.

    Keep in mind that the above assumes that vendor names are unique. If they are not, you need to use an abstraction layer and assign unique IDs to each vendor with which you can work then.
    Last edited by Airblader; 01-31-2013 at 10:23 AM.

  • #3
    Regular Coder
    Join Date
    Jan 2013
    Location
    Germany
    Posts
    578
    Thanks
    4
    Thanked 77 Times in 77 Posts
    I have updated my answer (pretty much rewrote it). Just in case you had already read it.

  • #4
    New Coder
    Join Date
    Dec 2012
    Posts
    22
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi,

    I have tried re-writing the codes, but I have no idea how to code inside the showfield part. Not really sure if I am going on the right track

    By default, product field will not be shown and when user click a vendor name, only all the products belong to that vendor will be shown.

    Can someone please kindly advise? Thanks.

    Below are the database design and the codes.

    Database:
    I created 1 database with 2 tables:
    1) Table named "vendor" with fields - id (primary key, auto-increment) and vendorname
    2) Table named "product" with fields - id (primary key, auto-increment), productname, vendorid (this will be the foreign key that link to the id in vendor table to identify the vendor that the product belongs to)

    Code:
     
    <script type="text/javascript">
    
    function hidefield(){
          document.getElementById('product').style.display='none';  
    } 
    
    function showfield(name){
           ??????  
    } 
    
    </script>  
    
    <body onload="hidefield()">
    
    <td>        
         <select name="vendor" onchange="showfield(this.options[this.selectedIndex].value)"> 
                  <option value=""></option>        
         <?php                         
                  $result1=mysql_query("SELECT * FROM vendor");                                 
                  while ($row1=mysql_fetch_array($result1)){        
          ?>                        
                  <option value='<?php echo $row1["vendorname"]; ?>'><?php echo $row1["vendorname"]; ?></option>        
         <?php } ?>        
         </select>
    </td> 
    
    <div id="product">        
            <?php                   
                     $total=2; //number of vendors                   
                     for ($i=1; $i<=$total; $i++){                   
                             $vendor=$i;        
             ?>                
    
             <div id="<?php echo $vendor; ?>"> 
             <select name="product">                        
                     <option value=""></option>         
             <?php   
                     $result2=mysql_query("SELECT * FROM product WHERE vendorid='$vendor'");                         
                     while ($row2=mysql_fetch_array($result2)){         
             ?>                  
                       <option value='<?php echo $row2["productname"]; ?>'><?php echo $row2["productname"]; ?></option>         
              <?php } ?>         
              </select>        
              </div>                      
              <?php } ?>
    
    </div>  
    </body>

  • #5
    Supreme Master coder! Philip M's Avatar
    Join Date
    Jun 2002
    Location
    London, England
    Posts
    18,174
    Thanks
    203
    Thanked 2,549 Times in 2,527 Posts
    Assuming the vendors are actually assigned vendor1 etc. and the corresponding divs are div1 etc, then you could simplify as follows:-

    Code:
    <script type = "text/javascript">
    function hidefields(){ 
    document.getElementById('div1').style.display='none'; 
    document.getElementById('div2').style.display='none';
    // and so on (you could use a loop if desired)
    } 
     
    function showfield(n) { 
    hidefields();
    n = n.replace(/[^0-9]/gi,"");  // extract the final number
    var d = "div" + n;
    document.getElementById(d).style.display='block'; 
    </script>
    Or better to do as was suggested in post #2. Name (id) the divs specifically divJones, divRobinson etc.

    It has already been pointed out that a better way of hiding the divs would be to assign a css class to every div and then find all divs with that class and hide them.

    Don't use the word name for a Javascript variable,. Use vname or something.

    Finally, have a look at http://www.codingforums.com/showthread.php?t=282936 which may give you some ideas.
    Last edited by Philip M; 02-14-2013 at 08:48 AM.

    All the code given in this post has been tested and is intended to address the question asked.
    Unless stated otherwise it is not just a demonstration.

  • #6
    New Coder
    Join Date
    Dec 2012
    Posts
    22
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi,

    Thanks for the advice. However, I have been reading this for many times and also looking at alot examples but still don't really get an understanding on how it works.

    Below code shows all the data retrieve from individual table and I have no idea how to make the dependant drop-down list. Can someone please kindly help me out on this? Thanks.

    For example:
    When user click "PC" > "Dell" and "HP" will shown > and then when user select "Dell" > "New Inspiron 15" and "Customizable XPS 8500" will shown

    My database:
    Code:
    Table name ("category")
         - Field name ("id",categoryname)
           Example:
             - 1st record ("1", "PC")
             - 2nd record ("2", "Furniture")
    
    Table name ("vendor")
         - Field name ("id","vendorname","categoryid")
           Example:
             - 1st record ("1", "Dell", "1")
             - 2nd record ("2", "HP", "1")
             - 3rd record ("3", "Reurion", "2")
    
    Table name ("product")
         - Field name ("id","productname","vendorid")
           Example:
             - 1st record ("1", "New Inspiron 15", "1")
             - 2nd record ("2", "Customizable XPS 8500", "1")
             - 3rd record ("3", "HP 200t-2c00", "2")
             - 4th record ("4", "Cardboard 123", "3")
    Code:
    	
    
    <script type="text/javascript">
    
    function addInput(){
    	var tbl = document.getElementById('tblAddress');
    	var lastRow = tbl.rows.length;
    	var row = tbl.rows[lastRow-1].cloneNode(true);
    	tbl.tBodies[0].appendChild(row);
    	var txts = row.getElementsByTagName('input');
    	for(var i = 0; i < txts.length; i++){
    		if(txts[i].type == "text") {
    			txts[i].value = "";
    		}
    	}
    }
    
    </script>
    
    <html>	
    <table border="1" id="tblAddress">		
    <td>
         <select name="category[]">
    	<option value=""></option>
    	<?php					
                      $resultcategory = mysql_query("SELECT * FROM category");
                      while ($rowcategory = mysql_fetch_array($resultcategory)){
    	?>
                     <option value="<?php echo $rowcategory['categoryname']; ?>"><?php echo $rowcategory['categoryname']; ?></option>
    	<?php
    	      }
    	?>
          </select>
    </td>
    
    <td>
             <select name="vendor[]">
    	 <option value=""></option>
    	<?php		
                        $resultvendor = mysql_query("SELECT * FROM vendor");
                        while ($rowvendor = mysql_fetch_array($resultvendor)){
    	?>
                            <option value="<?php echo $rowvendor['vendorname']; ?>"><?php echo $rowvendor['vendorname']; ?></option>
    	<?php
    	         }
    	?>
             </select>				
    </td>
    
    <td>
           <select name="product[]">
                 <option value=""></option>
    	<?php
                        $resultproduct = mysql_query("SELECT * FROM product");
                        while ($rowproduct = mysql_fetch_array($resultproduct)){
    	?>
                            <option value="<?php echo $rowproduct['productname']; ?>"><?php echo $rowproduct['productname']; ?></option>
    	<?php
    	        }
    	?>
           </select>				
    </td>
    </table>
    <input type="button" value="Add" onclick="addInput();">
    </html>

  • #7
    New Coder
    Join Date
    Dec 2012
    Posts
    22
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi,

    From post #6, I have managed to create the dependent dropdown list. However, it only works for the default 1st row, if I click "Add" button the codes are not working.

    Can someone please kindly help? Thanks,

    The edited codes shown below:

    Code:
    <script type="text/javascript">
    
    function addInput(){
    	var tbl = document.getElementById('tblAddress');
    	var lastRow = tbl.rows.length;
    	var row = tbl.rows[lastRow-1].cloneNode(true);
    	tbl.tBodies[0].appendChild(row);
    	var txts = row.getElementsByTagName('input');
    	for(var i = 0; i < txts.length; i++){
    		if(txts[i].type == "text") {
    			txts[i].value = "";
    		}
    	}
    }
    
    function showfield(vname){
    
           var myForm = document.forms.myform;
           var selbox1=myForm.elements["vendor[]"];
           var selbox2=myForm.elements["product[]"];
    	
           selbox1.options.length = 1;
           selbox2.options.length = 0;
    	
           if (vname == "") { 
                  selbox2.options[selbox2.options.length] = new Option('','');   
           } 
    		
          <?php
                  $catresult=mysql_query("SELECT * FROM category");
                  while ($catrow=mysql_fetch_array($catresult)){
           ?>
                      if(vname==<?php echo $catrow["id"]; ?>){
    					
           <?php
                         $catid=$catrow["id"];
                         $vendresult=mysql_query("SELECT * FROM vendor WHERE categoryid='$catid'");
                         while ($vendrow=mysql_fetch_array($vendresult)){
           ?>
                                  selbox1.options[selbox1.options.length] = new Option('<?php echo $vendrow["vendorname"]; ?>','<?php echo $vendrow["id"]; ?>');
    							
          <?php
                          }					
           ?>
    				
    	      }
          <?php
    	   }
          ?>		
    }
    
    function showfield1(pname){
           var myForm = document.forms.myform;
           var selbox=myForm.elements["product[]"];
    	
           selbox.options.length = 1;
    	
           <?php
    	$vendresult=mysql_query("SELECT * FROM vendor");
    	while ($vendrow=mysql_fetch_array($vendresult)){
           ?>
    	     if(pname==<?php echo $vendrow["id"]; ?>){
    						
    	        <?php
    		    $vendid=$vendrow["id"];
    		    $prodresult=mysql_query("SELECT * FROM product WHERE vendorid='$vendid'");
    		     while ($prodrow=mysql_fetch_array($prodresult)){
    	        ?>
    			selbox.options[selbox.options.length] = new Option('<?php echo $prodrow["productname"]; ?>','<?php echo $prodrow["id"]; ?>');
    								
    	       <?php
    		      }
                         ?>
    					
    	         }
            <?php
    	      }
            ?>
    }
    </script>
    
    <html>
    <body>
    
    <form name="form" method="post" action="" id="myform" >
    <table border="1" id="tblAddress">	
    <tr>	
    <td>
         <select name="category[]" onchange="showfield(this.options[this.selectedIndex].value)">
    	<option value=""></option>
    	<?php					
                      $resultcategory = mysql_query("SELECT * FROM category");
                      while ($rowcategory = mysql_fetch_array($resultcategory)){
    	?>
                     <option value="<?php echo $rowcategory['id']; ?>"><?php echo $rowcategory['categoryname']; ?></option>
    	<?php
    	      }
    	?>
          </select>
    </td>
    
    <td>
             <select name="vendor[]" onchange="showfield1(this.options[this.selectedIndex].value)">
    	 <option value=""></option>
             </select>				
    </td>
    
    <td>
           <select name="product[]">
                 <option value=""></option>
           </select>				
    </td>
    </tr>
    </table>
    <input type="button" value="Add" onclick="addInput();">
    </form>
    
    </body>
    </html>


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •