#1
  1. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    221

    A date question. Find prospects who joined within the past 7 days


    Hello;

    How can I get the prospects who joined the launch within the past 7 days?
    Also, do you recommend any other indexes?

    Code:
    CREATE TABLE `launch_launch_prospect` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `launch_id` int(11) NOT NULL,
      `prospect_id` int(11) NOT NULL,
      `date_added` date NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=43241 DEFAULT CHARSET=utf8;
    Thanks
  2. #2
  3. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,885
    Rep Power
    9646
    The date seven days ago is
    Code:
    NOW() - INTERVAL 7 DAY
    And any other indexes besides... what? The only one in there is for the primary key. If you want to search on a particular field (or more) very often then hint: it should get an index.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,794
    Rep Power
    4331
    the date seven days ago is
    Code:
    CURRENT_DATE - INTERVAL 7 DAY
    if you use NOW, which is equivalent to CURRENT_TIMESTAMP, you might miss some minutes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    221
    Edit

    pointless to do this in mysql. Can't match up mysql timezone with phps. Ending up using php date functions.

    PHP Code:
    public function how_many_days_propspect_been_in_launch($prospect_id$launch_id)
      {
        
    $CI =& get_instance();
        
    $sql "
        SELECT date_added
        FROM launch_launch_prospect WHERE prospect_id = ?
        AND launch_id = ?"
    ;
        
    $query $CI->db->query($sql, array($prospect_id$launch_id));
        if(
    $query->num_rows()!=1)
          {
            
    $_SESSION['error_message'] = "Invalid Request (Error Code 35. Prospect not found in launch!)";
            
    redirect(base_url('errors'),'refresh');
          }
        
    $date1 = new DateTime(date('Y-m-d'));
        
    $date2 = new DateTime($query->result_array()[0]['date_added']);
        return 
    $diff $date2->diff($date1)->format("%a");exit();
      } 

    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++

    This is actually a pretty cool feature.

    Check the gui => link

    So when the user has been in the system for 3 days, allow access to this page.

    That's the idea.

    Rudy, I know you're not php fanclub, but stay with me on this one pretty please.

    I add them to the system by setting the timezone:

    PHP Code:
    $this->custom_functions->set_timezone_by_user_id($_POST['user_id']);
    $data = array(
            
    'launch_id' => $_POST['launch_id'],
            
    'prospect_id' => $prospect_id,
            
    'time_added' => time(),
            
    'date_added' => date('Y-m-d'),
            
    'source' => (isset($_POST['source']) ? $_POST['source'] : '')
    ); 
    PHP Code:
    function set_timezone_by_user_id($user_id)
      {
        
    $CI = & get_instance();
        
    $sql "SELECT * FROM launch_owners WHERE id = ?";
        
    $query_timezone $CI->db->query($sql, array($user_id));
        
    date_default_timezone_set(''.$query_timezone->result_array()[0]['timezone'].'');
      } 

    And when I wanna retrieve how many days they've been in the system, I do:

    Check how many days in sequence:
    Code:
    SELECT Datediff(Curdate(), date_added) AS daysdiff
    FROM   launch_launch_prospect
    WHERE  prospect_id = 69
           AND launch_id = 14

    And here is when I do the math:
    PHP Code:
    public function can_prospect_see_plc($plc_id$prospect_email) {
        
    $CI = & get_instance();
        
    $CI - > custom_functions - > set_timezone_by_plc_id($plc_id);
        
    $CI - > plc_library - > is_valid_plc($plc_id);
        
    $launch_id $CI - > plc_library - > launch_id($plc_id);

        
    $prospects_id $CI - > prospects_library - > prospect_id_by_email($prospect_email);

        
    //Release PLC After
        
    $releases_this_plc_after_x_days $CI - > plc_library - > releases_this_plc_after_x_days($plc_id);
        
    //Days Been In Launch

        ############
        
    Check the date functions here
        $days_in_launch 
    $CI - > prospects_library - > how_many_days_propspect_been_in_launch($prospects_id$launch_id);
        
    //Should Redirect To Sales Page?
        
    $total_wait_before_sales_page $CI - > launch_library - > total_wait_before_sales_page($launch_id);
        
    $show_evergreen_sales_page_until_x_days_since_prospect_joined_the_launch $CI - > launch_library - > show_evergreen_sales_page_until_x_days($launch_id);


        if (
    $days_in_launch $total_wait_before_sales_page) {
            if (
    $show_evergreen_sales_page_until_x_days_since_prospect_joined_the_launch >= $days_in_launch) {
                if (
    $CI - > plc_library - > redirect_to_sales_page_if_sales_page_open($plc_id)) {
                    
    $redirect base_url("/eg-redirect/".$launch_id.
                        
    "/".$prospect_email);
                    
    redirect($redirect);
                    return 
    1;
                } else {
                    
    //Show PLC
                    
    redirect($CI - > plc_library - > plc_url($plc_id));
                    return 
    2;
                }
            } else {
                
    //Redirect To Offer Expired
                
    redirect($CI - > launch_library - > offer_closed_page($launch_id));
                return 
    3;
            }
        }
        if (
    $days_in_launch >= $releases_this_plc_after_x_days) {
            
    //Show PLC
            
    redirect($CI - > plc_library - > plc_url($plc_id));
            return 
    2;
        }
        if (
    $days_in_launch $releases_this_plc_after_x_days) {
            
    //PLC not Opened Yet
            
    redirect($CI - > plc_library - > redirect_url_if_before_access_date($plc_id));
            return 
    4;
        }


    I removed all php date calculations and really the only thing I am doing manually is simple <= => = math.


    And finally, I set page access dates using date('c')
    PHP Code:
    $data = array( 
                    
    'broadcast_access_date' => date('c',strtotime($_POST['broadcast_access_date']));
                    
    $this->db->where('id'$id); 
                    
    $this->db->update('launch_plcs'$data); 

    Is the best code? It's best I can do.

    Does it work? Yes for the price of makig mods cringe

    Do I wish I knew unitTesting? Phuckin oath

    Is anyone gonna reply to this? Not sure
    Last edited by English Breakfast Tea; May 5th, 2018 at 10:43 PM.
  8. #5
  9. Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,677
    Rep Power
    1841
    If you stored dates in mySQL with all the same TZ then a simple bit of SQL would give you anyone who joined, etc., within last 7 days as 7 days is 7 days, whatever the (same) TZ is.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc

IMN logo majestic logo threadwatch logo seochat tools logo