Subtract -1 from a field on Submit

Hi, I am on Joomla 1.5.26 and Chronoforms 3.2.

I am running a query to populate a select box according to a selection in a dropdown.

<select class="cf_inputbox" validation="required" type="text" id="location" name="location" size="10" > 
			<?php
if (!$mainframe->isSite() ) {return;}
$db =& JFactory::getDBO();
$query = "
SELECT `location`, `date`, `places_available`, `rel_2`
FROM `#__training_courses`
WHERE `course_name` = '3 Day Installer Training'
ORDER BY `course_name`;
";
$db->setQuery($query);
$options = $db->loadAssocList();
foreach ( $options as $o ) {
echo "<option value='".$o[date]." - ".$o[location]."' rel='".$o[rel_2]."'>".$o[date]." - ".$o[location]." - Places available: ".$o[places_available]."</option>";
}
?>
			</select>

The following query will populate the box with two values like this:

2012-07-27 - Dubai - Places available: 15
2012-06-25 - Edinburgh - Places available: 10

Then the user would select one of the two values that correspond to a unique course.

The table training_courses has fields of id, course_name, region, places_available, location, date, rel_1, rel_2

I would like to be able to subtract one place from the places_available field when the user submits the form.


What I did is I included id in the original query

<select class="cf_inputbox" validation="required" type="text" id="location" name="location" size="10" > 
			<?php
if (!$mainframe->isSite() ) {return;}
$db =& JFactory::getDBO();
$query = "
SELECT `id`, `location`, `date`, `places_available`, `rel_2`
FROM `#__training_courses`
WHERE `course_name` = '3 Day Installer Training'
ORDER BY `course_name`;
";
$db->setQuery($query);
$options = $db->loadAssocList();
foreach ( $options as $o ) {
echo "<option value='".$o[date]." - ".$o[location]."' rel='".$o[rel_2]."'>".$o[date]." - ".$o[location]." - Places available: ".$o[places_available]."</option>";
}
?>
			</select>

And on the On Submit code - before sending email: area I entered:

<?php
if (!$mainframe->isSite() ) {return;}
$db =& JFactory::getDBO();
$query = "
UPDATE `#__training_courses` SET `places_available` = `places_available`-1 WHERE `id` = '".$location->id."';
}
?>

That didn't work, though. Could you please help me get this through?

Thank you!

Hi quantum_leap,

In this code

<?php
if (!$mainframe->isSite() ) {return;}
$db =& JFactory::getDBO();
$query = "
UPDATE `#__training_courses` SET `places_available` = `places_available`-1 WHERE `id` = '".$location->id."';
}
?>

you also need to set and execute the query:

<?php
if (!$mainframe->isSite() ) {return;}
  $db =& JFactory::getDBO();
  $query = "
UPDATE `#__training_courses` SET `places_available` = `places_available`-1 WHERE `id` = '".$location->id."';
  $db->setQuery($query);
  $db->query();
}
?>

Bob

Still doesn't work, Bob! Do I need to have a DB connection active with the table I am going to be updating, by the way? When I do, it is just creating a new record entry and not updating the existing field.

If I remove the if statement bit and set a certain number for id (eg, 1, 2 or 3 that corresponds to the course id) it works fine. The problem is that I don't know how to pass this variable to the MySQL query on the onSubmit action according to the value of the id that has been selected from the dropdown.
So

<?php
      $db =& JFactory::getDBO();
      $query = "
    UPDATE `#__training_courses` 
    SET `places_available` = `places_available`-1 
    WHERE `id` = '1';
    ";
      $db->setQuery($query);
      $db->query();
    ?>

works fine but I want to have something like

WHERE `id` = 'id selected from form';

Please help! Thanks

Sorted. For anyone interested the correct code is

<?php
  $db =& JFactory::getDBO();
  $query = "
  UPDATE `#__training_courses` 
  SET `places_available` = `places_available`-1 
  WHERE `id` = '".$_POST['course_id']."'; ";
  $db->setQuery($query);
  $db->query();
?>