Solved

auto-populate multiple select boxes

Posted on 2009-04-02
9
1,296 Views
Last Modified: 2012-05-06
Hi everyone,

I made an application that have the following:
Master Categories
Categories
Products

The user is able to pick a Master Category and a dynamic page of Products (drop down list)  under their Categories shows up in the browser. There are configurations for each product that is assign by the administrator of the site. The user picks product 2 from category 1 and based on the configuration(s) of that product the next category change accordingly and show only the product(s) with the same configuration(s). I manage to make this application to work but with static data using multiple steps and forms but that way every time the client need to add a master category->categories->products, I have to create new steps with forms for the new master category->categories->products. So the clients want it dynamic and that leaves me with only one option, to use ajax that i'm not very familiar with. I have found a ajax code and trying to adapt it to the current application but without luck.

The trick to get the configuration of the product select and use it to get the products based on that configuration from the next category.
But it does not works as indented and I cant think anything more. Can someone please help?
The ajax code is using jquery 1.1.1 and is as follows:
select-chain.js
 
(function ($) {
    $.fn.selectChain = function (options) {
        var defaults = {
            key: "id",
            value: "label"
        };
        
        var settings = $.extend({}, defaults, options);
        
        if (!(settings.target instanceof $)) settings.target = $(settings.target);
        
        return this.each(function () {
            var $$ = $(this);
            
            $$.change(function () {
                var data = null;
                if (typeof settings.data == 'string') {
                    data = settings.data + '&' + this.name + '=' + $$.val();
                } else if (typeof settings.data == 'object') {
                    data = settings.data;
                    data[this.name] = $$.val();
                }
                
                settings.target.empty();
                
                $.ajax({
                    url: settings.url,
                    data: data,
                    type: (settings.type || 'get'),
                    dataType: 'json',
                    success: function (j) {
                        var options = [], i = 0, o = null;
                        
                        for (i = 0; i < j.length; i++) {
                            // required to get around IE bug (http://support.microsoft.com/?scid=kb%3Ben-us%3B276228)
                            o = document.createElement("OPTION");
                            o.value = typeof j[i] == 'object' ? j[i][settings.key] : j[i];
                            o.text = typeof j[i] == 'object' ? j[i][settings.value] : j[i];
                            settings.target.get(0).options[i] = o;
                        }
 
                        // hand control back to browser for a moment
                        setTimeout(function () {
                            settings.target
                                .find('option:first')
                                .attr('selected', 'selected')
                                .parent('select')
                                .trigger('change');
                        }, 0);
                    },
                    error: function (xhr, desc, er) {
                        // add whatever debug you want here.
                        alert("an error occurred");
                    }
                });
            });
        });
    };
})(jQuery);
 
 
the page that show the categories and the products
show_all.php
    <script type="text/javascript">
    <!--
    $(function () {
        <?php 
        	$queryA = $this->db->query('select * from categories where mastcat_id='.$_POST['mastcat_id'].'');
		
        	$totalCats =  $queryA->num_rows();
        	$i = 1;
			while ( $i < $totalCats)
			{
				echo "var cat$i = $('#cat$i');\n\t\t";
				$i++;
			}
        ?>        	
        <?php 
        	$i = 1;
        	$e = $i+1;
			while ( $i < $totalCats)
			{
		       	echo "cat$i.selectChain({
		            target: cat$e,
		            url: 'http://localhost/micro/index.php/micro/get_data',
		            data: { ajax: true, mastcat_id: \"$mastcat_id\"  }            
		        });";
		       	$i++;
		       	$e++;     
			}
			
        	echo "// note that we're assigning in reverse order
	        //to allow the chaining change trigger to work
	        cat1.selectChain({
	            target: cat2,
	            url: 'http://localhost/micro/index.php/micro/get_data',
	            data: { ajax: true, mastcat_id: \"$mastcat_id\" }
	        }).trigger('change');";
      ?>
        
    });
    //-->
    </script>
 
	foreach ($queryA->result() as $row3)
	{
		echo "<br><b>$row3->desc</b><br>\n";
		echo "<SELECT id=\"cat$i\" name=\"category\">";
		$i++;
		// get category products
		$queryB = $this->db->query('select products.id, products.desc, products.cat_id from products where mastcat_id='.$_POST['mastcat_id'].' and products.cat_id='.$row3->id.'');
			foreach ($queryB->result() as $row4){
				#echo "<option></option>";
				echo "<option value=\"$row4->id\">$row4->desc</option><br>\n";
				// get products configurations 
		
			}
		echo "</SELECT>";			
				$queryC = $this->db->query("SELECT * FROM products_config where id='$row4->id'");
					foreach ($queryC->result() as $row5)
					{
						echo "<input type=\"hidden\" name=\"$row4->id[]\" value=\"$row5->cid\">\n";
					}
		
	}
 
 
 
The file that is called by show_all.php
 
get_data.php
<?php
session_start(); 
$_SESSION['mastcat_id'] = $_GET['mastcat_id'];
 
if (@$_REQUEST['ajax']) {
	$results = $this->db->query('SELECT * FROM products WHERE mastcat_id='.	$_GET['mastcat_id'].' and products.id='.$_REQUEST['category'] .'');        
        $json = array();
        
		foreach ($results->result() as $row)
		{
			$json[] = '{"id" : "' . $row->cid . '", "label" : "' . $row->desc . '"}';
            $json[] = '"' . $row->cid . '"';
        }
        
        echo '[' . implode(',', $json) . ']';
        die(); // filthy exit, but does fine for our example.
 
 
?>
 
Mysql is as follows:
 
This table holds the categories.
Each category have a unique id, 
the master category it belongs to 
and the category description.
 
categories
id(unique)	mastcat_id	desc
1	2		category1
2	2		category2
3	2		category3
4	3		category4
5	3		category5
6	3		category6
 
This table holds the configurations
Each configuration have a unique id
and the configuration description.
 
configurations
id(unique)	desc
4	None
5	Config1
6	Config2
7	Config3
8	Config4
9	Config5
10	Config6
11	Config7
 
 
This tables holds master categories
Each master category have a unique id
and the master category description.
 
master_categories
mastcat_id(unique)	mastcat_desc
1		None
2		MastCat1
3		MastCat2
4		MastCat3
5		MastCat4
6		MastCat5
 
This table holds the products
Each product have a unique id, 
the product description
the category it belongs to 
and the master category it belongs to.
 
products
id(unique)	desc		cat_id	mastcat_id
1	Description	1	2
2	Description	1	2
3	Description	2	2
4	Description	2	2
5	Description	3	2
6	Description	3	2
7	Description	4	3
8	Description	4	3
9	Description	5	3
10	Description	5	3
11	Description	6	3
 
This tables holds the products and the configurations of each product
The field id is the product id and it's not unique,
a product will have more 2,3 or more configurations attached to it.
The field cid is the configuration id of the product
 
products_config
id	cid
1	4
1	5
1	6
2	7
2	7
2	8
2	9
3	4
3	7
3	9
3	5
4	4
4	5
4	6
4	10

Open in new window

0
Comment
Question by:wanderfuls
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
9 Comments
 
LVL 11

Expert Comment

by:AlexanderR
ID: 24108176
When i select a product from first drop down where first category is, does second drop down need to display
1.  ALL products with configurations similar to selected one in the first drop down
OR
2. products with similar configuration to selected one that were a part of category2
??
0
 

Author Comment

by:wanderfuls
ID: 24113925
Hi AlexanderR,

Thank you for reply, It needs to display products with similar configuration to selected one that were a part of category2 and so on, it's category it's dependant to the previous selected product configuration.

0
 
LVL 11

Accepted Solution

by:
AlexanderR earned 500 total points
ID: 24120494
See if this is anywhere near what you need.

Included 3 files.
FILE-----formHandler.js
 
var xmlHttp = createXmlHttpRequestObject();
 
function createXmlHttpRequestObject() {
   var xmlHttp;
   try {
      xmlHttp = new XMLHttpRequest();
   } catch(e) {
      xmlHttp = false;
   }
   if(!xmlHttp) {
      alert("Error creating the xmlHttpRequest object");
   } else {
      return xmlHttp;
   }
}
 
function getMaster() {
   if(xmlHttp) {
      try{
         xmlHttp.open("GET", "formEvents.php?action=getmaster", true);
         xmlHttp.onreadystatechange = handleMasterResponse;
         xmlHttp.send(null);
      } catch(e) {
         alert("Can't connect to server:\n"+e.toString());
      }
   } else {
      alert("problem connecting to formEvents.php")
   }
}
 
function handleMasterResponse() {
   if(xmlHttp.readyState == 4) {
      if(xmlHttp.status == 200) {
         try {
	    var masterHTML = '<select name="masterCats" onChange="requestCategories();"><option value="0">Choose Master</option>'+
			      xmlHttp.responseText +
			      '</select>';
            document.getElementById('masterSelect').innerHTML = masterHTML
         } catch(e) {
            alert("Error reading the response: "+e.toString());
         }
      } else {
         alert("There was a problem retrieving the data:\n" + xmlHttp.statusText);
      }
   }
}
 
function requestCategories() {
   var masterCatId = document.products.masterCats.options[document.products.masterCats.selectedIndex].value;
   if(xmlHttp) {
      try{
         xmlHttp.open("GET", "formEvents.php?action=getcategories&id="+masterCatId, true);
         xmlHttp.onreadystatechange = handleCategoriesResponse;
         xmlHttp.send(null);
      } catch(e) {
         alert("Can't connect to server:\n"+e.toString());
      }
   } else {
      alert("problem connecting to formEvents.php")
   }
}
 
function handleCategoriesResponse() {
   if(xmlHttp.readyState == 4) {
      if(xmlHttp.status == 200) {
         try {
	    var categoriesHTML = xmlHttp.responseText;
            document.getElementById('categories').innerHTML = categoriesHTML
         } catch(e) {
            alert("Error reading the response: "+e.toString());
         }
      } else {
         alert("There was a problem retrieving the data:\n" + xmlHttp.statusText);
      }
   }
}
 
function requestProducts(cat) {
   var masterCatId = document.products.masterCats.options[document.products.masterCats.selectedIndex].value;
   var productId = document.getElementById('category'+cat).options[document.getElementById('category'+cat).selectedIndex].value;
   if(xmlHttp) {
      try{
         xmlHttp.open("GET", "formEvents.php?action=getproducts&prod_id="+productId+"&cat_id="+(cat+1)+"&mast_cat_id="+masterCatId, true);
         xmlHttp.onreadystatechange = function() {
	    handleProductsResponse(cat);
	 };
         xmlHttp.send(null);
      } catch(e) {
         alert("Can't connect to server:\n"+e.toString());
      }
   } else {
      alert("problem connecting to formEvents.php")
   }
}
 
function handleProductsResponse(cat) {
   if(xmlHttp.readyState == 4) {
      if(xmlHttp.status == 200) {
         try {
	    var nextCat = document.getElementById('category'+(cat+1));
	    for(var i = nextCat.options.length - 1; i >= 0; i--) {
	       nextCat.remove(i);
	    }
	    var productsSelectBox = xmlHttp.responseText;
            eval(productsSelectBox);
         } catch(e) {
            alert("Error reading the response: "+e.toString());
         }
      } else {
         alert("There was a problem retrieving the data:\n" + xmlHttp.statusText);
      }
   }
}
 
 
 
FILE    form.htm
<html>
<head>
<script type="text/javascript" src="formHandler.js"></script>
</head>
<body onload="getMaster();">
   <form name="products">
      <div id="masterSelect"></div>
      <br><br>
      <div id="categories"></div>
   </form>
</body>
</html>
 
 
 
FILE formEvents.php
 
<?php
$dbcon = mysqli_connect('host', 'user', 'pass', 'db');
 
 
function getMaster() {
   global $dbcon;
   $sqlSelectMasterCats = "SELECT * FROM master_categories";
   $rsSelectMasterCats = mysqli_query($dbcon, $sqlSelectMasterCats);
   while($rwMasterCats = mysqli_fetch_assoc($rsSelectMasterCats)) {
      echo '<option value="'.$rwMasterCats['mastcat_id'].'">'.$rwMasterCats['mastcat_desc'].'</option>';
   }
}
 
function getCategories($master_id) {
   global $dbcon;
   $sqlSelectCatsAndProducts = "SELECT products.id AS product_id,
				       products.desc AS product_desc,
				       categories.id AS cat_id,
				       categories.desc AS cat_desc
				       FROM products, categories
				       WHERE categories.mastcat_id = ".$master_id.
					  " AND products.cat_id = categories.id";
   $rsSelectCatsAndProducts = mysqli_query($dbcon, $sqlSelectCatsAndProducts);
   $categories = array();
   while($rwCatsAndProducts = mysqli_fetch_assoc($rsSelectCatsAndProducts)) {
      $categories[$rwCatsAndProducts['cat_id']][0]['product_desc'] = $rwCatsAndProducts['cat_desc'];
      $categories[$rwCatsAndProducts['cat_id']][0]['product_id'] = 0;
      $categories[$rwCatsAndProducts['cat_id']][$rwCatsAndProducts['product_id']]['product_desc'] = $rwCatsAndProducts['product_desc'];
      $categories[$rwCatsAndProducts['cat_id']][$rwCatsAndProducts['product_id']]['product_id'] = $rwCatsAndProducts['product_id'];
  } 
  foreach($categories as $cat_id => $value){
      echo '<select id="category'.$cat_id.'" onChange="requestProducts('.$cat_id.');">';
      foreach($value as $key => $product){
	 echo '<option value="'.$product['product_id'].'">'.$product['product_desc'].'</option>';
      }
      echo '</select>';
  }
}
 
function getProducts($productId, $categoryId, $masterCatId){
  global $dbcon;
  $sqlSelectProducts = 'SELECT DISTINCT products_config.id AS id,
					products.desc AS description
			FROM products_config,
			     products
			WHERE cid IN (SELECT products_config.cid
				      FROM products_config
				      WHERE products_config.id = '.$productId.')
			   AND products.cat_id = '.$categoryId.'
			   AND products.mastcat_id = '.$masterCatId.'
			   AND products.id = products_config.id';
   $rsSelectProducts = mysqli_query($dbcon, $sqlSelectProducts);
   $productsSelectBox = '';
   $i=0;
   while($rsProducts = mysqli_fetch_assoc($rsSelectProducts)){
      $productsSelectBox .= 'var optn'.$i.' = document.createElement("option");'.
			    'optn'.$i.'.text = "'.$rsProducts['description'].'";'.
			    'optn'.$i.'.value = "'.$rsProducts['id'].'";'.
			    'document.getElementById("category'.$categoryId.'").options.add(optn'.$i.');';
      $i++;
  }
  echo $productsSelectBox;
}
 
switch ($_GET['action']) {
   case 'getmaster':
      getMaster();
      break;
   case 'getcategories':
      $master_id = mysqli_real_escape_string($dbcon, $_GET['id']);
      getCategories($master_id);
      break;
   case 'getproducts':
      $product_id = mysqli_real_escape_string($dbcon, $_GET['prod_id']);
      $category_id = mysqli_real_escape_string($dbcon, $_GET['cat_id']);
      $master_cat_id = mysqli_real_escape_string($dbcon, $_GET['mast_cat_id']);
      getProducts($product_id, $category_id, $master_cat_id);
      break;
}

Open in new window

0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

Author Comment

by:wanderfuls
ID: 24136435
thanks i'll check it and i'll reply :)
0
 

Author Comment

by:wanderfuls
ID: 24203642
Hi AlexanderR,

Yes it works as intended but there is an addition to be added that I forgot to mention at the primary question.
A product might have for example the configurations 1,3,5. When you select it the program will try to get products from the next category that have configurations 1,3, or 5, but if it doesn't find a product with those configurations it will display a javascript alert. What it has to be done is to display a text in the <option></option> tag stating "No available product for this category"  and skip to the next category in list and try to get products with configurations like 1,3,5 and so on.

Thanks a lot :)
0
 
LVL 11

Expert Comment

by:AlexanderR
ID: 24432812
sorry, i was not able to work on experts-exchange past few weeks.  I should have time this weekend to complete the task.
Thank you for the points at this time, but if you are willing i will resume work on this to give you a full solution.
0
 

Author Comment

by:wanderfuls
ID: 24438576
Thank you AlexanderR.

We'll be waiting for a solution.

Best Regards
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with mod_substitute 18 35
Wordpress and Wufoo 1 37
MySQL-Design Help 12 39
Difference between PHPClasse.php and SimplXLS.php 5 17
In threads here at EE, each comment has a unique Identifier (ID). It is easy to get the full path for an ID via the right-click context menu. However, we often want to post a short link within a thread rather than the full link. This article shows a…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The viewer will learn how to count occurrences of each item in an array.
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question