Fatal error when inserting record in a table

Hi Ryan,

I gave your change a try but now I'm getting a different error:

MySQL Error: Table 'ellescho_jazzonjstreet.cmsb_testingTable' doesn't exist - in email_signupF.php on line 21 by mysql_insert() in /home3/ellescho/public_html/jazzonjstreet/cmsAdmin/lib/common.php on line 1217

Any ideas?

Thanks,

Jerry

Hi Jerry, 

It looks like the table "testingTable" has not been setup or the name has been entered incorrectly. Can you confirm the name of the table you are trying to submit the data to?

Ryan

Minor confusion,

The actual table name was 'testing' (the 'Table' got carried over from the old code by mistake.

The code works now.

Thanks!

Jerry

Hi Ryan,

One more question if you have a moment.

I've been using the code you updated as part of a 2 part opt in email signup database.

The first part is the insertion of a record in the 'email_signup' database (using the updated code you provided).

The second part is that an email is sent to the person signing up containing the following confirmation link:

http://jazzonjstreet.com/confirmed.php?submit=1&confirmed=1&email=jerry@jkwebdesigns.com

Then I've used the code below in confirmed.php, to update the record.

Any thoughts on how I'd change this to be PHP7 compatible like the insert record code you generously offered?

Thanks,

Jerry

  $where = "email='".mysql_escape($_REQUEST['email'])."'";
     
      if(mysql_select_count_from('test',$where)){ //check to ensure that email exists in the table
    $where.=" AND confirmed='1'";
    if(!mysql_select_count_from('email_signup',$where)){ //email exists and has not been confirmed yet
        $query = "UPDATE `{$TABLE_PREFIX}test` SET
              hidden           = '0',
              confirmed          = '1',
              updatedDate      = NOW()
               
             WHERE email = '".mysql_escape( $_REQUEST['email'] )."'";
        mysql_query($query) or die("MySQL Error:<br/>\n". htmlspecialchars(mysql_error()) . "\n");
        $userNum = mysql_insert_id();

Hi Jerry, 

Here's some (untested and) updated code for PHP 7 / mysqli that uses some of our cmsb database functions.

//
$emailExists        = mysql_count('test',         ['email' => $_REQUEST['email']]);
$emailConfirmed     = mysql_count('email_signup', ['email' => $_REQUEST['email'], 'confirmed' => 1]);
$userNum            = 0;
if ($emailExists && !$emailConfirmed) {
  $updateNum    = null;
  $updateWhere  = [ 'email' => $_REQUEST['email'] ];
  $colsToValues = [ 'hidden' => 0, 'confirmed' => '1', 'updatedDate=' => 'NOW()' ];
  mysql_update('test', $updateNum, $updateWhere, $colsToValues);
  $userNum = mysqli()->insert_id;
}

Let me know if it works for you.

Thank you Dave

I'll test it today and post back

Jerry Kornbluth

Hi Dave,

Everything seems to work as it should  now.

Thanks for all your help!

I'll post a complete recipe for the double opt in email signup in the CMSB Cookbook and here shortly, but for now, here's what I'm using to create the new record in the email signup database (any comments and security suggestions welcome):

<?php
// submit form
if (@$_REQUEST['submit']) {
 @$errorsAndAlerts .= validateGoogleCaptcha();
  // error checking

  if (!@$_REQUEST['first_name'])    { $errorsAndAlerts .= "Please enter your first name<br/><br/>\n"; }
  if (!@$_REQUEST['last_name'])  { $errorsAndAlerts .= "Please enter your last name<br/><br/>\n"; }
    if (!@$_REQUEST['email'])  { $errorsAndAlerts .= "Please enter your email address<br/><br/>\n"; }

// email checking
    if ($_REQUEST['email'] || $_REQUEST['email2']) {
      if (!@$_REQUEST['email'])                            { $errorsAndAlerts .= "Please enter your email address<br/><br/>\n"; }
      elseif (!@$_REQUEST['email2'])                            { $errorsAndAlerts .= "Please re-enter your email address<br/><br/>\n"; }
      elseif ($_REQUEST['email'] != $_REQUEST['email2']) { $errorsAndAlerts .= "Sorry, the e mail addresses you entered don't match!<br/><br/>\n"; }
    }

 // check for duplicate emails
    if (!$errorsAndAlerts) {
      
      $count = mysql_select_count_from('email_signup', "'".mysql_escape($_REQUEST['email'])."' IN (email)");
      if ($count > 0) { $errorsAndAlerts .= "That email address is already signed up, please choose another!<br/><br/>\n"; }
    }

  // turn off strict mysql error checking for: STRICT_ALL_TABLES
  mysqlStrictMode(false); // disable Mysql strict errors for when a field isn't defined below (can be caused when fields are added later)

  // add record
  if (!@$errorsAndAlerts) {
    $tablename   = 'email_signup';
       $colsToValues = array();
       $colsToValues['createdDate=']     = 'NOW()';
       $colsToValues['updatedDate=']     = 'NOW()';
       $colsToValues['createdByUserNum'] = 0;
       $colsToValues['updatedByUserNum'] = 0;
       $colsToValues['first_name']         = $_REQUEST['first_name'];
       $colsToValues['last_name']         = $_REQUEST['last_name'];
       $colsToValues['email']            = $_REQUEST['email'];
       $colsToValues['hidden'] = 1;
       $hideMissingFieldErrors = true;
       $newRecordNum = mysql_insert($tablename, $colsToValues, $hideMissingFieldErrors);

    // display thanks message and clear form
    $errorsAndAlerts = "Thanks for submitting your information. <br /><br /> Before we can add your email address to our list, you'll need to confirm your intent by clicking on the link in the email that you will receive shortly. <br /><br /> If you do not see the email, check your spam folder.";

 
    // send email to applicant    
    $to=$_REQUEST['email'];
$subject = 'Email List Signup Request';
$headers = "From: $signup_email" . "\r\n";
$headers .= "Reply-To: $signup_email" . "\r\n";
$headers .= "MIME-Version: 1.0\r\n";
$headers .= "Content-Type: text/html; charset=ISO-8859-1\r\n";
$message = '<html><body>';
$message .= '<table rules="all" style="border-color: #666;" cellpadding="10">';
$eml = $_REQUEST['email'];
$message .= "<tr ><td><h2 align='center'>EMAIL LIST SIGNUP REQUEST</h2><br /><br />There is just one more step to be included on our email distribution list.<br /><br />To make sure that no one else signed you up for this list, please click on this link or paste it into your browser.<br /><br />
 <a href='http://mysite.com/confirmed.php?submit=1&confirmed=1&hidden=0&email=$eml'>http://mysite.com/confirmed.php?submit=1&confirmed=1&hidden=0&email=$eml</a></td></tr>";
$message .= "</table>";
$message .= "</body></html>";


// Send
if (mail($to,$subject,$message, $headers))
{
 echo 'Mail sent!';
} else
{
 echo 'Error! Mail was not sent.';
};
  }

}

?>

Here's what I'm using in the confirmation page to update the record:

<?php
// submit form
if (@$_REQUEST['submit']) {

  // error checking
  $errorsAndAlerts = "";
  if (!@$_REQUEST['email'])  { $errorsAndAlerts .= "Please enter the email address you used when you signed up.<br/><br/>\n"; }

   // turn off strict mysql error checking for: STRICT_ALL_TABLES
  mysqlStrictMode(false); // disable Mysql strict errors for when a field isn't defined below (can be caused when fields are added later)
 
 // update user
   if (!$errorsAndAlerts) {
    
    $emailExists        = mysql_count('email_signup',         ['email' => $_REQUEST['email']]);
$emailConfirmed     = mysql_count('email_signup', ['email' => $_REQUEST['email'], 'confirmed' => 1]);
$userNum            = 0;
if ($emailExists && !$emailConfirmed) {
  $updateNum    = null;
  $updateWhere  = [ 'email' => $_REQUEST['email'] ];
  $colsToValues = [ 'hidden' => 0, 'confirmed' => '1', 'updatedDate=' => 'NOW()' ];
  mysql_update('email_signup', $updateNum, $updateWhere, $colsToValues);
  $userNum = mysqli()->insert_id;
  //' errorsAndAlerts
$errorsAndAlerts = "Thanks, your email address has been succesfully added to our maillist";
    
}
    elseif($emailExists && $emailConfirmed){//email exists, but has already been confirmed
      $errorsAndAlerts.="That Email address has already been confirmed.<br/><br/>If you'd like to sign up with another Email address,<br/><br/><a  href='http://mysite
.com/email_signup.php'><span class='heading_font' >CLICK ON THIS LINK</span></a><br/>";
    }
    
     elseif (!$emailExist){ //email does not exist in the database
    $errorsAndAlerts.="Sorry, that email address doesn't exist in the database.<br/><br/>Please enter the same Email address that you used when you signed up, and click on submit.<br/>";
      }
       
}}
 
?>

Best,

Jerry