Event reservation application in article. Delete record.

This is a follow up from a (rather off-topic) post I made in another thread.

I am trying to make a small event-reservation application form to be placed in an article with use of the 'chronocontact content plugin'.

Therefor I made a form with:
1. A 'name' field witch is 'read only' and will automatically be filled in with the users full-name (only site members can make reservations)
2. A drop-down selection with the options '1' and '2' (for one or the people on the reservation)
3. A 'submit' button.

I connected the form to a database table named 'jos_chronoforms_Reservatie'

Scenario 1: A logged in site member has no reservation yet.
He goes to the article (with event info) and sees the reservation form with his full name in the 'name' field. He selects '1' or '2' people on his reservation and hits the 'add reservation' button. A record will be created in the database table 'jos_chronoforms_Reservatie' with a field containing his full name and one with a number '1' or '2'.

Scenario 2: A logged in member already has a reservation for the event.
He goes to the article (with event info) and sees a message 'You allready have a reservation for this event' and no form.

This works with this form code:

<?php
$my = JFactory::getUser();
?>
<?php
$user =& JFactory::getUser();
$db =& JFactory::getDBO();
$query = "
  SELECT count(*)
    FROM `jos_chronoforms_Reservatie`
    WHERE `cf_user_id` = ".$db->Quote($user->id).";
  ";
$db->setQuery($query);
if ( $db->loadResult() ) {
	echo "You allready have a reservation for this event";
}
else {
	echo "<input class='cf_inputbox required' title='' id='naam' name='naam' type='text' value='".$my->name."' readonly='readonly' />
			<select class='cf_inputbox validate-selection' id='select_1' size='1' title=''  name='aantal'>
				<option value=''>Choose Option</option>
				<option value='1'>1</option>
				<option value='2'>2</option>
			</select>
			<input value='ad reservation' name='aduser' type='submit' />";
}
?> 

Now, it would be nice to ad a feature the the user (with reservation) could undo his reservation by having a 'delete reservation' button in 'scenario 2'

With help from Bob I added a submit button with a value 'delete reservation' and a hidden field named ' remuser' and a value of the user id.

This makes the code like this:

<?php
$my = JFactory::getUser();
?>
<?php
$user =& JFactory::getUser();
$db =& JFactory::getDBO();
$query = "
  SELECT count(*)
    FROM `jos_chronoforms_Reservatie`
    WHERE `cf_user_id` = ".$db->Quote($user->id).";
  ";
$db->setQuery($query);
if ( $db->loadResult() ) {
	echo "You allready have a reservation for this event";
	echo "<input value='delete reservation' name='delete' id='delete' type='submit' />";
	echo "<input type='hidden' id='remuser' name='remuser' value='".$my->id ."' />";
}
else {
	echo "<input class='cf_inputbox required' title='' id='naam' name='naam' type='text' value='".$my->name."' readonly='readonly' />
			<select class='cf_inputbox validate-selection' id='select_1' size='1' title=''  name='aantal'>
				<option value=''>Choose Option</option>
				<option value='1'>1</option>
				<option value='2'>2</option>
			</select>
			<input value='ad reservation' name='aduser' type='submit' />";
}
?> 

With the help from a friend I've managed to come up with this to ad at 'On Submit code - before sending email:' (at form adminisration).

<?php
$submit = JRequest::getString('submit', '', 'post');
if ( $submit == 'delete reservation' ) {
  mysql_query("DELETE FROM `jos_chronoforms_Reservatie`
    WHERE `cf_user_id` = '{$_POST["remuser"]}'");
} else {
// do something else
}
?>

Now, when the user clicks the 'delete reservation' button it does not delete the users record but a new record will be created with no name and reservation amount but with the same 'cf_user_id' as the logged in user.

This is the generated bug report:
1. Form passed first SPAM check OK
2. Form passed the submissions limit (if enabled) OK
3. Form passed the Image verification (if enabled) OK
4. Form passed the server side validation (if enabled) OK
5. $_POST Array: Array ( [remof] => delete reservation [remuser] => 97 [a06b278eec754bb8c5e798437f0c9301] => 1 [1cf1] => 9afebfadc7540a4a527ff4ec452d890f [chronoformname] => Reservatie )
6. $_FILES Array: Array ( )
7. Form passed the plugins step (if enabled) OK
8. Debug End

Someone knows how to solve this?

Hi,

I suggest you use this logic to remove previous records:

#1- add a submit button to the form and show it only to logged in users, set the button name to be "remove_record"

<?php
$user =& JFactory::getUser();
if($user->id):
?>
button code here
<?php
endif;
?>

#2- in the onsubmit after email box add this code:

<?php
$user =& JFactory::getUser();
$db =& JFactory::getDBO();
if($user->id && isset($_POST['remove_record']))
$query = "DELETE FROM #__chronoforms_Reservatie WHERE cf_user_id = '".$user->id."'";
$db->setQuery($query);
$db->query();
?>

this should remove the old record and the one just inserted with this submission, of course it could be better to dynamically turn off the new insertion😉

Regards,
Max

Hi Max,

Thanks for your reply.

I followed your instructions (apart from hiding the button from unregistered guests, site still in production)

This is my form code now:

<?php
$my = JFactory::getUser();
?>
<?php
$user =& JFactory::getUser();
$db =& JFactory::getDBO();
$query = "
  SELECT count(*)
    FROM `jos_chronoforms_Reservatie`
    WHERE `cf_user_id` = ".$db->Quote($user->id).";
  ";
$db->setQuery($query);
if ( $db->loadResult() ) {
	echo "You allready have a reservation for this event";
	echo "<input value='delete reservation' name='remove_record' id='remove_record' type='submit' />";
}
else {
	echo "<input class='cf_inputbox required' title='' id='naam' name='naam' type='text' value='".$my->name."' readonly='readonly' />
			<select class='cf_inputbox validate-selection' id='select_1' size='1' title=''  name='aantal'>
				<option value=''>Choose Option</option>
				<option value='1'>1</option>
				<option value='2'>2</option>
			</select>
			<input value='ad reservation' name='aduser' type='submit' />";
}
?> 

I added the code you posted in the 'onsubmit after email' box.

This indeed removes the record with the users name from the database. However it still ads a new record with the users 'cf_user_id'.

Hi mackirony,

Very late - apologies.

I think you have to kill the 'normal' autogenerated code to stop it running and adding the spurious record. I've dug around in the code and it looks as though this might do it

$MyForm->haltFunction['autogenerated_after_email'] = true;

(or the equivalent before parameter if you have the form setup to save first).

Bob

Hi Bob,

Don't worry about it. I read somewhere on this forum you where on vacation. I hope you had a relaxing time.

I added your line to the code at "On Submit code - after sending email" and 'remove a reservation' does not create that spurious record any more. However, now the 'submit a reservation' part of the form stopped working.

Probably because that line stops adding a new record no matter what submit button is clicked.

Hi Mackirony,

OK - so it's a step in the right direction. It sounds as though it needs to be wrapped in an 'if' clause to detect whether the current task is a delete or a submit. I'm not sure how to detect that (and don't have time to look right now) but it must be possible.

Bob

Never mind Bob, I found it myself. Don't you love search engines? ;-)

if(isset($_POST['remove_record'])){
$MyForm->haltFunction['autogenerated_after_email'] = true;
}

"remove_record" being the name of the button that removes the reservation.

Now it works!

This is the complete code:

At "Form HTML"

<?php
$my = JFactory::getUser();
?>
<?php
$user =& JFactory::getUser();
if($user->id): // show to logged in only
$db =& JFactory::getDBO();
$query = "
  SELECT count(*)
    FROM `jos_chronoforms_Reservatie`
    WHERE `cf_user_id` = ".$db->Quote($user->id).";
  ";
$db->setQuery($query);
if ( $db->loadResult() ) {
	echo "You allready have a reservation for this event";
	echo "<input value='delete reservation' name='remove_record' id='remove_record' type='submit' />";
}
else {
	echo "<input class='cf_inputbox required' title='' id='naam' name='naam' type='text' value='".$my->name."' readonly='readonly' />
			<select class='cf_inputbox validate-selection' id='select_1' size='1' title=''  name='aantal'>
				<option value=''>Choose Option</option>
				<option value='1'>1</option>
				<option value='2'>2</option>
			</select>
			<input value='ad reservation' name='aduser' type='submit' />";
}
endif; // end show to logged in only
?> 

At "On Submit code - after sending email"

<?php
$user =& JFactory::getUser();
$db =& JFactory::getDBO();
if($user->id && isset($_POST['remove_record']))
$query = "DELETE FROM #__chronoforms_Reservatie WHERE cf_user_id = '".$user->id."'";
$db->setQuery($query);
$db->query();
if(isset($_POST['remove_record'])){
$MyForm->haltFunction['autogenerated_after_email'] = true;
}
?>

Now I want to ad some kind of "maximum amount of reservations" that automatically hides the forms and shows some "No more reservations possible" message. Adding up the numbers (1 or 2) in the "aantal" (ammount) fields and comparing the sum to some maximum number.

I will try and figure that out myself and eventually start a new thread if i have questions.

Thanks for helping out.

Hi mackirony,

That looks good, thanks.

Bob