Simple calc on submision using seperate table

[paulknox]

I've been working my way through the wiki and am banging my head on this simple issue. I have a form that on submission I want it to reduce a stock number in a separate list.

The 'stock' list has 'lens_model' and 'stock_level' and the submitting form 'iol' has 'lens_model' the lens models are radio buttons.

The following code reduces the number of ALL lens models

$stock_lens_model = (array) $formModel->formData['iol___lens_model'];

// Get the db and the query
$db = JFactory::getDbo();
$query = $db->getQuery(true);
{
// Clear down any previous query
$query->clear();

// Update the query to decrease the value contained in the field "spaces" by 1 for the current course id.
$query->update('stock_levels')->set('stock_level = stock_level - 1')->where('lens_model ='. (int) $stock_lens_model);
$db->setQuery($query);
$db->execute();
}

So I tried using the raw and nothing happens..

$radio1 = (int) $formModel->formData['iol___lens_model_raw'];
// Get the db and the query
$db = JFactory::getDbo();
$query = $db->getQuery(true);
{
// Clear down any previous query
$query->clear();

// Update the query to decrease the value contained in the field "spaces" by 1 for the current course id.
$query->update('stock_levels')->set('stock_level = stock_level - 1')->where('lens_model ='. (int) $radio1);
$db->setQuery($query);
$db->execute();
}

Any suggestions would be appreciates!

 

[troester]

Install https://extensions.joomla.org/extension/j-dump/
and debug what you get in your elements and queries.

 

[paulknox]

Thanks.. installed and it just returns

• 
  
  [string] My SQL query to read users = "
  UPDATE stock_levels
  SET stock_level = stock_level - 1
  WHERE lens_model =0"

OR

• 
  
  [string] My SQL query to read users = "
  UPDATE stock_levels
  SET stock_level = stock_level - 1
  WHERE lens_model =1"

I was expecting the WHERE lens_model to be returning a text string such as 'Tecnis PCB00' as thats one of the button values

 

[cheesegrits]

What element type is lens_model? I would expect it to be a join to a 'lens_models" table, using the primary key ('id') as the value, and model name as the label. And same on the stock table. And you would always deal with "raw" values.

Also, for stock control, I always find it better to calculate stock from first principles on each transaction submission. In other words, rather than subtracting the number of units from this transaction in the stock table, recount all the transactions. I'm on my phone, so typing code is a pain, but in pseudo code, "update stock, set level = (sum of all restock transaction for item - sum of all sale transactions for item)"

Might not be the way to go if you have millions of transactions, but for thousands, it's less error prone.

Hugh


Sent from my HTC6545LVW using Tapatalk