Solved

auto-populate multiple select boxes

Posted on 2009-04-02
9
1,300 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

WordPress Tutorial 2: Terminology

An important part of learning any new piece of software is understanding the terminology it uses. Thankfully WordPress uses fairly simple names for everything that make it easy to start using the software.

Question has a verified solution.

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

A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

623 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