Booking system - update a value on one list from another

[focault]

Hi,
I have a list called Courses which holds the available courses that a user can sign up for.
Courses have an element called spaces that is a number - example 20 spaces.
When a user signs up for a course I would like the value of spaces to be -1 space.
How would I do this in the form?

 

[focault]

Also it needs to update a Community Builder field "my courses" with the course selected.

 

[rob]

Hi
You would need to do this via a PHP form plugin, set to run on new records only, something along these lines:

// signup___course_id is the form field which contains the course Id you are signing up for
// We cast it to an array as it could be a select list or multiple select list.
 
$courseIds = (array) $formModel->formData['signup___course_id'];
 
// Get the db and the query 
$db = JFactory::getDbo();
$query = $db->getQuery(true);
 
foreach ($courseIds as $courseId)
{
    // 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('courses')->set('spaces = spaces - 1')->where('id = ' . (int) $courseId);
    $db->setQuery($query);
    $db->execute();
}

 

[rob]

Also it needs to update a Community Builder field "my courses" with the course selected.

You should be able to use the same logic to update another table as well

 

[focault]

Ok I have a List called Subscriptions which uses a database join element to pull the course name to be selected and a cascading dropdown element to pull the spaces on that course.

Well I gave it a go with these settings - where the list name = oki1q_fab_courses and the element that needs updating = oki1q_fab_courses___Spaces. The php plugin is set to OnAfterProccess.
But I cannot get it to update the spaces on the course.

// signup___course_id is the form field which contains the course Id you are signing up for
// We cast it to an array as it could be a select list or multiple select list.

$courseIds = (array) $formModel->formData['oki1q_fab_courses___id'];

// Get the db and the query
$db = JFactory::getDbo();
$query = $db->getQuery(true);

foreach ($courseIds as $courseId)
{
// 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('oki1q_fab_courses')->set('oki1q_fab_courses___Spaces = oki1q_fab_courses___Spaces - 1')->where('id = ' . (int) $courseId);
$db->setQuery($query);
$db->execute();
}

 

[cheesegrits]

I just worked through the kinks on this one with him on Skype.

Can you post the final code, jst for anyone in the peanut gallery who is working on something similar.

-- hugh

 

[focault]

// signup___course_id is the form field which contains the course Id you are signing up for
// We cast it to an array as it could be a select list or multiple select list.

$courseIds = (array) $formModel->formData['Course_Select_raw'];


// Get the db and the query
$db = JFactory::getDbo();
$query = $db->getQuery(true);

foreach ($courseIds as $courseId)
{
// 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('oki1q_fab_courses')->set('Spaces = Spaces - 1')->where('id = ' . (int) $courseId);
$db->setQuery($query);
$db->execute();
}

 

[cheesegrits]

Just FYI, you get easier to read code if you use the "Code" button in the editor (the {} button), and select PHP. It preserves white space, and also avoids things getting interpreted as smilies.

-- hugh

 

[cheesegrits]

BTW, I'm assuming you don't allow editing or deleting of the signup forms, whereby the number of bookings on a course might change? The above obviously only handles the case of adding a new booking. If you then delete a booking, the spaces on the course would not get changed to reflect that.

That's why I typically recommend using a MySQL view to show a view of a "courses" (or whatever) table, where the available slot count (spaces, whatever you want to call them) is calculated on the fly, by joining and counting related rows on table(s) which "consume" those slots.

So the "courses" table then has a static number, "max_slots", and the view does something like ...

CREATE VIEW courses_with_slots AS SELECT courses.*, COUNT(bookings.*) AS booked_slots, courses.max_slots - COUNT(bookings.*) AS available_slots FROM courses LEFT JOIN bookings ON bookings.course_id = courses.course_id

NOTE - that's just an imaginary query as an example, it doesn't use your table names, etc.

That query also assumes each booking consumes one slot. If the bookings table allows for a variable number of slots ("Number of attendees:"), the query is a little more complicated, but the concept is the same. You aren't trying to micro-manage the available slot count as part of the form submission / edit / row deletion process, you are deriving it on the fly from the row data.

You can then add a second Fabrik list based on that view for display purposes, while using the original courses table for adding, editing and modifying course data.

-- hugh