I'm working on a discount system for whichever customer shares a product and brings in new customers. Each unique visit = $0.05 off, each new customer = $0.50 off (it's a cheap product so yeah, no big numbers).
When a new customer shares the site, the customer who initially was responsible for the new customer (if any) will get half of the new customer's discount as well. The initial customer would get a fourth for the next level and the new customer half, etc, creating a tree or pyramid to the infinite.
The customers are saved along with their IP addresses (bin2hex(inet_pton)) in a database table (customers) with info like a unique id, e-mail address and first date/time the purchased a product (= time of registration).
The shares are saved in a separate table within the same database (sharing). Each unique IP addresses that visits the site creates a new row featuring the IP address (again saved as bin2hex(inet_pton)), the id of the customer who shared it and the date/time of the visit.
Sharing goes via URL featuring a GET element containing the initial customer's id. Visits and new customers overlap as visits will always occur before the new customer does. That is fine and of no worries.
The date/times are used just to make it a little more secure (I also use the IP along with cookies to see if people cheat the sharing system, which is definitely not impossible but I don't care). If an IP is already in sharing or customers, it does not count and will not create a new entry.
Now the problem is, how to make the infinity happen and apply the different discount values (always exactly half) to it? That's all I'd need to know.
It needs to calculate the discount for each customer separately, but also allow for monitoring altogether (though that's just a matter of passing all ID's through it).
I figured I'd start (after the database connection) with
$stmt = $con->prepare('SELECT ip,datetime FROM sharing WHERE sender=?');
$stmt->bind_param('i',$customerid);
$stmt->execute();
$stmt->store_result();
$discount = $discount + ($stmt->num_rows * 0.05);
$stmt->bind_result($ip,$timeofsharing);
to translate all the visits to $0.05 of discount each.
To check for the new customers that came from these visits, I wrote the following:
while ($sql->fetch()) {
$stmt2 = $con->prepare("SELECT datetime FROM users WHERE ip=?");
$stmt2->bind_param('s',$ip);
$stmt2->execute();
$stmt2->store_result();
$stmt2->bind_result($timeofpurchase);
Followed by a little more security comparing the datetimes:
while ($stmt2->fetch()) {
if (strtotime($timeofpurchase) < strtotime($timeofsharing)) {
$discount = $discount + $0.50;
}
}
}
}
But this is just for the initial customer's direct results. If I'd want to check for the next level, I'd basically have to put the exact same check and loop in itself, checking each new customer and visit the initial customer brought to the site, and then for the next level again to check all of the newer customers, etc, etc.
What to do? / Where to go? / What would be the correct practice for this?
Thanks!
Lets assume your initial url is like : visitor.php?uid=1234567890 Also, I'll assume that you have discount column in the table which stores all your users.
At this point, in visitor.php you should do the following :
<?php
define('SECRET','The Line Secret Key');
function encryptCookie($value, $key) {
if(!$value){return false;}
$text = $value;
$iv_size = mcrypt_get_iv_size(MCRYPT_RIJNDAEL_256, MCRYPT_MODE_ECB);
$iv = mcrypt_create_iv($iv_size, MCRYPT_RAND);
$crypttext = mcrypt_encrypt(MCRYPT_RIJNDAEL_256, $key, $text, MCRYPT_MODE_ECB, $iv);
return trim(base64_encode($crypttext)); //encode for cookie
}
function decryptCookie($value, $key) {
if(!$value){return false;}
$crypttext = base64_decode($value); //decode cookie
$iv_size = mcrypt_get_iv_size(MCRYPT_RIJNDAEL_256, MCRYPT_MODE_ECB);
$iv = mcrypt_create_iv($iv_size, MCRYPT_RAND);
$decrypttext = mcrypt_decrypt(MCRYPT_RIJNDAEL_256, $key, $crypttext, MCRYPT_MODE_ECB, $iv);
return trim($decrypttext);
}
function getValueForCookie($arr) {
$tmp = json_encode($arr);
return encryptCookie($arr,SECRET);
}
function getValueFromCookie($string) {
$tmp = decryptCookie($string,SECRET);
return json_decode($string,true);
}
if(isset($_GET['uid']) && strlen(trim($_GET['uid'])) > 0 && !isset($_COOKIE['Referal']) {
//assuming uid is int only
$uid = intval($_GET['uid']);
$cookie = array();
$stmt2 = $con->prepare("SELECT * FROM users WHERE uid = :uid");
$ret = $stmt2->execute(array(":uid" => $_GET['uid']));
if($ret && $stmt2->rowCount() == 1) {
$cookie['uid'] = $uid;
$discount = 0.05;
$update = $con->prepare("UPDATE users SET discount = discount + :discount WHERE uid = :uid");
$ret2 = $update->execute(array(":uid" => $uid,":discount" => $discount));
if($ret2) {
$cookie['visit_discount'] = 1;
} else {
$cookie['visit_discount'] = 0;
}
}
setcookie("Referal", getValueForCookie($cookie), 2147483647);
}
?>
Now, when the visitor registers a new account, after you have actually inserted the user's new data in the database, you will do the following (you will need the functions in the first file in here as well) :
<?php
$cookie_values = array();
if(isset($_COOKIE['Referal'])) {
$cookie_values = getValueFromCookie($_COOKIE['Referal']);
}
/*
Create the new user's account, use $cookie_values['uid'] as the referal's user id
Once you create the account and make sure its created do the following
*/
if(isset($cookie_values['uid'])) {
$stmt2 = $con->prepare("SELECT * FROM users WHERE uid = :uid");
$ret = $stmt2->execute(array(":uid" => $cookie_values['uid']));
if($ret && $stmt2->rowCount() == 1) {
$row = $stmt2->fetch(PDO::FETCH_ASSOC);
$discount = 0.5;
$update = $con->prepare("UPDATE users SET discount = discount + :discount WHERE uid = :uid");
$ret2 = $update->execute(array(":uid" => $cookie_values['uid'],":discount" => $discount));
if($ret2) {
$cookie_values['registration_discount'] = 1;
} else {
$cookie_values['registration_discount'] = 0;
}
setcookie("Referal", getValueForCookie($cookie_values), 2147483647);
if(!is_null($row['referal'])) {
while(!is_null($row['referal'])) {
$discount = $discount / 2;
$update = $con->prepare("UPDATE users SET discount = discount + :discount WHERE uid = :uid");
$ret2 = $update->execute(array(":uid" => $row['referal'],":discount" => $discount));
$select = $con->prepare("SELECT referal FROM users WHERE uid = :uid LIMIT 1");
$ret_select = $select->execute(array(":uid" => $row['referal']));
if($ret_select && $select->rowCount() == 1) {
$row = $select->fetch(PDO::FETCH_ASSOC);
} else {
$row['referal'] = null;
}
}
}
}
}
?>
Haven't tested the above, but it should work fine if my assumptions were correct to begin :D