Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2007
    Posts
    27

    Exclamation Unanswered: Display date/time field from MS Access

    Okay i'm trying to display the date from a record within ms access. The date is supposed to read 1/12/2008 but instead it displays as 1200117600.

    Here is my code

    Code:
    <html><title>Human Resources Employee Wellness Program</title><center><h3>Display Progress</h3></center>
    <?php
    require("displayconfig.php");
    $sql = "SELECT * FROM Activity_Log WHERE Employee_ID = '{$_POST["Employee_ID"]}'  ORDER BY FORMAT(Date, 'mm')"; 
    $rs = $conn->Execute($sql);
    ?>
    <table border="1" cellpadding="5" cellspacing="5" align="center" width="50%">
    <tr>
    	<th>Employee ID</th>
    	<th>Date</th>
    	<th>Miles</th>
    	<th>Activity</th>
    	<th>Minutes</th>
    </tr>
    <?php while (!$rs->EOF) { ?>
    	<tr>
    		<td><?php echo $rs->Fields['Employee_ID']->Value ?></td>
    		<td><?php echo $rs->Fields['Date']->Value ?></td>
    		<td><?php echo $rs->Fields['Miles']->Value ?></td>
    		<td><?php echo $rs->Fields['Activity']->Value ?></td>
    		<td><?php echo $rs->Fields['Minutes']->Value ?><br></td>
    	</tr>
    	<?php $rs->MoveNext() ?>
    <?php } ?>
    </table>
    <?php
    $rs->Close();
    $conn->Close();
    $rs = null;
    $conn = null;
    ?>
    </html>
    here is my config file
    Code:
    <?php
    $conn = new COM ("ADODB.Connection") or die("Cannot start ADO");
    $connStr = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=F:\Wellness Program\Copy of HR Employee Wellness Program CLB.mdb;";
    $conn->open($connStr); //Open the connection to the database
    ?>
    Does anybody know how I can fix this?

    Thanks in advance!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1200117600 is a unix epoch timestamp

    ditch the dreaded, evil "select star" and list the columns that you want to return

    then add this: dateadd("s",1200117600,'1970-01-01 00:00')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2007
    Posts
    27
    The only problem is that there is many different dates that need to be displayed from the database... ?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so change it to a function, and call that function each time you want to use the value....

    ..its one of the problems of using a JET database in PHP.

    the alternative is slightly klunky.. change the date in the query to a string representation (using the VBA format fucntion), and then change that text representation back into a PHP date variable using the appropriate function... mktime rings a bell.

  5. #5
    Join Date
    Oct 2007
    Posts
    27
    Well i think i get what you're saying. But i'm not sure how to go about making it into a function.? and I'm using PHP Version 4.3.2, by the way if that matters.

    i'm still kind of new to php so...

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look in the books / manual / php.net for how to declare a function (ie how to pass the parameters in, how to return a value)

    looking at this problem again I'd be minded to use the klunky workaround at first attempt.

    Code:
    function ConvertJETDate(strDate)
    //this function takes a string date and converts it to a unix datestamp
    //it isn't a fully finished function, theres no error checking, its only set up for true dates: format dd/mm/yyyy, as dates were intended to be used
    //it makes no assumptions , or choice of default values
    //optionally you can supply the time in the format  dd/mm/yyyy @ hh/MM/ss
    { $Day = substr(strDate,0,2);
      $Month = substr(strDate,3,2);
      $Year = (strDate,6,4);
      if (strlen(strdate)>9) //then we have additional characters which should be the time
      { $Hour = (strDate,13,2);
         $Minute = (strDate,16,2);
         $Second =(strDate,19,2);
      } else
      { $Hour = 0;
         $Minute = 0;
         $Second =0;
      } 
      UnixDateStamp = mktime($Hour,$Minute,$Second,$Month,$Day,$Year)
      return (UnixDateStamp);
    }
    this function is written on the fly, it hasn't been tested, it hasn't been proved.. but it should work

    its a php function so you would need to call it when you assign the value form the associative array to a working variable in PHP

    eg
    $mydatevalue = ConvertJETDate($Row['nameofdatecolumninJETTable']);

    another, and almost certainly better approach would be to find out what mechanism JET/Access uses to store dates and what mechanism PHP does and do a simple mathmatical conversion adding or in this case subtracting the difference

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem
    ... find out what mechanism JET/Access uses to store dates and what mechanism PHP does and do a simple mathmatical conversion adding or in this case subtracting the difference
    see post #2 in this thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ..fair enough

    ..shows my ignorance of the detail, but then again looking at magic numbers has never been my forte

  9. #9
    Join Date
    Oct 2007
    Posts
    27

    Question

    Okay so it should read like this then?
    Code:
    $sql = "SELECT Employee_ID, Miles, Activity, Minutes, Date FROM Activity_Log WHERE Employee_ID = '{$_POST["Employee_ID"]}'  ORDER BY FORMAT(Date, 'mm') dateadd("s",1200117600,'1970-01-01 00:00')";
    or how else should it read?

    the only other question i have is... there are many other dates, so will it work like that for all of the other dates?

    *edit*

    well i just tested that and it gave me this

    Code:
    Parse error: parse error, unexpected T_STRING in F:\InetPub\wwwroot\WellnessProgram\DisplayProgress.php on line 4
    and that line of code is line 4. What am i doing wrong?
    Last edited by mstng07; 01-18-08 at 12:19.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in future, could you please post your query with some human-oriented indenting? i get lost when scrolling sideways

    the DATEADD trick i showed you was to convert the unix values to datetime values

    you would use it in the SELECT clause, you don't just drop it in after the ORDER BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Oct 2007
    Posts
    27
    well no matter where i drop it, i get the same error. i don't know what i'm doing wrong.

    my appologies for my indent problem.

    Code:
    $sql = "SELECT Employee_ID, Miles, Activity, Minutes, Date
    
    FROM Activity_Log
    WHERE Employee_ID = '{$_POST["Employee_ID"]}'
    ORDER BY FORMAT(Date, 'mm')";
    Last edited by mstng07; 01-18-08 at 13:00.

Posting Permissions

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