Limit coupon use to three times?

Hi Forums,

I have a new issue. My client wants to make a coupon available to registered members, and they want to allow the members to use the same coupon code three times in a calendar year (the code will change the following years).

I have no idea even where to start with this one, has anyone tried this yet, or have any suggestions?

Thanks,
bec

You will have to add an extra row to the Users table in the DB. Something like coup_use.

Then when a coupon is used there will have to be an an if/else function applied to the Users table after you call it up in a SELECT * from Users recordset.

1. Check and see if the coupon has been used before.
2. If yes is it < 3.
3. if yes, the coupon gets an UPDATE applied and then is sent to the cart or checkout where they can see the discount.
4. If no, then you have to redirect to Sorry page that says Sorry, over the limit or something like that.

It might look something like this in theory.

if($coup_use != "") {
if($coup_use <3) {
echo header("Location: update.php")
} else {
echo header("Location: sorry.php")
}
}

The update page would just be code, something like SET Users.coup_use ++ and then redirect to cart or payoff page.

Also, make sure the coup_use table is set to NULL when users sign up for the first time.

There may be easier ways, but this is what I would do.

Hope you find it useful.
Jay

Thanks, Jay.

Some of that is clear, but I can't seem to get the visitors table to update! I am trying to do it using DA's Update Record.

Here is what the code looks like at the moment:

<?php
// WA Application Builder Update
if (isset($_POST["submit"])) // Trigger
{
$WA_connection = $connCedar;
$WA_table = "visitors";
$WA_redirectURL = "";
$WA_keepQueryString = false;
$WA_indexField = "VisitorID";
$WA_fieldNamesStr = "coup_use";
$WA_fieldValuesStr = "++";
$WA_columnTypesStr = "none,none,NULL";
$WA_comparisonStr = "=";
$WA_fieldNames = explode("|", $WA_fieldNamesStr);
$WA_fieldValues = explode("|", $WA_fieldValuesStr);
$WA_columns = explode("|", $WA_columnTypesStr);

$WA_where_fieldValuesStr = "".$row_WAATKvisitors['VisitorID'] ."";
$WA_where_columnTypesStr = "none,none,NULL";
$WA_where_comparisonStr = "=";
$WA_where_fieldNames = explode("|", $WA_indexField);
$WA_where_fieldValues = explode("|", $WA_where_fieldValuesStr);
$WA_where_columns = explode("|", $WA_where_columnTypesStr);
$WA_where_comparisons = explode("|", $WA_where_comparisonStr);

$WA_connectionDB = $database_connCedar;
mysql_select_db($WA_connectionDB, $WA_connection);
if (!session_id()) session_start();
$updateParamsObj = WA_AB_generateInsertParams($WA_fieldNames, $WA_columns, $WA_fieldValues, -1);
$WhereObj = WA_AB_generateWhereClause($WA_where_fieldNames, $WA_where_columns, $WA_where_fieldValues, $WA_where_comparisons );
$WA_Sql = "UPDATE `" . $WA_table . "` SET " . $updateParamsObj->WA_setValues . " WHERE " . $WhereObj->sqlWhereClause . "";
$MM_editCmd = mysql_query($WA_Sql, $WA_connection) or die(mysql_error());
if ($WA_redirectURL != "") {
if ($WA_keepQueryString && $WA_redirectURL != "" && isset($_SERVER["QUERY_STRING"]) && $_SERVER["QUERY_STRING"] !== "" && sizeof($_POST) > 0) {
$WA_redirectURL .= ((strpos($WA_redirectURL, '?') === false)?"?":"&").$_SERVER["QUERY_STRING"];
}
header("Location: ".$WA_redirectURL);
}
}
?>

Thanks!
bec

HI,
This MAY work for you. I've used similar code to reset numbers or coupon use's. Make sure that the coup_use table is set to INT and that you are using POST and are submitting off the button the user presses to use the coupon. You may have to play with it a bit. Let me know how it worked..or not.

<?php
// This is just a php page with no html and will process the coupon request, update the db and send the user where they need to go. You could call it update_coup.php
cart.php = $WA_redirectURL; // where ever the user needs to end up if success
require_once("Connections/your_db_file.php); // include the Connection file
if (!session_id()) session_start(); // make sure a Session is started
if (isset($_POST["submit"])) // Trigger - Make sure the form is set to POST as a method and this page as the action
{
$id = $_SESSION['VisitorID'];
$WA_connectionDB = $database_connCedar;
mysql_select_db($WA_connectionDB, $WA_connection);
$Sql = "UPDATE visitors SET visitors.coup_use = visitors.coup_use+1 WHERE visitor.VisitorID = $id";
$query = mysql_query($Sql, $WA_connection) or die(mysql_error());
$row = mysql_fetch_assoc($query);
}
if($row =< 3) {
header("Location: ".$WA_redirectURL);
} elseif($row > 3) {
header("Location: sorry.php");// or where ever the over the limit page is.
}
?>

Best,
Jay

Hey Jay, thanks for the quick response. However, I am trying to keep the number of pages low (client request) and I wonder if we could add your code to the page somehow?

This is how things are structured: The coupon form is only on the cart page, and only visible to members. Once you enter the code, it refreshes the cart page, and shows the updated total.

Could we possibly add it in somewhere that it would just echo a negative response under the coupon form? And where would I add your code to see if we could do that?

I forgot to mention that, other than the DB updating, everything works stellar.

Again, thanks!
-b.

Hey b,

Here is another way to do it.

In your DB make the default value for the coup_use table 3. We'll just count backwards.

Then use the Dreamweaver built-in update behavior to update the coupon. The behavior will be created in a Wizard and placed on your page at the right place, most likely. I would need to see the all the page code to be sure.

You will then want to use the Submit Button for that update to be the button the user pushes to validate their coupon.

Here is the update code you need - it goes near the top, but under $theValue switch()'s code generated by Dreamweaver, also under the start_session() stuff:
<?php
$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
$updateSQL = sprintf("UPDATE visitors SET coup_use=-1 WHERE VisitorID=%s",
GetSQLValueString($_POST['coup_use'], "int"),
GetSQLValueString($_POST['VisitorID'], "int"));

mysql_select_db($database_db_name, $db_name);//Make sure you change both to your DB
$Result1 = mysql_query($updateSQL, $db_name) or die(mysql_error());//Make sure you change the $db_name to your DB

$updateGoTo = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
$updateGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $updateGoTo));
}
?>

Then under the DOC Type in the <body> is the coupon form:

<form name="form1" action="<?php $_SERVER['PHP_SELF'];?>" method="post">
<input name="coup_use" type="text" value="<?php echo htmlentities($row_rsCoupUpdate['coup_use'], ENT_COMPAT, 'UTF-8'); ?>" size="5" readonly="readonly" /><br />
<input type="submit" value="Use a Coupon" />
<input type="hidden" name="VisitorID" value="<?php echo $row_rsCoupUpdate['VisitorID']; ?>" />
<input type="hidden" name="MM_update" value="form1" />
</form>

At that point you would have a conditional if/else statement that if the # was =<0 a message (in place of the form) would say All Coupons Used or something.

Make sure you change the DB name in the code and set your DB coup_use default value to 3.

This (minus style) should work as written. Let me know.

Best,
Jay

Hey Jay,

I'm trying to get it implemented. Unfortunately this is an older site and I really haven't had to dive into PHP since, so my brain is rusty.

So far it's not working, but I am probably doing something wrong. I'm sure there is a WA wizard solution to this yet that I haven't figured out.

Thanks again!
-b.

Hey Jay,

Here is what I tried this morning and got a weird result. I set the user coupon use to 2 just to see what would happen. I made sure that the session value to pull the coup_use value is set to page load. On the press of the "use coupon" button, I set a DA update single record call, to add on to the coupon use session value. I used both "++" and "+1" (without the quotes, of course), and get this: they reset the coup_use column to 0!

What the heck? :/
-b.

Does anyone have any suggestions? I really need to get this working correctly.

Thank you!
-b.

If you can please post back and include a brief summary of the way you have it now implemented and what the result is, this way someone else will be able to pick up and try to give you some pointers on this. Make sure to include some of the details for how you have this setup currently.