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: [robin] Automatically Update Member Record in Accounts Database

By gkornbluth - June 4, 2011

Hi Robin,

With your expert guidance I’m getting closer, but I've come up against two small challenges.

I'm using the Website membership plugin V1.03 for now), and when setting up error checking, I’m having some difficulty modifying the code below to:

1) Check for a valid user name and password,
2) Display an error message if either are not valid,
3) Update the database if credentials are valid and
4) Only on a successful update redirect the user to a special login page (header("Location: login2.php"); ).

The code below works, but will either:

A) Display an error if the username or password entered isn't found in the database (Form: name=action, value= login - If statement: @$_REQUEST['action']),

or

B) Update the user record if valid credentials are entered (Form: name=save, value= login - If statement: @$_REQUEST['save']),

but not both.

And,

C) I’m not sure how to set up the redirect to work only on a successful update.

Thanks as always,

Jerry Kornbluth

At the top of the working viewer:

<?php if (@$_REQUEST['save'] )

{


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

}

?>

<?php

// 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";
}

// this should only redirect after a successful update

else header("Location: login2.php");

?>

And the form in the body of the viewer:

<?php if (@$errorsAndAlerts): ?>
<div class="Heading-Text"><br/>
<?php echo $errorsAndAlerts; ?><br/>
</div>
<?php endif ?><br /><form method="post" action="?">
<input type="hidden" name="save" value="/newpage.php" />
<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="Click Here To Update Your Subscription" />


</td>
</tr>
<tr>
<td colspan="2" align="left">
<br />
<br /> <a class="special" href="<?php echo $GLOBALS['WEBSITE_LOGIN_REMINDER_URL'] ?>">FORGOT YOUR PASSWORD? CLICK HERE</a> <br /><br />
</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 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/