Solved

auto-populate multiple select boxes

Posted on 2009-04-02
9
1,280 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
  • 4
  • 3
9 Comments
 
LVL 11

Expert Comment

by:AlexanderR
Comment Utility
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
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:wanderfuls
Comment Utility
thanks i'll check it and i'll reply :)
0
 

Author Comment

by:wanderfuls
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you AlexanderR.

We'll be waiting for a solution.

Best Regards
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Wordpress syntax error 1 26
PHP Script - Am I missing anything here? 8 26
Button Click 11 22
resizeing PHP image 2 20
Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
This article will show, step by step, how to integrate R code into a R Sweave document
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now