mysql logic

**johnwayne77** · Dec 24, 2008 11:25

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:

Code:

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?

**r937** · Dec 24, 2008 11:32

Code:

SELECT customers_id
     , customers_name
     , COUNT(orders_id) AS number_of_orders
  FROM daTable
 WHERE date_purchased >= '2008-11-24'
   AND date_purchased  < '2008-12-25'
GROUP
    BY customers_id
HAVING number_of_orders >= 5
ORDER
    BY RAND() LIMIT 1

**johnwayne77** · Dec 24, 2008 12:36

thanks!

it worked

**johnwayne77** · Dec 24, 2008 13:31

r937, could u check your PM or mail?

i couldnt post my reply here due to sitepoint terms

**johnwayne77** · Dec 24, 2008 14:20

this is what i figure out by now:

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
");
   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

**johnwayne77** · Dec 24, 2008 16:00

i got to this version:

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,
    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** · Dec 24, 2008 16:54

at last, i finished it.

works fine

this is the code

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?

User Tag List

Thread: mysql logic

Thread Tools

Display

mysql logic

Bookmarks

Bookmarks

Posting Permissions