Good Morning,
I am trying to create a function that will return a single value that represents the customer that will receive a reward...
The source of the data that makes the determination is the banner_key table (partial - sample data)
key_id mem_id bid created status keylock last_update
144 10000022 131 3/11/2015 15:33 C V657QD26 3/13/2015 9:48
141 10000022 128 3/11/2015 15:47 C VC5Z2769 3/11/2015 14:55
6 10000006 6 2/3/2015 15:16 C 8AN332P7 3/6/2015 9:46
7 10000006 7 2/3/2015 15:16 A 2M78Q68U 0000-00-00 00:00:00
8 10000014 8 2/3/2015 15:16 A 9VE685A8 0000-00-00 00:00:00
9 10000014 9 2/3/2015 15:16 A A3H56S85 0000-00-00 00:00:00
10 10000023 10 2/3/2015 15:16 A 7M2X7V37 0000-00-00 00:00:00
11 10000001 11 2/3/2015 15:16 A 97UH24G2 0000-00-00 00:00:00
12 10000001 12 2/3/2015 15:16 A 97TJ3U86 0000-00-00 00:00:00
13 10000005 0 2/3/2015 15:16 A 32F352EU 0000-00-00 00:00:00
14 10000001 14 2/3/2015 15:16 A 7Y386BB6 0000-00-00 00:00:00
15 10000001 15 2/3/2015 15:16 A 58S47KL3 0000-00-00 00:00:00
.......
This is the function call
$created = date("Y-m-d H:i:s", time()) ;// temporary
$selected = random_30($created);
print
'Selected member ID '.$selected[0].
'<br>number '.$selected[2].
'<br>out of '.$selected[1].' possibilies.'.
'<br>beginning '.$selected[3].'<br>
';
Sample Results:
Selected member ID 10000002
number 1
out of 13 possibilies.
beginning
Selected member ID 0
number 11
out of 13 possibilies.
beginning
Selected member ID 10000019
number 6
out of 13 possibilies.
beginning
This is the function as I currently have it.
// ***********************************************************************
// function to Randomly select customer to receive Cash Reward
// Random selection of 1 customer that has purchased in past 30 days
// based on banner_key records
// function called with $created as Now in datetime format
function random_30($created){
$startdate = add_days($created,-30); // Get date 30 days ago
$sql = "
SELECT count( mem_id )
FROM banner_key
WHERE bid >0
AND created > ".$startdate."
GROUP BY mem_id
" ;
$result = mysql_query($sql);
$cust_row = mysql_fetch_row($result) ;
$cust_ct = $cust_row[0]; // number of unique mem_id recs in last 30 days
// Determine random number from 1 to unique mem_id customer count
$random_select = rand(0,$cust_ct-1);
$sql_b = "
SELECT mem_id
FROM banner_key
WHERE bid >0
AND created > ".$startdate."
GROUP BY mem_id
LIMIT ".$random_select.",1
";
$result_b=mysql_query($sql_b);
if ($row_b=mysql_fetch_array($result_b)){
$random_cust = $row_b[0];
}else{
$random_cust = 0;
}
$answer[0]= $random_cust; // mem_id selected from list
$answer[1]= $cust_ct; // total number of unique mem_ids
$answer[2]= $random_select; // number selected at random
$anawer[3]= $startdate; // date from 30 days ago
// if $randum_cust zero, no member selected
return $answer;
}
The function works (for the most part), but returns some odd / questionable results occasionally in testing.
This is the generated Query in the script:
SELECT count( mem_id )
FROM banner_key
WHERE bid >0
AND created > '2015-02-12'
GROUP BY mem_id
The issue appears to be in the first query...
When I run it as a part of the script (as above), I get a different result than I do if I run it in PHP MyAdmin
As it stands right now with the couple hundred records in that table, there are in fact 11 Unique mem_ids so the random number selector should choose between 0 - 10 as a result.
But when run in the script, it comes back with 13 unique mem_ids 0 - 12 (no clue where the other 2 come from), so when it does the selection of the mem_id, every time it selects the 11 or the 12, I get a zero for the result of the mem_id.
*NOTE - The one thing that I did notice when manually running the query is that the first result in the result set (the lowest mem_id record) has 13 records in the table.
So, that is possibly where the 13 is coming from, but I don't understand why.
Can someone save me from myself, before I go completely crazy??