Infinite sharing system (PHP/MySQLi)
- by Toine Lille
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 initially 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 of that, etc, creating a tree or pyramid that way that could be infinite.
Initial customer ($1.35 discount: 2 new+3 visits + half of 1 new+2 visits)
Visitor ($0)
Visitor ($0)
New customer ($0.60)
Visitor ($0)
Visitor ($0)
Newer customer ($0)
New customer ($0)
Visitor ($0)
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 (also 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 customer's id. Visits and new customers overlap, as visits will always occur before the new customer does. That's fine.
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 system). If an IP is already in the sharing or customer tables, 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 values 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 the initial customer they 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!
