Forums

Alright! Hey guys... I am in the need of a little more help...

I have a News Letter Subscription form with 2 text fields (name, email) and a check box (if they want to be removed) and a submit button.

<table class="newsletter">
        <tr>
          <td>Name</td>
        </tr>
        <tr>
          <td class="F2"><input type="text" name="name" /></td>
        </tr>
        <tr>
          <td>Email</td>
        </tr>
        <tr>
          <td class="F2"><input type="text" name="email" /></td>
        </tr>
        <tr>
          <td><input type="checkbox" name="remove" />
            Remove Me</td>
        </tr>
        <tr>
          <td height="40" align="center" valign="bottom"><input type="image" value="subscribe" src="images/ssubmit.gif"  /></td>
        </tr>
      </table>

I am trying to create a newsletter subscribe/unsubscribe form so if the box is checked it will look for a table entree with their name and email and if found it will delete it. I have already connected to the database and was able to get the form to add entrees to the database so the only issue is deleting them if the checkbox is checked.

So, is this at all possible?

Any help would be appreciated. I will be purchasing a license in a few hours because this component is excellent!

Thanks!

Aaron

Hi zendiko,

Yes, it's possible but will require a little hand-code I think. The SQL will be something like

DELETE FROM `#__table_name` WHERE cf_id = `99`;

You'll need to build this up in one of the OnSubmit boxes I think.

Bob

I attempted to try something like this by looking at a previous post on a similar section however I was placing the code in the From Code -> On Submit code - before sending email window and I was getting an error when I was trying to save the form...

When I hit save, I get this:

Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.

Please contact the server administrator, [email]webmaster@clients.zendiko.com[/email] and inform them of the time the error occurred, and anything you might have done that may have caused the error.

More information about this error may be available in the server error log.

Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.


Any Ideas?

Thanks Much!

Aaron

So here's what I placed in the OnSubmit box after email is sent:

<?php
$remove = JRequest::getVar('remove', '', 'post');
$email = JRequest::getVar('email', '', 'post');  

if($remove == 'remove'){

$db = &JFactory::getDBO();
$email = $db->Quoted($email);
$sql = "DELETE FROM jos_chronoforms_Newletter WHERE email='a@b.com'";
$db->setQuery($sql);
}
?>
<p>Thank you for subscribing to our newsletter</p>

I put [email]'a@b.com[/email]' as a tester to see if it would actually work. The name of the table that ChronoForms created for me is jos_chronoforms_Newletter and the name of the column that I want to check it against is email.

I still get the error i posted in my last post when i try to save this code... and I narrowed it down to this statement:

$sql = "DELETE FROM jos_chronoforms_Newletter WHERE email='a@b.com'";

When I place that statement in the area and try to save I get that error.... and when I hit the back button my Form code goes back to a previous version for some reason. Weird.

I got most of that code from a previous post in this forum: http://www.chronoengine.com/forums.html?cont=posts&f=2&t=11437&start=15&st=0&sk=t&sd=a&hilit=database+delete

I hope this helps... because I really need this functionality to work... has anyone successfully pulled this off before?

Thanks guys!

Aaron

Alright...

I've narrowed it down... The error is coming from the table name... if i take jos_chronoforms_Newletter out of the sql query it saves... if i have anything as a table name I get that error... hmmm... any ideas?

I checked my server log and this is what it said was the cause of that error:

[Tue Dec 02 01:19:02 2008] [error] [client 97.95.183.94] File does not exist: /home/zendiko/public_html/clients/500.shtml, referer: http://clients.zendiko.com/toss/bo/administrator/index2.php

Hi zendiko,

The error you've posted just says that your site doesn't appear to have a 500 error page. The 500 error is a server error - I've no real idea why this should happen with a database query.

The SQL you have there looks OK, it's missing a final ; and isn't fully quoted but should run OK (I just tested it here on my local server).

The next step is to open up PHPMyAdmin or something similar and paste the SQL directly into the query box. Then you can see any error messages without any Joomla stuff getting in the way.

Bob

I logged into PHPmyadmin and successfully completed the SQL statement... I had 3 entrees with that email and it deleted all 3.

hmm... the form program just won't let me post that table name into the onSubmit after email textarea...

-Aaron

Maybe Max can give more insight? or someone? You think it's a server issue or a component/chrono issue?

Hi zendiko,

I don't think that it is either a server issue or a component issue - there's just a bug in your code, most likely it's still misquoting the sql.

So now you know that the SQL you pasted in there is good. Next add a little debug code into your php to see what is happening on this page.

<?php
    $remove = JRequest::getVar('remove', '', 'post');
    $email = JRequest::getVar('email', '', 'post'); 

    if($remove == 'remove'){

    $db = &JFactory::getDBO();
    $email = $db->Quoted($email);
    $sql = "DELETE FROM jos_chronoforms_Newletter WHERE email='a@b.com'";
global $mainframe;
$mainframe->enqueuemessage("sql: ".$sql);
    $db->setQuery($sql);
$mainframe->enqueuemessage("DB error: ".$db->getErrorMessage);
    }
    ?>
    <p>Thank you for subscribing to our newsletter</p>

This hsould give you two messages when you submit. One will have the actual SQL created, the other the last DB error message.

Bob

I posted that code into the "On Submit code - after sending email:" section and I went to save it and got the Internal Error Again!

Yikes!

Hi zendiko,

OK - now please comment out the $db->setQuery line and see what messages you get.

I also notice - a little late - that you don't have a line to execute the query. I've added this in too but left it commented out at the moment.

Bob

. . .
global $mainframe;
$mainframe->enqueuemessage("sql: ".$sql);
// $db->setQuery($sql);
// $db->query();
$mainframe->enqueuemessage("DB error: ".$db->getErrorMessage);
. . .

Still the same error upon saving... there is one thing that I changed upon setting up the database... not sure if this would cause any harm... but when I created the form I accidently spelled it "Newletter" instead of "Newsletter" so when the table was made... it was called jos_chronoform_Newletter... and I later changed the form name to Newsletter. I thought... well maybe that was causing it... so I changed the form name back to Newletter and the error became worse! After it would not allow me to save I would hit back and normally I would hit Cancel to get back to the main Chronoform admin, but when I did that I got the error again as well... so I changed the form name back to Newsletter and it still has the error on the save but not on the cancel...

does that help at all? Maybe I should Just start the form and database over?

-Aaron

Also, I just purchased the licensing and if the new install is not any kind of upgrade, i'd rather not have to re-install. So when you get a chance, Bob, could you send me the directions on how to take off the backlink?

Thanks!

Aaron

Hi Aaron,

first, back to your saving error problem, what if you removed all code from the onsubmit code area, all is fine ? if so then add your code and delete all lines except the php tags, then save, then add one more line, and so, until you find where the problem is! I can't see where the problem is in your code, its working here fine so it must be because of some config at your server.

the license email I sent has all info needed, tell me if there is anything not clear enough.

Regards
Max

Max,

In the previous posts I mentioned that whenever I place the table name jos_chronoform_Newletter I get that error... but if i leave everything and just delete the name of the table it will save...

Aaron

Hi Aaron, then replace jos_chronoform_Newletter in your code with #__chronoform_Newletter

let me know!

Regards
Max

OK...

I got it to save: $sql = "DELETE #__chronoforms_Newletter WHERE email=$email";
but when I add "FROM" after DELETE it gets the error.

Any ideas?

Aaron


Thanks for your help! I appreciate it!

Hi zendiko,

One of my replies seems to have gone missing here. I'll try and repeat it.

The code you posted is not valid SQL so I'm not quite sure what it is doing, probably nothing.

It's always difficult to diagnose by posting here; and nearly impossible unless you post both the code you are using and the *exact* error messages.

It looks as though you have not followed the advice on quoting the SQL; you have not added the missing ; from the end of the statement and you have not added the code to display the sql (or if you have then you haven't posted the results).

Have you followed Max's suggestion of removing the code and replacing it line by line?

Bob

ok...
THis is my form code:

<table class="newsletter">
        <tr>
          <td>Name</td>
        </tr>
        <tr>
          <td class="F2"><input type="text" name="name" /></td>
        </tr>
        <tr>
          <td>Email</td>
        </tr>
        <tr>
          <td class="F2"><input type="text" name="email" /></td>
        </tr>
        <tr>
          <td><input type="checkbox" name="remove" value="remove" />
            Remove Me</td>
        </tr>
        <tr>
          <td height="40" align="center" valign="bottom"><input type="submit" name="submit" value="subscribe"  /></td>
        </tr>
      </table>

This is the code that I have in the OnSubmit:

<?php
    $remove = JRequest::getVar('remove', '', 'post');
    $email = JRequest::getVar('email', '', 'post'); 

    if($remove == 'remove'){

    $db = &JFactory::getDBO();
    $email = $db->Quoted($email);
  $sql = "DELETE jos_chronoforms_Newletter WHERE email=$email";
global $mainframe;
$mainframe->enqueuemessage("sql: ".$sql);
$db->setQuery($sql);
$db->query();
$mainframe->enqueuemessage("DB error: ".$db->getErrorMessage);
    }
    ?>
    <p>Thank you for subscribing to our newsletter</p>

Notice there is no FROM because as soon as I enter that in I cannot save it.

I just tried to dissect that line of code so I entered Just the following code:

<?php 
DELETE FROM
?>

And I got the error when trying to save it.
I have attached a screenshot of the error I am getting upon save.

If there are any problems with my SQL statement... please advise.

THanks.

Hi zendiko,

Well 'DELETE FROM' isn't valid anything - not PHP and not SQL. It's odd that it crashes your server though, I'd expect a PHP error message.

I don't have time to look more closely not, but will try to take a look right through the code this afternoon.

Bob

I think I should just kill the component and reinstall everything... What do I need to install... because I never installed the mambot.. and I dont even know what that is... all I installed was the v3 component and the module.... and that is giving me trouble as well.

If you can just quickly jot down what I was supposed to do, I will blow it all out and start over.

Thanks,

Aaron

Hi Aaron,

before you go far, try to delet all your onsubmit code and keep the $db line with the SQL line and test, you have lots of code there and it may be causing some problem.

Regards
Max

Hi zendiko,

I just set up a little test and this code works for me

<?php
global $mainframe;
$remove = JRequest::getVar('remove', '', 'post');
$email = JRequest::getVar('email', '', 'post');
if ( $remove == 'remove' ) {
  $db = &JFactory::getDBO();
  $email = $db->Quote($email);
  $sql = "DELETE FROM #__chronoforms_newletter WHERE email=$email;";
  if ( $debug) $mainframe->enqueuemessage("sql: ".$sql);
  $db->setQuery($sql);
  if ( !$db->query() ) {
    $mainframe->enqueuemessage("DB error: ".$db->getErrorMessage());
  }
}
?>
<p>Thank you for subscribing to our newsletter</p>

Bob

PS Note that its $db->Quote($email); not 'Quoted'

That code did not help... while it is probably correct... I still am unable to save without that server error... so I am sure that it is not a coding error... what do you think... scripting/setting error or server error?

Hi zendiko,

did you try my suggestion above ?

Max

Yes I did. It appears it is the "FROM" code now that is causing it.

Hi zendiko,

I'm as certain as I can be that FROM is not *causing* the problem. More likely removing the FROM makes the SQL Invalid and creates a different problem.

Please check that the table name is *identical* in spelling and upper/lower case in your database and in the SQL. That is both are "jos_chronoform_newletter", not "jos_chronoform_Newsletter" or any other version.

Bob

I have attached a picture of my database to verify spelling... remember I tested my SQL statement in phpmyadmin and it worked.... so obviously the SQL statement is correct.


[attachment=0]Picture 11.jpg[/attachment]

Hi zendiko,

That all looks good.

Have you output the SQL from ChronoForms (see one of my earlier posts) and tested that in PHPMyAdmin with cut and paste?

Bob

Yeah, that's what I was referring to when in my last post I said this:

remember I tested my SQL statement in phpmyadmin and it worked

HI zendiko,

I was just checking that you actually entered the code to echo the sql to the screen and copy and paste it from there - can give different results than copying from the code in the OnSubmit box.

If that's so - then I have no idea what is happening here.

If the site is On-line, then by all means pm me with a SuperAdmin login and I'll go take a look.

Bob