0

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??

1
Contributor
2
Replies
9
Views
2 Years
Discussion Span
Last Post by showman13
0

It just dawned on me... my intention for that first query would be to return a single number, telling me how many unique mem_ids are found by the query, but instead, it is giving me a listing of the number of individual records in the table related to each unique mem_id

count( mem_id )
13
7
4
23
3
11
7
5
1
7
5

Which I would assume is the issue...

But how do I format the query to just get the number of unique mem_ids?

I guess that is the real question.

0

I answered my own question AGAIN...
Seems that my mind actually kicks into gear sometimes Only after posting a question in here...

the answer:

SELECT count( DISTINCT mem_id ) 
FROM banner_key 
WHERE bid >0 
AND created > '2015-02-12' 

So simple it hurts...

Again, I apologize for bothering you with a question that I could ultimately answer for myself.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.