Look up element's value dependent on 2 conditions

[fudge4u]

I have a list called 'printing' which includes the elements 'item', 'quantity', 'price' This is the list which my customer populates when filling out a form. The element 'quantity' is a drop-down calc element which looks up the possible quantities that the customer can choose from in my next list.

This next list called 'quantities' has the elements 'item', 'quantity', 'price' This is my internal list which the customer does not have direct access to - but I use it to look up data.

I want the {printing___price} to be a calc element to look up the item's price in the 'quantities' list when {printing___item} = {quantities___item} AND {printing___quantity} = {quantities___quantity}

The 'quantities' list is set up like:
ITEM1, 100, 20.00
ITEM1, 250, 30.00
ITEM1, 500, 45.00
ITEM2, 100, 25.00
ITEM2, 500, 50.00

So, for instance, when the customer fills out the 'printing' form and selects ITEM2 with a quantity of 100, the price will show $25.00 on the form.

I've tried to figure out the PHP code for the price calc element, but have been unsuccessful trying to use the AND clause.

 

[fudge4u]

So this is what I tried - but whenever I try to put an AND line in there, it does not return the data. Is it possible to select a record based on 2 conditions? It does return a value if I have either of the ITEM or QUANTITY lines in the WHERE clause, but not if I have both.

$ret = '';
$db = JFactory::getDBO();
$query = "SELECT price
FROM quantities
WHERE item = " . $db->Quote('{printing___item}')
AND quantity = " . $db->Quote('{printing___quantity}');
$db->setQuery($query);
$ret = $db->loadResult();
return $ret;

 

[cheesegrits]

Have you tried dumping the data?

I suspect you may need to use a _raw placeholder.

Try doing ...

var_dump((string)$query);exit;

After calling setQuery, and see what it is set to.

if you are doing this in AJAX on the page, you'll need to open dev tools, network tab, and look at the response from the AJAX call fired off when calc triggers.

-- hugh

 

[fudge4u]

I added the line and opened the dev tools in the web browser, but I see no reference to the ajax call - not exactly sure what I am looking for. The ajax is set to YES in the calc element.

 

[cheesegrits]

In the Network tab. When you do anything in the form to trigger the calc you should see a POST fire off.

Sent from my HTC One using Tapatalk

 

[fudge4u]

The POST for the price element's ID, in the Params, the printing___price = "", as well as the printing___raw element. The response pane is blank.

 

[fudge4u]

After a bunch of experimenting, I ended up with this code, which solved my issue:

$ret = '';
$db = JFactory::getDBO();
$query = "SELECT price
FROM quantities
WHERE quantity = '{printing___quantity}' AND item = '{printing___item}'";
$db->setQuery($query);
$ret = $db->loadResult();
return $ret;