Acajoom and ChronoForms - Unsubcribe

Hi there,

I read here on the forums how you can use ChronoForms to make a subscription form for Acajoom using the following code:

<?php
$email = JRequest::getVar('email', '', 'post');
$name = JRequest::getVar('name', '', 'post');
$MyForm->formrow->redirecturl = JURI::base()."index.php?option=com_acajoom&act=subscribe&listid=14&receive_html=1&email=$email&name=$name";
?>

Correct me if I'm wrong: this script inserts the name and e-mail into the Acajoom database, and possible custom fields into it's own database created by ChronoForms ('Create Table') right ?

Is it possible to reverse this proces, and make a form in which you can unsubscribe. In other words: that it deletes the name and e-mail in the Acajoom database and the custom fields in the ChronoForms database ? And how would that code look like ?

Greets,
Marc

Hi Marc,

Yes I'm sure it is. You'd need to know the AcaJoom code for unsubscribe - at a guess it's act=unsubscribe.

Bob

But then it only deletes the data in the Acajoom table. What I'm looking for is that, if i unsubscribe using ChronoForms, it also deletes the data in the table created by ChronoForms.

How can I do that ?

Hi Marc,

Sorry, I misunderstood.

Yes I guess you could to that. You'd need to detect the unsubscribe in the OnSumbit after box and then have a little MySQL to delete the record DELETE FROM `table_name` WHERE `column_name` = 'identifier';

Bob

Hi Bob,

Thanks for your help.

I added the php code to 'On Submit code - after sending email' but it doesn't add a thing to Acajoom.
Am i doing something wrong ? I added some screens of my setting and my form code...Could you have a look ?

Settings:



Form data:



Greets,
Marc

Hi Marc,

I suggest that you turn debug on for the form - you should then be able to see the ReDirect URL after you submit. Can you post it here please. I suspect that something may be a little wrong in there.

Bob

Hi Bob,

This is what i see when I fill in the form and submit it:




Greets,
Marc

Hi Marc,

This looks like an older version of ChronoForms - do you know which version it is?

Bob

Hi Bob,

The component version is 2.5 J1.5 RC3.1 and the plugin version is 0.5

Greets,
Marc

I managed to get the subscription thing going after updating to the latest version. But now comes the hardest part: Deleting data in the Acajoom database and in the Chronoforms database isn't working.

This is the code i'm using now.

<?php
$email = JRequest::getVar('email', '', 'post');
$name = JRequest::getVar('name', '', 'post');
$MyForm->formrow->redirecturl = JURI::base()."index.php?option=com_acajoom&act=unsubscribe&listid=1&receive_html=0&email=$email&name=$name";

$db =& JFactory::getDBO();
$query = "
  DELETE * 
    FROM ".$db->nameQuote('#__jos_chronoforms_newsletter_subscription')."  
    WHERE ".$db->nameQuote('email')." = ".$db->quote('$email').";
  ";

$db->setQuery($query);

?>

Greets, Marc

Hi Marc,

I don't know about the AcaJoom end, for the ChronoForms bit you aren't executing the query:

$db->setQuery($query);
$db->query(); // add this line
?>

Bob

Hi Bob,

I added the line but it has no effect. The Record is still in the database.

Greets,
Marc

Hi Marc,

You may need to remove the * after DELETE. To debug MySQL turn on Site Debug and check the query that is being created.

Bob

Hi Bob,

Sorry, still no results (I deleted the *)

Greets,
Marc

Hi Marc,

Please turn Site Debug on, submit the form then search through all the output to see if you can see this query. It would be good to see the actual SQL created and any error messages reported.

Bob

Hi Marc & Bob,
Just thought I'd pitch in with what I believe to be an error in your query;
You've surrounded the $email variable with '', which would prevent variable substitutions - so in fact, you are trying to remove records having '$email' (literally) as email, rather than the value of $email.

/Fredrik

Hi Fredrik,

Good catch - I missed that completely :-(

Bob

Hi guys,

I removed the '' and switched debug on, and now it's producing the following error:

500 - JDatabaseMySQL::query: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM `jos_chronoforms_newsletter_subscription` WHERE `email` = ' at line 1 SQL=DELETE * FROM `jos_chronoforms_newsletter_subscription` WHERE `email` = [email]'marcderix@mailadress.com[/email]';

You may not be able to visit this page because of:

1. an out-of-date bookmark/favourite
2. a search engine that has an out-of-date listing for this site
3. a mistyped address
4. you have no access to this page
5. The requested resource was not found.
6. An error has occurred while processing your request.

Please try one of the following pages:

* Home Page

If difficulties persist, please contact the System Administrator of this site.

JDatabaseMySQL::query: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM `jos_chronoforms_newsletter_subscription` WHERE `email` = ' at line 1 SQL=DELETE * FROM `jos_chronoforms_newsletter_subscription` WHERE `email` = [email]'marcderix@mailadress.com[/email]';

Call stack
# Function Location
1 JSite->dispatch() /public_html/joomla/index.php:68
2 JComponentHelper->renderComponent() public_html/joomla/includes/application.php:124
3 require_once() public_html/joomla/libraries/joomla/application/component/helper.php:162
4 uploadandmail() public_html/joomla/components/com_chronocontact/chronocontact.php:62
5 CFChronoForm->submitForm() public_html/joomla/components/com_chronocontact/chronocontact.php:125
6 CFCustomCode->runCode() public_html/joomla/components/com_chronocontact/libraries/chronoform.php:465
7 eval() public_html/joomla/components/com_chronocontact/libraries/customcode.php:51
8 JFDatabase->query() public_html/joomla/components/com_chronocontact/libraries/customcode.php(51) : eval()'d code:14
9 JDatabaseMySQL->query() public_html/joomla/plugins/system/jfdatabase/jfdatabase_inherit.php:641
10 JError->raiseError() public_html/joomla/libraries/joomla/database/database/mysql.php:231
11 JError->raise() public_html/joomla/libraries/joomla/error/error.php:171
12 JException->__construct() public_html/joomla/libraries/joomla/error/error.php:136

Greets,
Marc

Hi Marc,
It would seem the * is still in there. Please doublecheck that your SQL-query starts with "DELETE FROM " rather than "DELETE * FROM ".

/Fredrik