[SOLVED] logic help for a contest system

[johnwayne77]

here is my situation:

 

i have a prepay online store and i advertised a contest to win a free code when buying at least 5 codes during 24.11-24.12 2008 period.

 

now this is my table:

 

orders_id 	customers_id 	customers_name 	customers_company 	customers_street_address 	customers_suburb 	customers_city 	customers_postcode 	customers_state 	customers_country 	customers_telephone 	customers_email_address 	customers_address_format_id 	delivery_name 	delivery_company 	delivery_street_address 	delivery_suburb 	delivery_city 	delivery_postcode 	delivery_state 	delivery_country 	delivery_address_format_id 	billing_name 	billing_company 	billing_street_address 	billing_suburb 	billing_city 	billing_postcode 	billing_state 	billing_country 	billing_address_format_id 	payment_method 	cc_type 	cc_owner 	cc_number 	cc_expires 	last_modified 	date_purchased 	orders_status 	orders_date_finished 	currency 	currency_value

 

i don't have a sql logic to : select all orders depending on date_purchased field which is in '2008-11-21 21:47:45' format, then calculate total number of orders for each of the customers in that period, depending on customers_id and orders_id i guess, then make a list of the customers that have the number of orders above or equal to 5 and finally randomly select one.

 

any ideas?

[rhodesa]

try this:

SELECT customer_id FROM orders WHERE COUNT(order_id) > 5 GROUP BY customer_id ORDER BY RAND() LIMIT 1

[johnwayne77]

this is what i figure out by now:

 

<?
    
    $result = mysql_query("SELECT customers_id
     , customers_name
 , customers_email_address
 , billing_state
 , customers_telephone
     , COUNT(orders_id) AS number_of_orders
  FROM orders
WHERE date_purchased >= '2008-12-01'
   AND date_purchased  < '2008-12-25'
GROUP
    BY customers_id
HAVING number_of_orders >= 5
");
   while($row = mysql_fetch_array($result)); {
       
	$query = mysql_query("SELECT * FROM tombola");
	$queryrows = mysql_num_rows($query);

	if ($queryrows > 0) {
	echo "Castigatorul tombolei de craciun a fost deja desemnat!<br>";
	$past = mysql_query("SELECT * from tombola");
	while ($row2 = mysql_fetch_array($past)) {
        echo "Castigatorul este ".$row2["customers_name"]."(".$row2["customers_email_address"].")<br><br>Telefon: ".$row2["customers_telephone"]."<br>Oras: ".$row2["billing_state"]."  <br><br>".$row2["customers_name"]." a comandat ".$row2["number_of_orders"]." coduri!";
	}

	}else {
	mysql_query("INSERT into tombola VALUES (".$row['customers_name'].", ".$row['customers_email_address'].", ".$row['billing_state'].", ".$row['customers_telephone'].", ".$row['number_of_orders'].") ");  
	$now = mysql_query("SELECT * from tombola");
	while ($row3 = mysql_fetch_array($now)) {
        echo "Castigatorul este ".$row3["customers_name"]."(".$row3["customers_email_address"].")<br><br>Telefon: ".$row3["customers_telephone"]."<br>Oras: ".$row3["billing_state"]."  <br><br>".$row3["customers_name"]." a comandat ".$row3["number_of_orders"]." coduri!";
	}
	}

        }
        
?>

 

but something is wrong.. i echo nothing on the screen and the table doesn't update

[rhodesa]

that query will fail, because you need to group on all those items. i added some debugging too:

 

$result = mysql_query("SELECT customers_id,
    customers_name,
    customers_email_address,
    billing_state,
    customers_telephone,
    COUNT(orders_id) AS number_of_orders
  FROM orders
  WHERE date_purchased BETWEEN '2008-12-01' AND '2008-12-25'
  GROUP BY customers_id,
    customers_name,
    customers_email_address,
    billing_state,
    customers_telephone
HAVING number_of_orders >= 5
") or die(mysql_error());

[johnwayne77]

i got to this version:

 

<?
    
    $result = mysql_query("SELECT customers_id
     , customers_name
 , customers_email_address
 , billing_state
 , customers_telephone
     , COUNT(orders_id) AS number_of_orders
  FROM orders
WHERE date_purchased >= '2008-12-01'
   AND date_purchased  < '2008-12-25'
GROUP BY customers_id,
    customers_name,
    customers_email_address,
    billing_state,
    customers_telephone
HAVING number_of_orders >= 5
ORDER
    BY RAND() LIMIT 1
");
   while($row = mysql_fetch_array($result)); {
       
	$query = mysql_query("SELECT * FROM tombola");
	$queryrows = mysql_num_rows($query);
	while($row2 = mysql_fetch_array($query)); {
	if ($queryrows > 0) {
	echo "Castigatorul tombolei de craciun a fost deja desemnat!<br>";
	//$past = mysql_query("SELECT * from tombola");
//	while ($row2 = mysql_fetch_array($past)) {
        echo "Castigatorul este ".$row2["customers_name"]."(".$row2["customers_email_address"].")<br><br>Telefon: ".$row2["customers_telephone"]."<br>Oras: ".$row2["billing_state"]."  <br><br>".$row2["customers_name"]." a comandat ".$row2["number_of_orders"]." coduri!";
	//}

	}else {
	$result2 = mysql_query("SELECT customers_id
     , customers_name
 , customers_email_address
 , billing_state
 , customers_telephone
     , COUNT(orders_id) AS number_of_orders
  FROM orders
WHERE date_purchased >= '2008-12-01'
   AND date_purchased  < '2008-12-25'
GROUP BY customers_id,
    customers_name,
    customers_email_address,
    billing_state,
    customers_telephone
HAVING number_of_orders >= 5
ORDER
    BY RAND() LIMIT 1
");
while($row5 = mysql_fetch_array($result2)); {
	mysql_query("INSERT into tombola values('".$row5['customers_name']."','".$row5['customers_email_address']."','".$row5['billing_state']."','".$row5['customers_telephone']."','".$row5['number_of_orders']." ')");  
	$now = mysql_query("SELECT * from tombola");
	while ($row3 = mysql_fetch_array($now)) {
        echo "Castigatorul este ".$row3["customers_name"]."(".$row3["customers_email_address"].")<br><br>Telefon: ".$row3["customers_telephone"]."<br>Oras: ".$row3["billing_state"]."  <br><br>".$row3["customers_name"]." a comandat ".$row3["number_of_orders"]." coduri!";
	}}
	}

        }}
        
?>

 

the if works ok... but when my table is zero i get an empty INSERT on all fields...

 

any idea where is the mistake?

[johnwayne77]

at last, i finished it.

 

works fine

 

 

this is the code

<?
    
    $result = mysql_query("SELECT customers_id
     , customers_name
 , customers_email_address
 , billing_state
 , customers_telephone
     , COUNT(orders_id) AS number_of_orders
  FROM orders
WHERE date_purchased >= '2008-12-01'
   AND date_purchased  < '2008-12-25'
GROUP BY customers_id
HAVING number_of_orders >= 5
ORDER
    BY RAND() LIMIT 1
");
while($row = mysql_fetch_array($result)) {
         
      
   $cn = $row["customers_name"];
   $ce = $row["customers_email_address"];
   $ct = $row["customers_telephone"];
   $co = $row["billing_state"];
   $cc = $row["number_of_orders"];

   }
   

   
	$query = mysql_query("SELECT * FROM tombola");
	$queryrows = mysql_num_rows($query);
	while($row2 = mysql_fetch_array($query)); {
	if ($queryrows > 0) {
	$past = mysql_query("SELECT * from tombola");
	while ($row4 = mysql_fetch_array($past)) {
	echo "Castigatorul tombolei de craciun a fost deja desemnat!<br>";

        echo "Castigatorul este ".$row4['customers_name']." (".$row4["customers_email_address"].")<br><br>Oras: ".$row4["customers_telephone"]."<br>Telefon: ".$row4["billing_state"]."  <br><br>".$row4["customers_name"]." a comandat ".$row4["number_of_orders"]." coduri!";
	//}
	}
	}else {

	mysql_query("INSERT into tombola values ('$cn','$ce','$ct','$co','$cc')");  
	$now = mysql_query("SELECT * from tombola");
	while ($row3 = mysql_fetch_array($now)) {
        echo "Castigatorul este ".$row3["customers_name"]." (".$row3["customers_email_address"].")<br><br>Oras: ".$row3["customers_telephone"]."<br>Telefon: ".$row3["billing_state"]."  <br><br>".$row3["customers_name"]." a comandat ".$row3["number_of_orders"]." coduri!";
	}}


        }
       
?>

 

what do you think?