Correct MySQL syntax for inserting a variable into expiresDate field

Sure Greg,

Great suggestion, and it almost works. (see below)

The code that sets the value of the$adjustedMembershipExpiryDate variable is:

<?php // code to check and adjust expires date 4-3-15 ?>
<?php if ( $organization_informationRecord['valid_for_one_year_from_date_joined'] = 0 || $organization_informationRecord['valid_for_one_year_from_date_joined'] = '' ):?>
<?php $curr_month = date("n") ?>
<?php $start_month = $organization_informationRecord['start_month'] ?>
<?php $cutoff_month = $organization_informationRecord['cutoff_month'] ?>
<?php $renewal_duration = $organization_informationRecord['renewal_duration'] ?>
<?php
  $currentYear = date('Y');
  if($curr_month >= $cutoff_month) {
    $renewalYear = $currentYear + ($renewal_duration);
  }
  else {
    $renewalYear = $currentYear + ($renewal_duration - 1);
  }
 
  $expiryMonth = $start_month-1;
  if($expiryMonth < 10) {
    $expiryMonth = "0".$expiryMonth;
  }
  if($expiryMonth == 00) {
    $expiryMonth = 12;
  }
 
  $testEndDate = $renewalYear. '-'.$expiryMonth.'-01';
  $lastDayofMonth = date('t', $testEndDate);
   if($expiryMonth == 02) {
    $lastDayofMonth = 28;
  }
  if($expiryMonth == 04 || $expiryMonth == 06 || $expiryMonth == 08 || $expiryMonth == 11) {
    $lastDayofMonth = 30;
}
  $adjustedMembershipExpiryDate = $renewalYear.'-'.$expiryMonth.'-'.$lastDayofMonth.' 00:00:00';  
  ?>

The variable created (for example) is in the format: 2016-07-31 00:00:00

The code that is inserting the date is:

 mysql_query("INSERT INTO `{$TABLE_PREFIX}accounts` SET
                      first_name         = '".mysql_escape( $_REQUEST['first_name'] )."',
                      middle_initial         = '".mysql_escape( $_REQUEST['middle_initial'] )."',
                      last_name         = '".mysql_escape( $_REQUEST['last_name'] )."',
                      expiresDate      = $adjustedMembershipExpiryDate,
                      neverExpires     = '0',
                      createdDate      = NOW(),
                      updatedDate      = NOW(),
                      createdByUserNum = '0',
                      updatedByUserNum = '0'")
      or die("MySQL Error Creating Record:<br/>\n". htmlspecialchars(mysql_error()) . "\n");
      $userNum = mysql_insert_id();

Without the apostrophes, the error thrown is:

MySQL Error Creating Record:
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 ' neverExpires = '0', createdDate = NOW(), ' at line 51

*************************

With the apostrophes, a user record with the correct expiresDate is created, but the following error is thrown (which is in the code that Clair put together recently):

E_NOTICE: A non well formed numeric value encountered
/home2/public_html/serverpath/apptest.php (line 51)
http://www.thedomain.com/apptest.php

I believe that you have the login credentials for this account if you need it (it's the same account as the captcha issue).

the file that is producing this issue is apptest.php

Thanks for checking into this.

Jerry Kornbluth

Hi Greg,

Just an FYI update.

The line of code in the calculation code (above), that Claire helped create:

$lastDayofMonth = date('t', $testEndDate);

Seems to be the cause of the error:
E_NOTICE: A non well formed numeric value encountered
/home2/public_html/serverpath/apptest.php (line 51)
http://www.thedomain.com/apptest.php

When that error causes a miscalculation, resulting in an invalid $adjustedMembershipExpiryDate variable, like 2016-9-31 00:00:00, (there are only 30 days in September), the expiresDate field in the created user record is left blank, even with the apostrophes.

Hope that explains.

Jerry Kornbluth

Hey Jerry,

I've taken a look at the code, but I wasn't able to recreate the issue on the site. Is there something in particular I need to enter to put into the fields to get the issue to appear?

While I was looking through the code I created apptest2.php, it might be worth testing with that to see if the issue still appears on that page. 

Cheers,

Greg

Thanks to all who made suggestions, especially Greg and Claire.

It's now working and this is the recipe in my CMSB Cookbook http://www.thecmsbcookbook.com that resulted.

Best,

Jerry Kornbluth

PICKING MEMBERSHIP RENEWAL DATE AND DURATION - Apr 20th, 2015

Instead of calculating an expiration date (expiresDate) based on the date that a member signed up, an organization
wanted to calculate the expiration date and membership duration (in years) from a fixed month. 

Further, they wanted anyone signing up after a particular cutoff month relative to the current month, to get an extra
year added to their membership, so that their initial membership period was never shorter than one year. 

Some of the other challenges were calculating a valid date for months that had 30 or 28 days vs those that had 31 days,
and generating a valid MySQL date from the calculation.

With Claire Ryan's and Greg Thomas' from Interactive Tools help, we came up with the following solution.

In a single record editor (mine was called ‘Organization Information’) there are 2 list fields, a text field, and a
check box . The list fields are called Start Month and Cutoff Month. Their values are:

1|January
2|February
3|March
4|April
5|May
6|June
7|July
8|August
9|September
10|October
11|November
12|December

NOTE: the format number|text allows the list to display the text in the pull down menu, while inserting the selected
value in numbers. 

The text field is called Renewal Duration and accepts 1 digit with a value from 1-9. With the description: Enter the
digit corresponding to the Renewal Duration in years (1-9).

The check box ( called ‘Valid for One Year From Date Joined’), with the description: “If renewal period begins on
date joined, check this box. Otherwise the 3 fields below will determine the renewal criteria.”

Then define a few variables:

<?php
@$curr_month = date("n") ;
@$start_month      = str_pad(@$organization_informationRecord['start_month'], 2, "0", STR_PAD_LEFT); 
@$cutoff_month = @$organization_informationRecord['cutoff_month'] ;
@$renewal_duration = @$organization_informationRecord['renewal_duration'] ;
@$currentYear = date('Y');
?>

After that, perform the calculations required and check for valid last day of the month :

 <?php
@$renewalYear = (intval(@$curr_month) >= intval(@$cutoff_month))? @$currentYear + (@$renewal_duration) :@ $currentYear +
(@$renewal_duration - 1) ;
@$expiryMonth = ($organization_informationRecord['start_month'] -1);
  if(@$expiryMonth < 10) {
    @$expiryMonth = "0".@$expiryMonth;
  }
  if(@$expiryMonth == 00) {
    @$expiryMonth = 12;
  }
   @$testEndDate = @$renewalYear. '-'.@$expiryMonth.'-01';
  @$lastDayofMonth = date('t', strtotime(@$testEndDate));
    if(@$expiryMonth == 02) {
    @$lastDayofMonth = 28;
  }
  if(@$expiryMonth == 04 || @$expiryMonth == 06 || @$expiryMonth == 09 || @$expiryMonth == 11) {
    @$lastDayofMonth = 30;
}
if(@$expiryMonth == 01 || @$expiryMonth == 03 || @$expiryMonth == 05 || @$expiryMonth == 07 || @$expiryMonth == 08 ||
@$expiryMonth == 10 || @$expiryMonth == 12 ) {
    @$lastDayofMonth = 31;
}
?>

And then create a valid MySQL date from the result:

<?php $adjustedMembershipExpiryDate = $renewalYear. '-'.$expiryMonth.'-'.$lastDayofMonth.' 00:00:00'; ?>

You can use the following to test your progress.

Current Month is: <?php echo date("n"); ?><br />
Test End Date is: <?php echo @$testEndDate ; ?><br />
Start Month is: <?php echo @$start_month ; ?><br />
Expiry Month is: <?php echo @$expiryMonth ; ?><br />
Last Day of Month: <?php echo @$lastDayofMonth ?><br />
Cutoff Month is: <?php echo @$cutoff_month ?><br />
Renewal Duration is:<?php echo @$organization_informationRecord['renewal_duration'] ?> years<br />
Current Year is: <?php echo date('Y'); ?><br />
Renewal Year is: <?php echo @$currentYear + (@$renewal_duration - 1); ?><br />
Calculated $adjustedMembershipExpiryDate is: <?php echo @$adjustedMembershipExpiryDate ?> <br />

<?php if(@$curr_month >= @$cutoff_month): ?>
It's <?php echo @$cutoff_month ?> or after, so use renewal_duration  (<?php echo @$renewal_duration ?>) as year interval
added to current year.
<?php else:?>
It's before <?php echo @$cutoff_month ?>, so use renewal_duration -1 (<?php echo @$renewal_duration -1 ?>) as year
interval added to current year.
<?php endif ?>

The final step is to insert the $adjustedMembershipExpiryDate into the  mysql_query("INSERT INTO list. NOTE: the
apostrophes are so that the variable is recognized correctly.

 expiresDate      = '$adjustedMembershipExpiryDate',

Then, to allow for the, “membership from date joined” option, I surrounded the calculation code and the 
mysql_query("INSERT INTO code with:

<?php if ( $organization_informationRecord['valid_for_one_year_from_date_joined'] = 0 ||
$organization_informationRecord['valid_for_one_year_from_date_joined'] = '' ):?>
... date calculation code and your  mysql_query("INSERT INTO code, including  expiresDate      =
'$adjustedMembershipExpiryDate', ...
 <?php elseif (@$organization_informationRecord['valid_for_one_year_from_date_joined'] == '1'):?>
...Your original mysql_query("INSERT INTO code, including expiresDate      = (NOW() + INTERVAL 1 YEAR), ...
<?php endif ?>