SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: mysql logic

  1. #1
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql logic

    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks!

    it worked

  4. #4
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, could u check your PM or mail?

    i couldnt post my reply here due to sitepoint terms

  5. #5
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  7. #7
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •