#1
  1. A Change of Season
    Devshed Loyal (3000 - 3499 posts)

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

    How can I find prospects who joined within 2 and 25 days ago


    Going crazy on this oeiiii;

    Time_added is stored as
    PHP Code:
    time(); 
    How can I find prospects who joined within 2 and 25 days ago.. or any date range basically.

    Code:
    CREATE TABLE `launch_launch_prospect` (
      `id` int(11) NOT NULL,
      `launch_id` int(11) NOT NULL,
      `prospect_id` int(11) NOT NULL,
      `time_added` int(11) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=u

    I thought
    PHP Code:
    $sql_prospects "SELECT *, 
           launch_launch_prospect.prospect_id AS id, 
           time_added, 
           launch_launch_prospect.prospect_id AS PID, 
           launch_prospects.email 
    FROM   launch_launch_prospect 
           INNER JOIN launch_launches 
                   ON launch_launch_prospect.launch_id = launch_launches.id 
           INNER JOIN launch_prospects 
                   ON launch_launch_prospect.prospect_id = launch_prospects.id 
    WHERE  launch_launch_prospect.launch_id = "
    .$launch_id.
           AND time_added >= "
    $from.
           AND time_added <= "
    .$to .
    ORDER  BY time_added ASC"

    But doesn't seem to be correct!

    Output on screen

    Show those who have been in the system between 2 And 26 days
    From: 1522897755
    To: 1520824155

    SELECT *, launch_launch_prospect.prospect_id AS id, time_added, launch_launch_prospect.prospect_id AS PID, launch_prospects.email FROM launch_launch_prospect INNER JOIN launch_launches ON launch_launch_prospect.launch_id = launch_launches.id INNER JOIN launch_prospects ON launch_launch_prospect.prospect_id = launch_prospects.id WHERE launch_launch_prospect.launch_id = 10 AND time_added >= 1522897755 AND time_added <= 1520824155 ORDER BY time_added ASC
    Last edited by English Breakfast Tea; April 6th, 2018 at 10:14 PM.
  2. #2
  3. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,749
    Rep Power
    9646
    Look at it.
    Code:
    time_added >= 1522897755 AND time_added <= 1520824155
    Look at it. Now tell me exactly what it is doing.
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2017
    Location
    Lithuania
    Posts
    48
    Rep Power
    46
    I know this is not going to help you much (but requinix provided you with a hint anyways, so you know where to look at), but if there's way for you to store data/time in standard MySQL format yyyy-mm-dd, I would suggest you doing so. I also used time() years ago and usually it was pain in the a**, especially when viewing database directly and having no idea what actual time a records like 1522897755 represents. So I invested some time to make a transition to yyyy-mm-dd and my life became much, much easier.

    I can now exactly tell what time each record was created/modified by viewing database, no need to copy numerical value and convert it into an actual time every time. Writing queries like yours is also much easier because I can use "normal" format now.
    Do you license and update your PHP scripts?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2016
    Location
    Cheshire, UK
    Posts
    85
    Rep Power
    72
    I agree with phpmillion. Not ony is DATE or DATETIME format easily readable but
    1 ) It is usable by all the mysql datetime functions without converting to DATETIME format first
    2 ) It has much greater value range (1901-01-01 to 9999-12-31) than time() which is limited to the 68 year unix epoch.
  8. #5
  9. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,929
    Rep Power
    4554
    If it was MYSQL DateTime, couldn't you then do something like

    WHERE time_added BETWEEEN (NOW() - INTERVAL 2 DAYS) AND (NOW() - INTERVAL 25 DAYS)

    Been years since I've played in MySQL, so no idea if that's valid it not, but I remember something like that.
    -- Cigars, whiskey and wild, wild women. --
  10. #6
  11. A Change of Season
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,474
    Rep Power
    221
    Originally Posted by requinix
    Look at it.
    Code:
    time_added >= 1522897755 AND time_added <= 1520824155
    Look at it. Now tell me exactly what it is doing.
    This condition is impossible. It's like looking for a number that is greater than 3 but smaller than 2. I blame the Corona next to me.

    I changed it and it seems to work fine now. Except for 1 thing.

    This is basically what it does.

    It finds the number of prospects at each stage of a product launch. you can see them in the white boxes saying "Currently here".

    So basically I work with 3 tables: launch_plcs, launch_launches, and launch_launch_prospect.

    The small thing now is that I see some prospects in both steps. For example in PLC2 and PLC3.

    I have a feeling I should round time_added (time prospect added to the launch_launch_prospect)

    Any tips to debug?

    If you need more info please let me know.

    Code:
    CREATE TABLE `launch_launch_prospect` (
      `id` int(11) NOT NULL,
      `launch_id` int(11) NOT NULL,
      `prospect_id` int(11) NOT NULL,
      `time_added` int(11) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    Code:
    CREATE TABLE `launch_plcs` (
      `id` int(11) NOT NULL,
      `launch_id` int(11) NOT NULL,
      `user_id` int(11) NOT NULL,
      `release_after_days_evergreen` int(11) NOT NULL DEFAULT '1',
      `page_title_for_browser` varchar(255) NOT NULL,
      `title` varchar(255) NOT NULL,
      `youtube_video` varchar(255) NOT NULL,
      `broadcast_status` int(11) DEFAULT NULL,
      `broadcast_access_date` date NOT NULL,
      `evergreen_status` int(11) DEFAULT '1',
      `sub_headline` varchar(255) NOT NULL,
      `main_headline` varchar(255) NOT NULL,
      `magic_content` varchar(255) NOT NULL,
      `magic_content_2` varchar(255) NOT NULL DEFAULT 'no',
      `magic_content_3` text NOT NULL,
      `magic_timer` varchar(255) NOT NULL DEFAULT 'no',
      `show_magic_content_after_seconds` int(11) NOT NULL,
      `video_frame_bg` varchar(255) NOT NULL,
      `show_launch_nav` int(11) NOT NULL DEFAULT '0',
      `show_offer_open_timer` varchar(255) NOT NULL DEFAULT 'yes'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    Code:
    CREATE TABLE `launch_launches` (
      `id` int(11) NOT NULL,
      `launch_status` int(11) NOT NULL DEFAULT '1',
      `title` varchar(255) NOT NULL,
      `launch_type` varchar(255) NOT NULL,
      `user_id` int(11) NOT NULL,
      `product_id` int(11) NOT NULL,
      `open_offer_x_days_after_last_plc` int(11) NOT NULL DEFAULT '6',
      `offer_open_date` date NOT NULL,
      `sales_page` varchar(500) NOT NULL,
      `offer_closed_page` varchar(255) NOT NULL,
      `how_many_days_keep_sales_page_open` int(11) NOT NULL DEFAULT '4'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    PHP Code:
    public function currently_in_plc($plc_id)
            {
                
    $this->validation_library->does_this_plc_belong_to_this_login($plc_id);
                
    $sql "
    SELECT launch_plcs.id, 
           launch_plcs.launch_id, 
           launch_plcs.user_id, 
           launch_plcs.release_after_days_evergreen, 
           launch_plcs.page_title_for_browser, 
           launch_plcs.title, 
           launch_plcs.youtube_video, 
           launch_plcs.broadcast_status, 
           launch_plcs.broadcast_access_date, 
           launch_plcs.main_headline 
    FROM   launch_plcs 
           INNER JOIN launch_launches 
                   ON launch_launches.id = launch_plcs.launch_id 
    WHERE  launch_plcs.id = ? 
                    "
    ;
                
    $query $this->db->query($sql, array($plc_id));
                
    $plcs $query->result_array();
                
    $this_plc_release_after $plcs[0]['release_after_days_evergreen'];
                
    $this->data['launch_id'] = $plcs[0]['launch_id'];
            
            
    ##########
            ##########
            ##########
            ##########
            //Total wait of previous plcs
            
    $sql "
    SELECT launch_id, 
           Sum(launch_plcs.release_after_days_evergreen) AS SUM 
    FROM   launch_plcs 
    WHERE  launch_plcs.launch_id = ? 
           AND id < ? 
                    "
    ;
            
    $query $this->db->query($sql, array($this->data['launch_id'] , $plc_id));
            
    $previous_wait 0;
            
    $plcs $query->result_array();
            
    $total 0;
            if(
    $query->num_rows()>0)
                {
                    
    $previous_wait $query->result_array()[0]['SUM'];
                }

            
    //Next:
                
    $sql "
    SELECT launch_plcs.release_after_days_evergreen 
    FROM   launch_plcs 
    WHERE  launch_plcs.launch_id = ? 
           AND id > ? 
    ORDER  BY id ASC 
    LIMIT  1 "
    ;
            
            
    $query_next $this->db->query($sql, array($this->data['launch_id'] , $plc_id));
            
    $next_wait 0;
            if(
    $query_next->num_rows()>0)
                {
                    
    $next_wait $query_next->result_array()[0]['release_after_days_evergreen'];
                }
            
            
    //If this is the last PLC, retrieve "open_offer_x_days_after_last_plc" instead of "release_after_days_evergreen"
            
    if($query_next->num_rows()==&& $this->last_plc($this->data['launch_id'])==$plc_id)
                {
                    
    $sql "
    SELECT open_offer_x_days_after_last_plc 
    FROM   launch_launches 
    WHERE  id = ? "
    ;
                    
    $query_last $this->db->query($sql, array($this->data['launch_id'] ));
                    
    $next_wait $query_last->result_array()[0]['open_offer_x_days_after_last_plc'];    
                }
            

            
    $next $this_plc_release_after+$previous_wait+$next_wait;
            
    // echo "Release ";
            // echo $this_plc_release_after;
            // echo " Days After Last Step";
            // echo "<br />";
            // echo "Previous PLCs Total Wait:". $previous_wait;
            // echo "<br />";

            
    $from $this_plc_release_after+$previous_wait;
            
    $to $next;
             echo 
    " Show those who have been in the system between ".($from);
             echo 
    " And ".($next)." days";
            
    // echo "<br />";
            // echo "From: ".strtotime("-".$from." days");
            // echo "<br />";
            // echo "To: ".strtotime("-".$next." days");
            // echo "<br />";echo "<br />";
            
    $from strtotime("-".$from." days");
            
    $to strtotime("-".$next." days");
            
    $this->launch_prospects_between_dates($to$from$this->data['launch_id']);


            

            
    ##########
            ##########
            ##########
            
               
    }

        public function 
    last_plc($launch_id)
            {
                
    $sql "SELECT launch_plcs.id
                        FROM launch_plcs WHERE launch_id = ?
                        ORDER BY id DESC LIMIT 1"
    ;

                
    $query $this->db->query($sql, array($launch_id));
                
                return 
    $query->result_array()[0]['id'];
            }    
        public function 
    launch_prospects_between_dates($to$from$launch_id)
            {
                
    $this->data['launch_prospects']=array();
        echo 
    $sql_prospects "SELECT *, 
           launch_launch_prospect.prospect_id AS id, 
           time_added, 
           launch_launch_prospect.prospect_id AS PID, 
           launch_prospects.email 
    FROM   launch_launch_prospect 
           INNER JOIN launch_launches 
                   ON launch_launch_prospect.launch_id = launch_launches.id 
           INNER JOIN launch_prospects 
                   ON launch_launch_prospect.prospect_id = launch_prospects.id 
    WHERE  launch_launch_prospect.launch_id = "
    .$launch_id.
           AND time_added > "
    .$to.
           AND time_added <= "
    .$from .
    ORDER  BY time_added ASC"

                 
    $query_p $this->db->query($sql_prospects);
                 foreach(
    $query_p->result_array() as $p)
                   {
                    
    $in_the_system time()-$p['time_added'];
                    
    $in_the_system round($in_the_system / (60 60 24));
                     
    $this->data['launch_prospects'][]=array('in_the_system'=> $in_the_system'PID'=>$p['PID'],'email'=>$p['email'], 'id'=>$p['id'], 'time_added'=>$p['time_added']);
                   }
                 
                
    $this->data['prospects'] = $this->data['launch_prospects'];
                
    $this->load->view('header',$this->data);
                
    $this->load->view('prospects_view',$this->data);
                
    $this->load->view('footer_view',$this->data);    
            } 



    I changed the query to this, but still see some prospects in 2 steps:
    PHP Code:
    public function launch_prospects_between_dates($to$from$launch_id)
            {
                
    $this->data['launch_prospects']=array();
        
    $sql_prospects "SELECT *, 
           launch_launch_prospect.prospect_id AS id, 
           time_added, 
           launch_launch_prospect.prospect_id AS PID, 
           launch_prospects.email 
    FROM   launch_launch_prospect 
           INNER JOIN launch_launches 
                   ON launch_launch_prospect.launch_id = launch_launches.id 
           INNER JOIN launch_prospects 
                   ON launch_launch_prospect.prospect_id = launch_prospects.id 
    WHERE  launch_launch_prospect.launch_id = "
    .$launch_id.
           AND (time_added BETWEEN "
    .$to." AND ".$from ." )
    ORDER  BY time_added ASC"

                 
    $query_p $this->db->query($sql_prospects);
                 foreach(
    $query_p->result_array() as $p)
                   {
                    
    $in_the_system time()-$p['time_added'];
                    
    $in_the_system round($in_the_system / (60 60 24));
                     
    $this->data['launch_prospects'][]=array('in_the_system'=> $in_the_system'PID'=>$p['PID'],'email'=>$p['email'], 'id'=>$p['id'], 'time_added'=>$p['time_added']);
                   }
                 
                
    $this->data['prospects'] = $this->data['launch_prospects'];
                
    $this->load->view('header',$this->data);
                
    $this->load->view('prospects_view',$this->data);
                
    $this->load->view('footer_view',$this->data);    
            } 
    Last edited by English Breakfast Tea; April 7th, 2018 at 10:05 AM.
  12. #7
  13. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,749
    Rep Power
    9646
    It's getting a bit hard to follow.

    If you see the same data in multiple places then that means the queries overlap in some areas. Look at the actual data for one of them to see why it's being included in more than one query's results, then decide from there what you need to change so that doesn't happen.
  14. #8
  15. A Change of Season
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,474
    Rep Power
    221
    Got it working. This one was a total biatch!

    It looks like
    Code:
    AND date_added > '".$from."' AND date_added <= '".$to."'
    Is more practical and easier to use than time() for what I wanted to do.

    It got hard since I wanna get the list of prospects joined between X and Y days and X and Y are dynamic per PLC.

    I also had to reverse TO and FROM to get the dates right.

    Anyways this is the sql looks good
    PHP Code:
    $sql_prospects "SELECT *, 
           launch_launch_prospect.prospect_id AS id, 
           time_added, 
           launch_launch_prospect.prospect_id AS PID, 
           launch_prospects.email 
    FROM   launch_launch_prospect 
           INNER JOIN launch_launches 
                   ON launch_launch_prospect.launch_id = launch_launches.id 
           INNER JOIN launch_prospects 
                   ON launch_launch_prospect.prospect_id = launch_prospects.id 
    WHERE  launch_launch_prospect.launch_id = "
    .$launch_id.
           AND date_added > '"
    .$from."' AND date_added <= '".$to."'
    ORDER  BY date_added ASC"

    This project is a bit tough since I have to work with lots of dynamic dates and ranges.

    I'll post more soon :0

    Thanks all
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2016
    Location
    Cheshire, UK
    Posts
    85
    Rep Power
    72
    You need to learn the use of mysql datetime functions
    Code:
    WHERE DATE(FROM_UNIXTIME(time_added)) BETWEEN CURDATE() - INTERVAL 25 DAY AND CURDATE() - INTERVAL 2 DAY
  18. #10
  19. A Change of Season
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,474
    Rep Power
    221
    Originally Posted by Barand
    You need to learn the use of mysql datetime functions
    Code:
    WHERE DATE(FROM_UNIXTIME(time_added)) BETWEEN CURDATE() - INTERVAL 25 DAY AND CURDATE() - INTERVAL 2 DAY
    I haven't tested it yet but looks like they should give the same results:

    PHP Code:
    public function how_many_days_in_launch_using_php($prospect_id$launch_id)
      {
          
    $CI =& get_instance();
          
    $sql "SELECT date_added
                  FROM   launch_launch_prospect
                  WHERE  launch_launch_prospect.prospect_id = ?
                  AND launch_launch_prospect.launch_id = ?"
    ;
          
    $query $CI->db->query($sql, array($prospect_id$launch_id));
          return 
    floor((time() - $query->result_array()[0]['date_added']) / (60 60 24));
      } 
    PHP Code:
    public function how_many_days_in_launch_using_mysql($prospect_id$launch_id)
      {
        
    $CI =& get_instance();
        
    $sql "
        SELECT DATEDIFF(CURDATE(), date_added) as daysdiff
        FROM launch_launch_prospect WHERE prospect_id = ?
        AND launch_id = ?"
    ;
        
    $query $CI->db->query($sql, array($prospect_id$launch_id));
        return 
    $query->result_array()[0]['daysdiff'];
      } 

    If that's the case, then your solution is better.

IMN logo majestic logo threadwatch logo seochat tools logo