Coupon script

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
n_scorpio
Forum Newbie
Posts: 4
Joined: Mon Dec 01, 2008 7:00 pm
Location: Detroit

Coupon script

Post by n_scorpio »

I have a simple MySQL table for managing coupons.

I would like to have an option to allow a coupon for _ALL_ users or just _ONE_ Customer.

However I would like to track if the coupon was used or not.

Also, I would like to use `enabled` to enable/disable a specific coupon in the database.

Here's my table and code:

Code: Select all

 
CREATE TABLE `coupons` (
  `id` int(9) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  `code` varbinary(50) NOT NULL default '',
  `type` char(1) NOT NULL default '',
  `amount` int(4) NOT NULL default '0',
  `exp_date` date NOT NULL,
  `enabled` tinyint(4) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `exp_date` (`exp_date`),
  KEY `code` (`code`),
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=131 ;
 
And some other code borrowed from:

Code: Select all

 
// 0 = Enabled - 1 = Disabled
if ($enabled == "1") {
    
    $name = "Invalid";
    $code = "Invalid Coupon";
    $type = "d";
    $amount = 0.00;
 
} // I have a lot of chaos going on here...
 
$todays_date = date("Y-m-d");
$today = strtotime($todays_date);
$expiration_date = strtotime($exp_date);
 
if ($expiration_date > $today) {
 
    //$valid = "yes"; // The coupon is valid and all is GREAT!
    // Apply the coupon code and use it towards the order
    
} else {
 
    //$valid = "no"; // The coupon has expired
    $name = "Invalid";
    $code = "Invalid Coupon";
    $amount = 0.00;
}
 
if ($code) {
    $query        = "SELECT * FROM coupons WHERE code='$code'";
    $result        = mysql_query($query) or die("There was an error getting the coupon code!" . mysql_error());
    $num_rows = mysql_num_rows($result);
}
 
if ($num_rows > 0) {
    
    $name       = mysql_result($result,0,"name");
    $code       = mysql_result($result,0,"code");
    $type       = mysql_result($result,0,"type");
    $amount     = mysql_result($result,0,"amount"); 
    $exp_date       = mysql_result($result,0,"exp_date");
 
} else {
    
    $name = "Invalid";
    $code = "Invalid Coupon";
    $type = "d";
    $amount = 0.00;
    $exp_date = "0000-00-00";
}
 
header("Content-Type: application/xml; charset=UTF-8");
     
// Start the XML Output
echo "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>";
 
echo "<content>\n";
echo "\t<name>$name</name>\n";
echo "\t<code>$code</code>\n";
echo "\t<type>$type</type>\n";
echo "\t<amount>$amount</amount>\n";
echo "\t<exp_date>$exp_date</exp_date>\n";
echo "</content>\n";
?>
 
Any help is greatly appreciated.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Coupon script

Post by requinix »

n_scorpio wrote:I would like to have an option to allow a coupon for _ALL_ users or just _ONE_ Customer.
How I'd do it:

Add a field to your coupons table for the user. It can be a user ID, or a name, or whatever - some unique identifier. The field can be null. You could change some of those KEYs to reflect it, like KEY(code,user) and KEY(name,user).
For normal coupons that field is null, but for specific users it has a value.

If the coupon code remains after the person uses it then you can issue one query like

Code: Select all

SELECT * FROM table WHERE code = "$code" AND (user IS NULL OR user = "$user") LIMIT 1
If the code should not remain (one-time use only) then you have two queries:

Code: Select all

SELECT * FROM table WHERE code = "$code" AND user IS NULL LIMIT 1
# and if that doesn't return any results
DELETE FROM table WHERE code = "$code" AND user = "$user" LIMIT 1
# then use mysql_affected_rows to determine if a row was deleted
# (in which case there was a match)
For the whole enabled/disabled you just add another condition to the queries:

Code: Select all

...WHERE conditions AND enabled = 0 LIMIT 1
(You know, typically true/false is represented with non-zero/zero. If a coupon is enabled it'd make sense for enabled=1. But that's just my opinion)

PS: you can incorporate the expiration date into the query too:

Code: Select all

SELECT fields FROM table WHERE exp_date >= DATE(NOW()) LIMIT 1
[/color]
n_scorpio
Forum Newbie
Posts: 4
Joined: Mon Dec 01, 2008 7:00 pm
Location: Detroit

Re: Coupon script

Post by n_scorpio »

I am slightly confused what you mean about the Index Key's?

-n_scorpio
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Coupon script

Post by requinix »

In your CREATE TABLE you define some keys:

Code: Select all

 KEY `exp_date` (`exp_date`),
  KEY `code` (`code`),
  KEY `name` (`name`)
In your position, since the code and name won't be taken alone (you'd be searching according to the user as well) you can redefine those keys as

Code: Select all

 KEY `exp_date` (`exp_date`),
  KEY `code_user` (`code`, `user`),
  KEY `name_user` (`name`, `user`)
If you don't understand, don't worry about it and just let them be.
n_scorpio
Forum Newbie
Posts: 4
Joined: Mon Dec 01, 2008 7:00 pm
Location: Detroit

Re: Coupon script

Post by n_scorpio »

How about an ALTER for the table by any chance? :)
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Coupon script

Post by requinix »

In that case, just leave the KEYs as they are now. It's not a problem or anything.

Code: Select all

ALTER TABLE `table` ADD COLUMN `field` `type` AFTER `column`
table=the table name, field=what you want to call the field, type=the type of data (int, varchar, etc), column=the field before where this new field should be inserted (if you want it at the end just remove the whole "AFTER column" bit).

"field" should be a reference to another field in another table, like your users table. "type" is the same type of data used there.
So if you want to relate the coupon to a user ID, and your users table looks like

Code: Select all

CREATE TABLE `users` (
    `userid` INT NOT NULL auto_increment,
    ....
then "type" should also be "INT". Do not copy the "NOT NULL" too.
"field" can be anything you want to call it, but I'd suggest whatever name you use in the other table ("userid" in this example).

Example:

Code: Select all

ALTER TABLE `coupons` ADD COLUMN `userid` INT UNSIGNED AFTER `code`
will result in a table that looks like

Code: Select all

CREATE TABLE `coupons` (
  `id` int(9) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  `code` varbinary(50) NOT NULL default '',
  `userid` int(9) default NULL,
  `type` char(1) NOT NULL default '',
  `amount` int(4) NOT NULL default '0',
  `exp_date` date NOT NULL,
  `enabled` tinyint(4) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `exp_date` (`exp_date`),
  KEY `code` (`code`),
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=131 ;
n_scorpio
Forum Newbie
Posts: 4
Joined: Mon Dec 01, 2008 7:00 pm
Location: Detroit

Re: Coupon script

Post by n_scorpio »

Thank you for all the help, I am stuck now on the logic for reading the coupon to check against it.
Post Reply