OK so now i did change the structure to have the id of SKU, so now no matter how many changes are made to the name of the sku, everythings still works like a charm... this is the code i use so far in the Advanced tab of a dropdown element...
$codeproduit='{rapport_production_salles_blanches___codeproduit_raw}';
$db = JFactory::getDbo();
$db->setQuery("SELECT skucode FROM listesku WHERE listesku.id='$codeproduit'");
$skuid = $db->loadResult();
$db = JFactory::getDbo();
$db->setQuery("SELECT bouchonsoption1 FROM listesku WHERE listesku.skucode='$skuid'");
$rows1 = $db->loadResult();
$db = JFactory::getDbo();
$db->setQuery("SELECT bouchonsoption2 FROM listesku WHERE listesku.skucode='$skuid'");
$rows2 = $db->loadResult();
$db = JFactory::getDbo();
$db->setQuery("SELECT CONCAT(codempbouchons, ' ',description) FROM liste_mp_bouchons WHERE id='$rows1'");
$name1 = $db->loadResult();
$db = JFactory::getDbo();
$db->setQuery("SELECT CONCAT(codempbouchons, ' ',description) FROM liste_mp_bouchons WHERE id='$rows2'");
$name2 = $db->loadResult();
$options[] = JHTML::_('select.option', '0', 'Veuillez choisir' );
$options[] = JHTML::_('select.option', '1', $name1);
$options[] = JHTML::_('select.option', '2', $name2);
return $options;
i know this code can be optimized to remove a couple of lines, if you have suggestion.... but it's working.... so the only thing missing is the Ajax call to change when employee choose the codeproduit element... so when i load the form, i have option 0, 1, 2...when they hit Apply to save the form once, i have the right options like i want them, and they now reflects the real value in my listsku table... so is their a way to turn them into an Ajax driven options ?
rodeokid
Without writing your own custom AJAX handling, there isn't a way.
I'm trying to help you avoid a mistake in database design which I can absolutely guarantee will come back to bite you on your ass in the future. You obviously don't have to take my advice, but keep in mind I've been building databases for people for several decades.
If you don't want different SKUs, then add two extra tables, something like 'options' and 'package', where 'package' defines the product SKU + option.
(note that I'm adding supplier_sku in these examples, you haven't mentioned it, but I assume you have them in you product tables)
suppliers
id, name
1, Acme Bottles
2, Bottle Tops R Us
3, Global Bottle Tops, Inc
products:
id, sku, description, supplier_id, supplier_sku
1, sku123, small bottle, 1, ab1
2, sku124, big bottle, 1, ab2
options
id, name, supplier_id, supplier_sku
1, big bottle top, 2, btru1
2, big bottle top, 3, gbt1
3, small bottle top, 2, btru2
4, small bottle top, 3, gbtr2
packages
id, product_id, option_id
1, 1, 3
2, 1, 4
3, 2, 1
4, 2, 2
So "package" 2 is a "small bottle" from "Acme Bottles", their SKU ab1) with "small bottle top" from "Global Bottle Tops, Inc" (their SKU gbtr2).
So on your form, you have a "Product" join, and a "Package" (or call it "Product Options" or whatever) CDD. They select the product, the package CDD then shows the options for that product (two different tops). You'll need a CONCAT label to grab the option name from the options table.
This is called "normalizing" your data, and is an absolutely essential part of database design.
In your example of needing to do a product recall, a simple query will find all "packages" that use the affected option_id, given a supplier SKU.
It is also future proofed, so if in the future you add more options, it's just adding rows to tables, not modifying PHP code.
Hugh
Sent from my HTC6545LVW using Tapatalk