Subtracting and updating data

41 Posts

Nick2687 Reply #1, 15 years, 1 month ago

I'm working on a project where I have created a form that will submit data to custom database table, using the method I learned from bob's guide here: http://bobsguides.com/custom-db-tables.html.

However I'm looking to do one additional thing with my form which is to have a number submitted from the form, and then I would like my snippet to query the database for a particular integer field, then subtract the number that was submitted from the form, from the field in the database, and finally update the field with the result.

so for example

database field = total hours
total hours = 20
form field = hours taken
hours taken = 5

submit form -> total hours - hours taken = 15
new total hours = 15

I'm just beginning to learn php, so any guidance would be appreciated.

863 Posts

Lucas Reply #2, 15 years, 1 month ago

Have you created a schema and classes for your custom table as described in the tutorial?

If you've managed that, your query could be something like this:

// your form data
$hours_taken = 5; 
$row_id = 6;

// retrieve the object of the row you are updating
$object = $modx->getObject('myClass', $row_id);  
// get Total Hours and adjust to new value
$new_hours = $object->get('total_hours') - $hours_taken;
$object->set('total_hours', $new_hours);
// don't forget to save!
$object->save();

41 Posts

Nick2687 Reply #3, 15 years, 1 month ago

Thanks so much!

A variation of that works perfectly.

41 Posts

Nick2687 Reply #4, 15 years, 1 month ago

A Follow up to this...

Can you use placeholders such as such as [[*pagetitle]] within a snippit?

for example I have this in a snippit

<?php
$path = MODX_CORE_PATH . 'components/tracking/';
$result = $modx->addPackage('tracking',$path . 'model/','pto_');

// Find the row in "Tracking" which includes the name within the page title
$ptos= $modx->getCollection('Tracking',array('name'=>'[[*pagetitle]]'));

// output the total hours accumulated  
foreach($ptos as $pto) {
  $output .= '<td>' . $pto->get('total_hours_aclum') . '</td>';
}
return $output;

This does not seem to work. If I replace the pagetitle placeholder with a name, it processes like it should, but I want it to grab the name from the page title, so that it is dynamic per each page, and I don't have to write 20 snippits, one for each page.

*edit: typo

863 Posts

Lucas Reply #5, 15 years, 1 month ago

No you can't do that, but what you can do is insert [ [*pagetitle]] into your snippet call within your template:

[[!mySnippet? &name=`[[*pagetitle]]`]]

Then in your snippet you can access the value of 'name' like this:

$name = $modx->getOption('name',$scriptProperties);

Also, I'm not sure how your data is structured but wouldn't it be better to search by resource ID instead of the page title?