server validation to restrict login

building web site for posting items for sale

basic membership can post 10 items then must upgrade account.

php/mysql tables
accounts
items
primary index accounts.account_ID
index items.account_ID

sql is this
SELECT COUNT(items.account_ID)
FROM items INNER JOIN accounts ON items.account_ID = accounts.account_ID
WHERE accounts.account_email = var1
GROUP BY items.account_ID
//var1 is $_POST['user_name'] // user name is login text field

not sure how to get a server validation from this or if that is even what i want

i want a user to be denied login when they reach their posting limit and echo a message to encourage them to upgrade.

On the login page, add the account_email as a session that is created when they login.

double click the Authenticate user server behavior. On the third page, you can select columns to store in session variables.

No recreate the recordset using the account_email session variable instead of the post value on the login success page.

add a set session value server behavior to capture the count that is returned.


For the trigger use Session Variable is not set.

For the value, click the lightning bolt and select the count column from the recordset.

You will need to manually rearrange the code so the set session value behavior is after the recordset.



In the security assist access rules manager, create a new rule to check that the count session is less than or equal to 10. then apply that rule to the login success page and send them to the update your account page if it fails.


Again, you may have to rearrange the code so the the rule is after the session value code.

thanks Jason,

i accomplished what i wanted but did it a little differently.
Once i figured out the COUNT()recordset

//sql is---
SELECT items.account_ID, COUNT(*)
FROM items INNER JOIN accounts ON items.account_ID = accounts.account_ID
WHERE accounts.account_screen_name = var1
GROUP BY items.account_ID

I used PHP if/else statement to
hide the submit button on the item post page and replace with a statement

<?php if ($row_rs_count['COUNT(*)']<"10"){ ?>
<input type="submit" name="submit1" id="submit1" value="Post Your Item for Sale" />
<?php }
elseif ($row_rs_account['account_validated']=="goldaccount"){ ?>
<input type="submit" name="submit2" id="submit2" value="Post Your Item for Sale" />
<?php }
else { echo ('<h2>Your account has reached the limit of 10 items. Upgrade your account to <a href="Gold-Member.php">Gold Member</a> for unlimited item postings</h2>');
}
?>

then i used Universal Email to send an email to the account holder encouraging them to upgrade their account to be able to post unlimited items

<?php
if ((($row_rs_count['COUNT(*)']>="10" && $row_rs_account['account_validated']=="basicaccount"))) {
//WA Universal Email object="mail"
//Send Loop Once Per Entry
$RecipientEmail = "".$row_rs_account['account_email'] ."";include("WA_Universal_Email/WAUE_post-item_1.php");
$RecipientEmail = "webmaster@bubbasmobileyardsales.com";include("WA_Universal_Email/WAUE_post-item_1.php");

//Send Mail All Entries
if (""!="") {
header("Location: ");
}
}
?>

thanks again for WebAssist Support,
Jim Balthrop

OK, glad you got it working.