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)

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/

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