[SOLVED] Multiple Dropdown selection with MySQL results

[avvllvva]

In my product adding page, there is one dropdown for adding categories (one product may have multiple categories so I made it multiple selection).

 

I'm using same page for editing, so at this time I have to make default selection for the categories which were chosen at the time of add.

 

For achieving this i tried following code

 

<?php 
// tbl_products -> product details
// tbl_map_product_category -> mapping of product table with category table (2 fields category_id & product_id)
// $pro_id -> variable that contains the id of  product to be edit

$qrystr = "SELECT 
			p.*, (SELECT category_id FROM tbl_map_product_category WHERE product_id='$pro_id') as p_id
	   FROM 
			tbl_products p 
	  ";
$qry = mysql_query($qrystr);
?>				
<select multiple="multiple" >
		<?php
		         while($row = mysql_fetch_array($qry)) {?>
                                         <option <?php if($row['id']==$row['p_id']){?> selected="selected"<?php } ?>  >
                                              <?php echo $row['title'] ?>
                                          </option>
                                    <?php
		 	  } ?>
</select>				

 

and this is working only when we chose sinlge category, for multiple categories it fails, because at that time the subquery[(SELECT category_id FROM tbl_map_product_category WHERE product_id='$pro_id') as p_id] returns multiple rows.

 

 

Is there any other method to achieve this OR how to modify this code ??

Thanks in advance for any help.

 

 

 

[avvllvva]

If u don't understood what I meant, simply  the same in joomla administration. At there u can see different multiple-selection-dropdowns(eg:-list of menus), and at the time of editions previously selected items are automatically selected. This is exactly I'm looking for.... please help. 

[avvllvva]

I'm extremely sorry for my mistake..

It was a typo,  instead of product table  it is actually the category table.

 

So please look at new code,  discard the first script. Now its also  more readable

 

<?php 
// tbl_category -> category details
// tbl_map_product_category -> mapping of product table with category table (2 fields category_id & product_id)
// $pro_id -> variable that contains the id of  product to be edit

$qrystr = "SELECT c.*, (SELECT category_id FROM tbl_map_product_category WHERE product_id='$pro_id') as category_id
       FROM tbl_category c 
	  ";
$qry = mysql_query($qrystr);


echo '<select multiple="multiple" >';	// dynamic dropdown for category with multiple selection		
	while($row = mysql_fetch_array($qry)) {
		if($row['id']==$row['category_id']) // comparing current category id with existing category id in the mapping table        
		  $selection  = 'selected="selected"';
		else
		  $selection  = '';

	   echo '<option '.$selection.' >'.$row['title'].'</option>'; 
	} 
echo '</select>';	
?>

 

 

Usually how, you guys are handling this situation?, please let me know....

 

[avvllvva]

Okay then, I think nobody understood my problem.... maybe of my english.

 

So for now........

 

can anybody tell, how to do a simple multiple dropdown selection dynamically ??

[cags]

I can't help you with your problem but I think I can clarify it for other people. You wish to populate a dropdown box based on the value selected in another dropdown box. So if for example your site was an e-commerce site, you might select manufacturer in one box and it would automatically populate a second box with the products stored in the database for that manufacturer.

 

The only thing I can tell you, is if you wish to do it without the user submitting the form, you will require AJAX and as such may get more help in the AJAX forum.

[avvllvva]

Hi Cags, thanks for your reply.

 

But.... what u explained is not my requirement, its entriely different, you might be misunderstood.

thnx again for ur response.

 

Anyway I will try to explain it more clearer.

 

There is only one dropdown( but multiple selection is enabled).

 

Here I have 2 actions

Action 1 : Add a product

          Here along with product details , it saves the category choosen from dropdown

          (one or      more categories).

Action 2 : Edit a product

            Here ,  offcourse.. i have to display the dropdown for changing the category

            (i'm using the same dropdown which is used in adding page). And at this time I have

              to show them the previously selected categories.

      so i've put something like default selection for these selected categories inside

              the dropdown, so he can identify the previously selected categories.

                For this I have tried dropdown property -'selected="selected"'.

 

This is my basic need.

 

But here I'm used a single mysql query for doing it, datas from category table and datas from category-product mapping table (for previously selected categories for that product)

I hope this will give u a clear idea.

 

If there any confusion with my db tables & query, plz let me know , I can explain.

 

 

But I'm so wondering about nobody before gone through this type of requirement ???  is this the first time ???? But How JOOMLA handled this situation  ???

I hope somebody can help me........             

[avvllvva]

Finally it gets solved.

 

Credit belongs to Kickstart (http://www.phpfreaks.com/forums/index.php/topic,271779.0.html).

 

Final code is

<?php 
// tbl_category -> category details
// tbl_map_product_category -> mapping of product table with category table (2 fields category_id & product_id)
// $pro_id -> variable that contains the id of  product to be edit

$qrystr = "SELECT c.*, mpc.category_id as category_id
       FROM tbl_category c 
	   LEFT OUTER JOIN tbl_map_product_category mpc
           ON c.id = mpc.category_id AND mpc.category_id = '$pro_id'
	  ";
$qry = mysql_query($qrystr);


echo '<select multiple="multiple" >';	// dynamic dropdown for category with multiple selection		
	while($row = mysql_fetch_array($qry)) {
		if($row['id']==$row['category_id']) // comparing current category id with existing category id in the mapping table        
		  $selection  = 'selected="selected"';
		else
		  $selection  = '';

	   echo '<option '.$selection.' >'.$row['title'].'</option>'; 
	} 
echo '</select>';	
?>