Automatically Update Member Record in Accounts Database

8 posts by 3 authors in: Forums > CMS Builder: Plugins & Add-ons
Last Post: June 6, 2011   (RSS)

By gkornbluth - June 2, 2011

Hi All,

I'd like to be able to automatically update the expiresDate after a member renews their membership.

So far I've set up a hidden (masked) "thank you" page that can only be accessed after the member renews their dues using PayPal.

For the "thank you" page I cobbled together pieces of some of the sample forms, and I think I'm almost there (no error checking yet, just bare bones operation).

I set up a test section called "reset_date", with a date field called "expiresDate" and a "username" and "password" text field.

The problem is that instead of only updating the record with matching username and password fields, when the form is submitted all table records are updated.

I don't really understand enough about MySQL queries to fix the problem and could use some help.

I realize that the current user is not logged in and that their account has expired (expiresDate field is earlier then today, neverExpires is not checked) and I'm not sure what to do about that either.

Thanks,

Jerry Kornbluth

Here's what I have:

In the head:
<?php if (@$_REQUEST['save'] && $_REQUEST['username'] = $CURRENT_USER['username'] && $_REQUEST['password'] = $CURRENT_USER['password'])

{
mysql_query("UPDATE `{$TABLE_PREFIX}reset_date` SET

expiresDate = NOW() + INTERVAL 1 YEAR ")

or die("MySQL Error:<br/>\n". htmlspecialchars(mysql_error()) . "\n");
$userNum = mysql_insert_id();

}

?>

And for the form in the body of the page:

<form method="post" action="?">
<input type="hidden" name="save" value="1" />
<table border="0" cellspacing="0" cellpadding="2">
<tr>
<td class="body-text-bold">Username</td>
<td><input type="text" name="username" value="<?php echo htmlspecialchars(@$_REQUEST['username']); ?>" size="20" /></td>
</tr>
<tr>
<td class="body-text-bold">Password</td>
<td><input type="password" name="password" value="<?php echo htmlspecialchars(@$_REQUEST['password']); ?>" size="20" /></td>
</tr>

<tr>
<td colspan="2" align="center">
<br/><input type="submit" name="submit" value="Login" />


</td>
</tr>
</table>
</form>
The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

Re: [gkornbluth] Automatically Update Member Record in Accounts Database

By robin - June 3, 2011 - edited: June 3, 2011

Hey Jerry,

As you found out, if you don't give SQL a WHERE condition then it will update the whole table! You'll need to add a WHERE condition that looks something like this:

"UPDATE `{$TABLE_PREFIX}reset_date`
SET expiresDate = NOW() + INTERVAL 1 YEAR
WHERE password = '" . $_REQUEST['password'] . "'
AND username = '" . $_REQUEST['username'] . "'"

I also see another possible problem with your php code. In the if statement you have:
$_REQUEST['username'] = $CURRENT_USER['username']
but using a single equals doesn't compare, it assigns. If you want to check if those two variables match then you need double equals like this:
$_REQUEST['username'] == $CURRENT_USER['username']

Hope that helps,
Robin
Robin
Programmer
interactivetools.com

Re: [robin] Automatically Update Member Record in Accounts Database

By gkornbluth - June 3, 2011

Robin,

Thank you!

It's really good that you folks speak and understand this language fluently.

I'll give it a shot over the weekend.

Jerry Kornbluth
The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

Re: [gkornbluth] Automatically Update Member Record in Accounts Database

By robin - June 6, 2011

Hey Jerry,

You might just need to re-arrange the code a bit. You only want all that to happen on an attempted save right? So lets put all of it in the $_REQUEST['save'] condition. Also move the error checking above the update query. Hopefully this will get you started:
<?php if (@$_REQUEST['save'] )
{
// error checking
$errorsAndAlerts = alert();

if (@!$_REQUEST['username'] && @!$_REQUEST['password']) {
$errorsAndAlerts = "Enter your user name and password,<br />then click submit to automatically update your subscription<br /><br />";
}
elseif (@!$_REQUEST['username']) {
$errorsAndAlerts = "Please enter your user name";
}
elseif (@!$_REQUEST['password']) {
$errorsAndAlerts = "Please enter your password";
}
if(!$errorsAndAlerts) {
mysql_query("UPDATE `{$TABLE_PREFIX}accounts`
SET expiresDate = NOW() + INTERVAL 1 YEAR
WHERE password = '" . $_REQUEST['password'] . "'
AND username = '" . $_REQUEST['username'] . "'")
or die("MySQL Error:<br/>\n". htmlspecialchars(mysql_error()) . "\n");
$userNum = mysql_insert_id();
// on success

// this should only redirect after a successful update
header("Location: login2.php");
}

}

?>

Hope that helps,
Robin
Robin
Programmer
interactivetools.com

Re: [robin] Automatically Update Member Record in Accounts Database

By gkornbluth - June 6, 2011

Robin,

That worked perfectly.

I'm sorry, but at the risk of being a pest, my last hurdle is to return an error if the username or password doesn't exist in the database.

I tried cobbling together the following based on code in the v1.03 membership plugin sample signup form, but I get a syntax error when either of the $count lines of code are included.

Hope it's an easy fix...

Thanks,

Jerry Kornbluth

Here's what I inserted just before the
if(!$errorsAndAlerts) code:

$count = mysql_select_count_from('accounts', "`username` = '".mysql_escape(@$_REQUEST['username'])."'");
elseif ($count = 0 && @$_REQUEST['username']) {
$errorsAndAlerts = "That password or username is invalid!";
}

$count = mysql_select_count_from('accounts', "`password` = '".mysql_escape(@$_REQUEST['password'])."'");
elseif ($count = 0 && @$_REQUEST['password']) {
$errorsAndAlerts = "That password or username is invalid!";
}
The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

Re: [gkornbluth] Automatically Update Member Record in Accounts Database

By Jason - June 6, 2011

Hi Jerry,

It looks like the syntax error is coming from using elseif without an if statements. Also, you should use "$count == 0" instead of $count = 0 as "=" is an assignment operator and "==" is a comparison operator.

Try this:

$count = mysql_select_count_from('accounts', "`username` = '".mysql_escape(@$_REQUEST['username'])."'");
if ($count == 0 && @$_REQUEST['username']) {
$errorsAndAlerts = "That password or username is invalid!";
}

$count = mysql_select_count_from('accounts', "`password` = '".mysql_escape(@$_REQUEST['password'])."'");
if ($count == 0 && @$_REQUEST['password']) {
$errorsAndAlerts = "That password or username is invalid!";
}

Is that closer to what you're looking for?

Hope this helps
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Automatically Update Member Record in Accounts Database

By gkornbluth - June 6, 2011

Jason,

Thanks!

That works, and I can certainly live with it...

But the reason I was trying to use the elseif was so that the preceding errorsAndAelrts elseif's still worked.

Now, if anything is entered into either field, and the submit button is pressed, the "That password or username is invalid" error comes up instead of the "Please enter your ..." .

Thanks,

Jerry
The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php