PHP MYSQL Competition Creation

Question

fabzster 0 Light Poster

12 Years Ago

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

competition mysql php

Edited 12 Years Ago by fabzster because: n/a

3 Contributors
12 Replies
297 Views
2 Days Discussion Span
Latest Post 12 Years Ago Latest Post by fabzster

All 12 Replies

Biiim 182 Junior Poster

12 Years Ago

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 182 Junior Poster

12 Years Ago

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

12 Years Ago

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.

Edited 12 Years Ago by diafol because: n/a

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Biiim 182 Junior Poster · Answer 1 · 2012-03-06T00:30:05+00:00

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

fabzster 0 Light Poster · Answer 2 · 2012-03-06T13:13:45+00:00

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 · Answer 3 · 2012-03-06T13:32:02+00:00

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 0 Light Poster · Answer 4 · 2012-03-06T13:49:10+00:00

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 0 Light Poster · Answer 5 · 2012-03-06T14:18:18+00:00

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 0 Light Poster · Answer 6 · 2012-03-06T20:28:49+00:00

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 182 Junior Poster · Answer 7 · 2012-03-06T22:00:01+00:00

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 · Answer 8 · 2012-03-07T00:54:58+00:00

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 0 Light Poster · Answer 9 · 2012-03-07T16:09:43+00:00

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

PHP MYSQL Competition Creation

Recommended Answers Collapse Answers

All 12 Replies

Recommended Answers