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';
}
}
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..
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>
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.
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")
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>
Before you post, read our: 
