#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2017
    Posts
    7
    Rep Power
    0

    Date, Time in separate field (compare)


    Hello all,
    First time in a long time I am asking for a bit of help.

    I am trying to pull date, time and AM/PM in a database that are separated.

    Code:
    start_date=(m/d/Y) start_time=(7:00) start_day=(AM)
    Then

    Code:
    app_date=(m/d/Y) app_time=(8:00) app_day=(AM)
    Hopefully the above makes sense.

    Every 30 minutes a cron runs: I want to sent a text (no worries there) between those dates and times.

    Here is my mysql code:

    Code:
    $DATE1 = date("m/d/Y");
    $HOUR1 = date("g:i");
    $DAY1 = date("A");
    
    $get_cart1a = "select * from to_do WHERE alert_date='$DATE1' && 
    alert_time='$HOUR1' && alert_day='$DAY1' && app_date='$DATE1' && 
    app_time='$HOUR1' && app_day='$DAY1'";
    $get_cart1a_res = mysql_query($get_cart1a) or die(mysql_error());
    Of course the while loop (self explanatory)

    Then I try to compare with an if statement:

    Code:
    $start   = strtotime("$alert_day $alert_time $alert_day"); 
    $end     = strtotime("$app_date $app_time $app_day"); 
    $compare = strtotime(date("m/d/Y g:i A"));
    Code:
    if($compare >= $start && $compare <= $end) {
    //do if it compares
    }
    I have searched and tried a bunch of combinations, but no go, been a month!!

    This does not return anything, but it should!!

    Written in PHP and MySql
  2. #2
  3. Maddening Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,453
    Rep Power
    9645
    First step should be to not separate the date and time. There's no need, and in fact it makes more work for you. Put them together into one DATETIME field each. MySQL will store them as YYYY-MM-DD HH:MM:SS which is not only fine but a sort of best practice for dates.

    Second step is the query: if you can make it find all the appropriate results for you then you don't need that loop with the condition (but you do, of course, still need the loop). Now that the entire date/time is in one field you can do the query very easily:
    PHP Code:
    $now date("Y-m-d H:i:s");
    $get_cart1a "SELECT * FROM to_do WHERE '{$now}' BETWEEN alert_date AND app_date"
    (You could put the date into the query too using NOW() but you'd then have to ensure your server and your database are using the same timezone, and you might not have control over that.)

    Third step is there is no third step. Just send the notifications for each result.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2017
    Posts
    7
    Rep Power
    0
    Originally Posted by requinix
    First step should be to not separate the date and time. There's no need, and in fact it makes more work for you. Put them together into one DATETIME field each. MySQL will store them as YYYY-MM-DD HH:MM:SS which is not only fine but a sort of best practice for dates.

    Second step is the query: if you can make it find all the appropriate results for you then you don't need that loop with the condition (but you do, of course, still need the loop). Now that the entire date/time is in one field you can do the query very easily:
    PHP Code:
    $now date("Y-m-d H:i:s");
    $get_cart1a "SELECT * FROM to_do WHERE '{$now}' BETWEEN alert_date AND app_date"
    (You could put the date into the query too using NOW() but you'd then have to ensure your server and your database are using the same timezone, and you might not have control over that.)

    Third step is there is no third step. Just send the notifications for each result.
    Thank you Requinix!!!
    I thought that was the case for the format of the database....but I am trying to put all the fields together in the database and it is not working to well. All I get is all zeros.

    Here is my code:
    PHP Code:
    $AL_DATE htmlentities(date('Y-m-d'strtotime($_POST['alert_date'])));
    $AL_TIME htmlentities(date('H:i:s'strtotime($_POST['alert_time'].$_POST['alert_day'])));
    $ALERT $AL_DATE $AL_TIME;

    $AP_DATE htmlentities(date('Y-m-d'strtotime($_POST['app_date'])));
    $AP_TIME htmlentities(date('H:i:s'strtotime($_POST['app_time'].$_POST['app_day'])));
    $APPOINT $AP_DATE $AP_TIME;

    $ALERT1 strtotime($ALERT);
    $APP1 strtotime($APPOINT); 
    Above I tried every combination, with htmlentities and without, etc.

    PHP Code:
    $sq "INSERT INTO to_do VALUES('', '$ALERT1', '$APP1')"
    Just confused. Read a few places that contradict each other! LOL

    The alert_day and app_day are AM/PM

    Thanks!
  6. #4
  7. Maddening Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,453
    Rep Power
    9645
    1. What is the structure of the to_do table? "SHOW CREATE to_do" would provide one way to answer that.
    2. Are you sure you want to insert Unix timestamps and not date strings?
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2017
    Posts
    7
    Rep Power
    0
    Here is the database table structure

    Code:
    CREATE TABLE IF NOT EXISTS `to_do` (
      `tt_id` bigint(20) NOT NULL,
      `alert_send` varchar(255) NOT NULL,
      `alert_date` datetime NOT NULL,
      `app_date` datetime NOT NULL,
      `archive` varchar(255) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    I am not sure what I need to store in the database for the cron to run correctly. Which the code that you provided would work if the syntax is right when I add it. But there are 3 fields (date, time and am/pm) I am trying to combine to one datetime field in the database.

    I hope I explained that right.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,362
    Rep Power
    630
    Storing the date/time in the database should be treated separately from formatting cron. In your code to create the cron job, format the date from the database for cron.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2017
    Posts
    7
    Rep Power
    0
    Originally Posted by gw1500se
    Storing the date/time in the database should be treated separately from formatting cron. In your code to create the cron job, format the date from the database for cron.
    I am doing that. This code is not on the cron file...that I can handle, just need to figure out how to add 3 HTML fields together to a datetime field in mysql.

    IE:
    $_POST['app_date'] //this is the date itself YYYY-MM-DD
    $_POST['app_time'] //this is the time 7:00
    $_POST['app_day'] //this is the AM/PM
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,362
    Rep Power
    630
    First you don't need to input the day. 'app_date' will give you that. You can either eliminate that in the form or just ignore it. MySQL accepts a very loose datetime format. Simply do this:
    PHP Code:
    s=$_POST['app_date']." ".$_POST['app_time']; 
    Use that string in your query. Of course you need to validate the format from the form and use prepared statements but it is otherwise trivial.
    Last edited by gw1500se; November 25th, 2017 at 08:49 AM.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2017
    Posts
    7
    Rep Power
    0
    Thank you for all of your help!!! It is working the way it should.

    If anyone is interested this is what I had to do with the $_POST's
    PHP Code:
    //START DATE
    $S_DATE htmlentities($_POST['alert_date']);
    $S_DATE1 date("Y-m-d"strtotime($S_DATE));
    $S_TIME htmlentities($_POST['alert_time'].$_POST['alert_day']);
    $S_TIME1 date("H:i:s"strtotime($S_TIME));
    $S_DATE2 $S_DATE1." ".$S_TIME1;

    //APPOINTMENT DATES
    $A_DATE htmlentities($_POST['app_date']);
    $A_DATE1 date("Y-m-d"strtotime($A_DATE));
    $A_TIME htmlentities($_POST['app_time'].$_POST['app_day']);
    $A_TIME1 date("H:i:s"strtotime($A_TIME));
    $A_DATE2 $A_DATE1." ".$A_TIME1
    It may be the long way around but it works great!!!!

    THANK YOU!!!!
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2017
    Posts
    7
    Rep Power
    0
    OOP forgot the INSERT

    Code:
    $sq = "INSERT INTO to_do VALUES('', '$DATE_P', '$DATE_P', '$SUB', '$USER_ID', '$USERNAME', '$DESCRIPT', 'NO', '$S_DATE2', '$A_DATE2', 'NO')";
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,362
    Rep Power
    630
    Yep, the long way around and the wrong way to store it. You should not be satisfied with poor programming practices just because it "works."
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2017
    Posts
    7
    Rep Power
    0

    Thumbs down


    Originally Posted by gw1500se
    Yep, the long way around and the wrong way to store it. You should not be satisfied with poor programming practices just because it "works."
    Originally Posted by gw1500se
    s=$_POST['app_date']." ".$_POST['app_time'];
    The above did not work I had to have them added all together to get the 24 time.

    I posted what worked with 3 different HTML fields put together so it would be inserted into a datetime field in (and maybe help someone else). Oh yeah it would be simple to do a now() but how can you do that if you are doing an alert to be texted??

    This is why I did not ask for help on a public forum for over 15 years of coding. I figured it out myself. I do not code full time, don't want to, been in construction for 35 years. I have been building an office site for myself that tracts projects, profit and loss, materials, sub-contractors, employees, invoicing, proposals......more and more.

    So, since you are so perfect, what would you have done then???? Love to see it. But the above did not work because you need the AM or PM for MySQL to tell if it was 7 in the morning or 7 in the evening.

    Will not be back on here....ever
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,362
    Rep Power
    630
    I said it was untested. What was the error or what did
    PHP Code:
    echo s
    output that was wrong?
    There are 10 kinds of people in the world. Those that understand binary and those that don't.

IMN logo majestic logo threadwatch logo seochat tools logo