date/time sensitive data?

[crazylegswilson]

I need help. I'm making a site that will 1) allow one group of users to act as admin and an other group to act as users. 2) allow the admin group to create forms that are stored in a database, for use by the user group. 3) allow the admin group to set days and times from which the forms are available and d/t on which the forms will no longer be available. I have pretty much everything set up, from login systems and the form creation system and a bunch of other bells and whistles, but I'm having trouble figuring out how to ever go about making my #3 possible. I guess I would need to have a relational key set between the table that holds the forms and another table that holds the time constraints, and after that, I don't know what to do. I see php has a nice date compare feature (mcal_date_compare()), and I've haphazardly found out how to get mktime() to spit out a stamp for me... but is there any way someone could give me a large push in the right direction from here? Am I making sense? Is there anything I could clarify? Can anyone help me?

[NokX]

i store all my date/time information in the form of a unix timestamp.

time()
Returns the current time measured in the number of seconds since the Unix Epoch (January 1 1970 00:00:00 GMT)

for example, today's date and time (November 24th, 2004 at 1:40PM EST) would be 1101321627. you'd store that value into an INT field in MySQL.

that way i can query the dates/times very easily, such as...

"SELECT * FROM tblInformation WHERE DateCreated > ".time()

that will get all records that are in the future. it also allows me to format my dates however i want...

date("m/d/yyyy", $row->DateCreated);

[crazylegswilson]

Cool! Thank you... so, next question then: assuming i figure out how to allow admin to select from/to available dates and relate that requirement to their forms, could you tell me how to go about making sure a date say, todays date, fits inside the given parameters? you know what i mean? like, the code checks to make sure today's date is > available_from and < avaialble_to? so the form only shows itself to the user between certain dates and times? the admin wants to be able to make forms only available from say, tomorrow at noon until tomorrow at one thirty. so i see i would make a form that allows that kind of input from the admin, which most likely converts it into the seconds from the unix epoch or whatever, then puts it in that format in the db (for the record, i'm doing this thing with PostgreSQL). then when the page that calls up forms for the user tries to call up forms, it blocks the ones that are not available based on today's time... again, am i amking sense?

[NokX]

so you want the user to type in a particular date to start from and go to...correct? for example...

I want records from 10/15/2004 until 11/15/2004.

$_POST["txtStartDate"] = "10/15/2004";
$_POST["txtEndDate"] = "11/15/2004";

and then based on that information, search the database? if that's the case, just do some reverse engineering using our friend mktime()...

mktime(hour, minute, second, month, day, year);

now, before we put the dates values into mktime(), we need to break down the date entered using another buddy, split().

$thestartdate = split("/", $_POST["txtStartDate"]);
$theenddate= split("/", $_POST["txtEndDate"]);

$thestartdate = mktime(0, 0, 0, $thestartdate[0], $thestartdate[1], $thestartdate[2]);
$theenddate = mktime(0, 0, 0, $theenddate[0], $theenddate[1], $theenddate[2]);

as you can see...you can narrow your search down even more by including hours, minutes, seconds into the mktime function.

now we'll take those values and put 'em into a sql query...

$query = "SELECT * FROM tblInformation WHERE DateCreated > ".$thestartdate." AND DateCreated < ".$theenddate;

you can also use BETWEEN in your statement...just whatever you want.

[crazylegswilson]

You're very helpful, thank you. No, I'm not looking to search the database... um, let me see here. well, sort of. ok. i want to search the database for entries which are set to be available during a given time. lets just say the admin wants to allow users to access certain forms only on tuesday, february 12th 2005 between 3 and 4 pm. so when creating the form, the admin selects the option to limit the use of the form by time, and then proceeds to select from feb 12 2005 3pm and select to feb 12 2005 4pm. when a user looks at thier menu on feb 11, they see nothing. on feb 12, between 3 and 4 pm, they see the form and can use it.

the only searching going on should be in the sql which will be some sort of join, no? I'm guessing that sql will be able to tell the differences between dates, so i think the statement would need to be something like "SELECT * FROM forms, date_constraints WHERE $todays_date_and_time > from_date || to_date" ... maybe that isnt the right sql. do you see my dilemma? again- does what i wrote make much sense?

[crazylegswilson]

Maybe the question I should be asking is: is there a php or sql statement that will compare or check a time/date stamp? if you have three values: today's date and time ($T), the date and time where access starts being allowed($START), and finally, the date and time where access stops ($STOP), how do i go about comparing time stamps like: if ($START < $T and $T< $STOP) {do stuff}else{dont do anything- exit};
anyone?

[crazylegswilson]

Anyone? I'd really love some help with this... If you're reading it and don't have time for a lenghty reply, could you at least give me a shove in the right direction to solving this on my own? I'm still pulling my hair out here... Anyone know a tutorial or something for a newb like me out there? is there anything i've posted on this thread here that doesn't make sense? Help?

[crazylegswilson]

ok... can someone at least tell me how i could create a unix timstamp from a form? like if i get the user to enter (maybe via select menus) a date, how can i convert that date into seconds since the epoch? I tried to figure out how to do it on 'paper' but with leap years and stuff, i'm all messed up. so if the user selects january 15th 2005 at 1430 (2:30pm)- what code do i need to convert that to that crazy 10 digit timestamp? I think knowing that will solve my problem. timestamps are progressive, so if i need to make sure 'now' is between 2 timestamps, simple less than/greater than stuff will work, wont it? Anyone? I have very little hair left to pull out here.

[crazylegswilson]

I seem to have solved my problem (fingers are crossed). My hair might even grow back. the important part is that if this code was easy to find, i might not have learned anything. so here, for the sanity of future unix timestamp newbies,
is the code that i needed to compare two timestamps.

First, the junk i used to put the code in the db:

<form method="post" action="form_time.php">
  <table width="468" border="0">
    <tr>
      <td width="421">Select the time <em><strong>from </strong></em>which you wish to make this form available: </td>
      <td width="14">&nbsp;</td>
      <td width="19">&nbsp;</td>
    </tr>
    <tr>
      <td><table width="200" border="0">
        <tr>
          <td>Year</td>
          <td>Month</td>
          <td>Day</td>
          <td>Hour</td>
          <td>Minute</td>
        </tr>
        <tr>
          <td><input name="year_from" type="text" id="year_from" value="2005" size="7" maxlength="4"></td>
          <td><select name="month_from" id="month_from">
              <option value="1" selected>January</option>
              <option value="2">February</option>
              <option value="3">March</option>
              <option value="4">April</option>
              <option value="5">May</option>
              <option value="6">June</option>
              <option value="7">July</option>
              <option value="8">August</option>
              <option value="9">September</option>
              <option value="10">October</option>
              <option value="11">November</option>
              <option value="12">December</option>
          </select></td>
          <td><select name="day_from" id="day_from">
              <option value="1" selected>1</option>
              <option value="2">2</option>
              <option value="3">3</option>
              <option value="4">4</option>
              <option value="5">5</option>
              <option value="6">6</option>
              <option value="7">7</option>
              <option value="8">8</option>
              <option value="9">9</option>
              <option value="10">10</option>
              <option value="11">11</option>
              <option value="12">12</option>
              <option value="13">13</option>
              <option value="14">14</option>
              <option value="15">15</option>
              <option value="16">16</option>
              <option value="17">17</option>
              <option value="18">18</option>
              <option value="19">19</option>
              <option value="20">20</option>
              <option value="21">21</option>
              <option value="22">22</option>
              <option value="23">23</option>
              <option value="24">24</option>
              <option value="25">25</option>
              <option value="26">26</option>
              <option value="27">27</option>
              <option value="28">28</option>
              <option value="29">29</option>
              <option value="30">30</option>
              <option value="31">31</option>
          </select></td>
          <td><select name="hour_from" id="hour_from">
              <option value="00" selected>12AM</option>
              <option value="01">1AM</option>
              <option value="02">2AM</option>
              <option value="03">3AM</option>
              <option value="04">4AM</option>
              <option value="05">5AM</option>
              <option value="06">6AM</option>
              <option value="07">7AM</option>
              <option value="08">8AM</option>
              <option value="09">9AM</option>
              <option value="10">10AM</option>
              <option value="11">11AM</option>
              <option value="12">12PM</option>
              <option value="13">1PM</option>
              <option value="14">2PM</option>
              <option value="15">3PM</option>
              <option value="16">4PM</option>
              <option value="17">5PM</option>
              <option value="18">6PM</option>
              <option value="19">7PM</option>
              <option value="20">8PM</option>
              <option value="21">9PM</option>
              <option value="22">10PM</option>
              <option value="23">11PM</option>
          </select></td>
          <td><select name="minute_from" id="minute_from">
            <option value="00" selected>00</option>
            <option value="5">05</option>
              <option value="10">10</option>
              <option value="15">15</option>
              <option value="20">20</option>
              <option value="25">25</option>
              <option value="30">30</option>
              <option value="35">35</option>
              <option value="40">40</option>
              <option value="45">45</option>
              <option value="50">50</option>
              <option value="55">55</option>
                              </select></td>
        </tr>
        <tr>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
        </tr>
      </table></td>
      <td></td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>Select the time to which you wish to make this form available: </td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><table width="200" border="0">
        <tr>
          <td>Year</td>
          <td>Month</td>
          <td>Day</td>
          <td>Hour</td>
          <td>Minute</td>
        </tr>
        <tr>
          <td><input name="year_to" type="text" id="year_to" value="2005" size="7" maxlength="4"></td>
          <td><select name="month_to" id="month_to">
              <option value="1" selected>January</option>
              <option value="2">February</option>
              <option value="3">March</option>
              <option value="4">April</option>
              <option value="5">May</option>
              <option value="6">June</option>
              <option value="7">July</option>
              <option value="8">August</option>
              <option value="9">September</option>
              <option value="10">October</option>
              <option value="11">November</option>
              <option value="12">December</option>
          </select></td>
          <td><select name="day_to" id="day_to">
              <option value="1" selected>1</option>
              <option value="2">2</option>
              <option value="3">3</option>
              <option value="4">4</option>
              <option value="5">5</option>
              <option value="6">6</option>
              <option value="7">7</option>
              <option value="8">8</option>
              <option value="9">9</option>
              <option value="10">10</option>
              <option value="11">11</option>
              <option value="12">12</option>
              <option value="13">13</option>
              <option value="14">14</option>
              <option value="15">15</option>
              <option value="16">16</option>
              <option value="17">17</option>
              <option value="18">18</option>
              <option value="19">19</option>
              <option value="20">20</option>
              <option value="21">21</option>
              <option value="22">22</option>
              <option value="23">23</option>
              <option value="24">24</option>
              <option value="25">25</option>
              <option value="26">26</option>
              <option value="27">27</option>
              <option value="28">28</option>
              <option value="29">29</option>
              <option value="30">30</option>
              <option value="31">31</option>
          </select></td>
          <td><select name="hour_to" id="hour_to">
              <option value="00" selected>12AM</option>
              <option value="01">1AM</option>
              <option value="02">2AM</option>
              <option value="03">3AM</option>
              <option value="04">4AM</option>
              <option value="05">5AM</option>
              <option value="06">6AM</option>
              <option value="07">7AM</option>
              <option value="08">8AM</option>
              <option value="09">9AM</option>
              <option value="10">10AM</option>
              <option value="11">11AM</option>
              <option value="12">12PM</option>
              <option value="13">1PM</option>
              <option value="14">2PM</option>
              <option value="15">3PM</option>
              <option value="16">4PM</option>
              <option value="17">5PM</option>
              <option value="18">6PM</option>
              <option value="19">7PM</option>
              <option value="20">8PM</option>
              <option value="21">9PM</option>
              <option value="22">10PM</option>
              <option value="23">11PM</option>
          </select></td>
          <td><select name="minute_to" id="minute_to">
            <option value="00" selected>00</option>
            <option value="05">05</option>
              <option value="10">10</option>
              <option value="15">15</option>
              <option value="20">20</option>
              <option value="25">25</option>
              <option value="30">30</option>
              <option value="35">35</option>
              <option value="40">40</option>
              <option value="45">45</option>
              <option value="50">50</option>
              <option value="55">55</option>
                              </select></td>
        </tr>
        <tr>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
        </tr>
      </table></td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><input type="submit" name="Submit" value="Submit"></td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
  </table>
</form>

in form_time.php there's the mktime() stuff:

<?php 
// the order here seems to be hour, minute, second, month, day, year... see your php mktime manual for details :)
    $from= mktime ($_POST[hour_from], $_POST[minute_from], 0, $_POST[month_from], $_POST[day_from], $_POST[year_from]); 
    $to = mktime ($_POST[hour_to],$_POST[minute_to],0,$_POST[month_to],$_POST[day_to],$_POST[year_to]);        
?>


<?php
$sql = "INSERT INTO the_table_that_holds_time_stuff (from, to) VALUES ('$from', '$to')";

// create connection; substitute your own information
$dbconn = pg_connect("host=place dbname=db user=dude password=sweet") or die("Couldn't make a connection");

// execute SQL query and get result
$sql_result = pg_query($dbconn,$sql) or die(pg_result_error());

?>

finally, to extract a list of only the forms that are currently available!
(my page that does this is called form_menu.php)

<?php
$now =  echo strtotime ("now"); 

$sql = "SELECT DISTINCT id, chapter_number, chapter_title FROM list_o_forms_table, the_table_that_holds_time_stuff WHERE from <= $now || $now <= to ORDER BY to DESC";
?>

now maybe I've left something important out, and maybe i've broken protocol by including too much code, but since no one has replied to my childlike wailing for assistance, i'm leaving this alone for a while. I'll come back and edit anything i find wrong with it later, and i do still want any and all input into what i think i just got done doing. I hope this junk proves helpful for someone else or at least gives an idea of how to approach a similar problem.