PHP MYSQL Competition Creation

[fabzster]

Hi

We have a competition running that I would like to automate getting the results with PHP from my MYSQL DB.

I can run a query on the Database to get the following results

Consultant1 20 sales
Consultant2 10 sales
Consultant3 5 sales
Consultant4 1 sales
Consultant5 3 sales
Consultant6 8 sales
Consultant7 30 sales
Consultant8 17 sales

Now this is where it gets tricky for me........

For every 10 sales the consultant gets thier name entered in a draw, so if the have 30 they have 3 entries.

perhaps I need to add all the entries to a virtual table or array of sorts then randomly pick one as the winner.

Anyone have any ideas on how I should be going about this??

I value your assistance

[Biiim]

SELECT consultant,FLOOR(SUM(`numsales`)/10) AS `entries` FROM `salestable` GROUP BY consultant

<?php 
$Q = 'SELECT consultant,FLOOR(SUM(`numsales`)/10) AS `entries` FROM `salestable` GROUP BY consultant';
$R = mysql_query($Q);
$entries = array();
while($row = mysql_fetch_assoc($R)){
	if($row['entries'] > 0){
		$i = 0;
		while($i < $row['entries']){
			$entries[] = $row['consultant'];
			$i++;
		}
	}
}
echo "Total Entries: ".count($entries)."<br/>\r\n";
$entryBreakdown = array();
foreach($entries as $v){
	if(!ISSET($entryBreakdown[$v])){
		$entryBreakdown[$v] = 1;
	}else{
		$entryBreakdown[$v]++;
	}
}
echo "Breakdown by consultant:<br/>\r\n";
foreach($entryBreakdown as $k=>$v){
	echo "{$k} Has {$v} Entries<br/>\r\n";
}

echo "Random winners:<br/>\r\n";
$rand_keys = array_rand($entries, 3);
echo $entries[$rand_keys[0]] . "<br/>\r\n";
echo $entries[$rand_keys[1]] . "<br/>\r\n";
echo $entries[$rand_keys[2]] . "<br/>\r\n";
echo "Congrats!";
?>

[Biiim]

Just thought of one flaw, the same guy could win all 3.

echo "Random winners:<br/>\r\n";
$winners = array();
$numwinners = 3;
$i = 0;
while($i < $numwinners){
	$rand_key = array_rand($entries);
	if(!in_array($entries[$rand_key],$winners)){
		$winners[] = $entries[$rand_key];
		$i++;
	}
}
echo $winners[0] . "<br/>\r\n";
echo $winners[1] . "<br/>\r\n";
echo $winners[2] . "<br/>\r\n";
echo "Congrats!";

[diafol]

Depends on how your table is structured. Anyway, let's assume result resource fields: consultant, sales (raw number of sales - not floored)

$num_sales_per_entry = 10;
$num_winners = 3;

while($data = mysql_fetch_array($result)){
  $tries = floor($data['sales']/$num_sales_per_entry);
  for($x=0;$x<$tries;$x++){
    $entries[] = $data['consultant'];
  } 
}
shuffle($entries);
for($x=0;$x<$num_winners;$x++){
  echo "WINNER " . ($x + 1) . " = " $entries[$x] . "<br />";
}

Not tested. Unsure how random shuffle is too. This can give same winner three times too.

[Biiim]

my second post was a solution to keep picking till it has 3 different winners :)

[fabzster]

SELECT consultant,FLOOR(SUM(`numsales`)/10) AS `entries` FROM `salestable` GROUP BY consultant

<?php 
$Q = 'SELECT consultant,FLOOR(SUM(`numsales`)/10) AS `entries` FROM `salestable` GROUP BY consultant';
$R = mysql_query($Q);
$entries = array();
while($row = mysql_fetch_assoc($R)){
	if($row['entries'] > 0){
		$i = 0;
		while($i < $row['entries']){
			$entries[] = $row['consultant'];
			$i++;
		}
	}
}
echo "Total Entries: ".count($entries)."<br/>\r\n";
$entryBreakdown = array();
foreach($entries as $v){
	if(!ISSET($entryBreakdown[$v])){
		$entryBreakdown[$v] = 1;
	}else{
		$entryBreakdown[$v]++;
	}
}
echo "Breakdown by consultant:<br/>\r\n";
foreach($entryBreakdown as $k=>$v){
	echo "{$k} Has {$v} Entries<br/>\r\n";
}

echo "Random winners:<br/>\r\n";
$rand_keys = array_rand($entries, 3);
echo $entries[$rand_keys[0]] . "<br/>\r\n";
echo $entries[$rand_keys[1]] . "<br/>\r\n";
echo $entries[$rand_keys[2]] . "<br/>\r\n";
echo "Congrats!";
?>

Hi

Many thanks for the reply

I need only 1 winner each month, I just need to know the following

lets assume i get the below results.....

Consultant1 2
Consultant2 3
Consultant3 8
Consultant4 0
etc......

I know based on the amount of sales.... Consultant 2 should have 2 entries into the draw Consultant 3 8 entries. so their chances of coming up randomly should increase with the amount of entries they have, they should be weighted more somehow.

[diafol]

for 1 winner:

$num_sales_per_entry = 10;
while($data = mysql_fetch_array($result)){
  $tries = floor($data['sales']/$num_sales_per_entry);
  for($x=0;$x<$tries;$x++){
    $entries[] = $data['consultant'];
  } 
}
shuffle($entries);
echo "WINNER = {$entries[$x]}";

[fabzster]

for 1 winner:

$num_sales_per_entry = 10;
while($data = mysql_fetch_array($result)){
  $tries = floor($data['sales']/$num_sales_per_entry);
  for($x=0;$x<$tries;$x++){
    $entries[] = $data['consultant'];
  } 
}
shuffle($entries);
echo "WINNER = {$entries[$x]}";

Thanks for the prompt reply

How does this give more chance to consultants that have more entries though?

[fabzster]

my second post was a solution to keep picking till it has 3 different winners :)

Hi Thanks seems I am pretty close to a 100% solution with your help

I did however not ask the question correctly (My Appologies), Some consultants belong to stores that get an entry every 10 sales and some belong to stores that get entries every 15 sales, so we cannot do this in the query but in the PHP code itself.

Any ideas?

[fabzster]

I modified what you gave me in order to suite my needs, unfortunately this does not run sometimes

$Q = "SELECT COUNT(tblpolicies.PolicyNumber)AS Figures, tblpolicies.ConsultantFullName, tblstores.StoreType  FROM tblpolicies, tblstores
      WHERE tblPolicies.DateReceived BETWEEN '{$StartDate}' AND '{$EndDate}' AND tblpolicies.PolicyStatus='Active' AND
      ConsultantFullName!='HOUSE CONSULTANT' AND ConsultantFullName!='PINNACLE FINRITE' AND tblpolicies.StoreId=tblstores.StoreId
      GROUP BY tblpolicies.ConsultantFullName ORDER BY Figures DESC";

$R = mysql_query($Q);
$entries = array();
while($row = mysql_fetch_assoc($R)){
	
	
	If(($row['StoreType']) == "FLAGSHIP" || "SUPER LARGE")
				{
					$row['Figures'] = floor($row['Figures'] / 15); 
			
				}
				
	If(($row['StoreType']) == "LARGE" || "SUPER MEDIUM")
				{
					$row['Figures'] = floor($row['Figures'] / 10); 
			
				}
				
	If(($row['StoreType']) == "MEDIUM")
				{
					$row['Figures'] = floor($row['Figures'] / 8); 
			
				}
	If(($row['StoreType']) == "SMALL" || "KIOSK")
				{
					$row['Figures'] = floor($row['Figures'] / 5); 
			
				}
	
	
	
	if($row['Figures'] > 0){
		$i = 0;
		while($i < $row['Figures']){
			$entries[] = $row['ConsultantFullName'];
			$i++;
		}
	}
	
}

echo "Total Entries: ".count($entries)."<br/>\r\n";
$entryBreakdown = array();
foreach($entries as $v){
	if(!ISSET($entryBreakdown[$v])){
		$entryBreakdown[$v] = 1;
	}else{
		$entryBreakdown[$v]++;
	}
}
echo "Breakdown by consultant:<br/>\r\n";
foreach($entryBreakdown as $k=>$v){
	echo "{$k} Has {$v} Entries<br/>\r\n";
}
 
echo "Random winners:<br/>\r\n";
$winners = array();
$numwinners = 3;
$i = 0;
while($i < $numwinners){
	$rand_key = array_rand($entries);
	if(!in_array($entries[$rand_key],$winners)){
		$winners[] = $entries[$rand_key];
		$i++;
	}
}
echo $winners[0] . "<br/>\r\n";
echo $winners[1] . "<br/>\r\n";
echo $winners[2] . "<br/>\r\n";
echo "Congrats!";

[Biiim]

in what way does it not run properly?

also is this "COUNT(tblpolicies.PolicyNumber)" if a policy number can only ever be 1 sale towards an entry it is correct otherwise multiple sales will be counted as 1

[diafol]

How does this give more chance to consultants that have more entries though?

The shuffle function orders the entries randomly. Then you just pick the first entry in the new randomized list - a bit like taking a card from the top of a shuffled deck. If there are many kings say, and only one of everything else, there will be a greater probability that a king will be at the top of the deck. It's not very sophisticated, but seems straightforward.

THis piece of code may be problematic:

while($row = mysql_fetch_assoc($R)){
 
 
	If(($row['StoreType']) == "FLAGSHIP" || "SUPER LARGE")
				{
					$row['Figures'] = floor($row['Figures'] / 15); 
 
				}
 
	If(($row['StoreType']) == "LARGE" || "SUPER MEDIUM")
				{
					$row['Figures'] = floor($row['Figures'] / 10); 
 
				}
 
	If(($row['StoreType']) == "MEDIUM")
				{
					$row['Figures'] = floor($row['Figures'] / 8); 
 
				}
	If(($row['StoreType']) == "SMALL" || "KIOSK")
				{
					$row['Figures'] = floor($row['Figures'] / 5); 
 
				}
 
 
 
	if($row['Figures'] > 0){
		$i = 0;
		while($i < $row['Figures']){
			$entries[] = $row['ConsultantFullName'];
			$i++;
		}
	}
 
}

You have multiple standalone ifs. Maybe a switch with a default setting would be better:

while($row = mysql_fetch_assoc($R)){
    switch($row['StoreType']){
      case "FLAGSHIP":
      case "SUPER LEAGUE":
        $fig = floor($row['Figures'] / 15;
        break;
      case "LARGE":
      case "SUPER MEDIUM":
        $fig = floor($row['Figures'] / 10;
        break;
      case "MEDIUM":
        $fig = floor($row['Figures'] / 8;
        break;
      case "SMALL":
      case "KIOSK":
        $fig = floor($row['Figures'] / 5;
        break;
      default:
        $fig = 0;
        break;
    }
    if($fig > 0){
      $i = 0;
      while($i < $fig){
	$entries[] = $row['ConsultantFullName'];
	$i++;
      }
    }
}

Equally this could be done with a if/elseif/else (many elseifs).
However, I understood that you just wanted one winner?
Is it the query that doesn't run or the subsequent code? If you throw in an echo mysql_num_records() just for now, that'll tell you if it's an SQL issue, if there isn't a specific error.

[fabzster]

Many Thanks for the assistance guy's

I used the code I posted earlier, For Some reason when I run that in Chrome it times out eventually .... Very Weird!

When I run the code in IE, it runs perfectly, I cannot understand why though