6 replies to this topic

[newphpcoder]

Good Day!

I had problem in displaying datetime using select statement.

I have TimeIn and TimeOut with datatype DateTime

I want to display it (dd-mm-yyyy) and the hours is 12 hours with AM and PM.

now it display:

TimeIn: 2011-10-24 05:35:00
TimeOut: 2011-10-24 13:35:00

this is my code:

$sql = "SELECT EMP_NO, TimeIn, TimeOut FROM attendance WHERE Date BETWEEN '$DATE1' AND '$DATE2'";

I want to display the data
TimeIn: 24-10-2011 05:35 AM
TimeOut: 24-10-2011 014:35 PM

I tried to find syntax for that but it did not match on what format i want.


Thank you
Thank you

[Orjan]

as I've already told you, you need to reformat it with the date() function in php on output. that is the way to do it.

date("d-m-Y h:i A", $variableContainingTheTime);

is the code to show what you want.
if the date is in a text format, you would need to do

date("d-m-Y h:i A", strtotime($variableContainingTheTime));

[newphpcoder]

Orjan said:

as I've already told you, you need to reformat it with the date() function in php on output. that is the way to do it.

date("d-m-Y h:i A", $variableContainingTheTime);

is the code to show what you want.
if the date is in a text format, you would need to do

date("d-m-Y h:i A", strtotime($variableContainingTheTime));

How can I used this code in my select statement:

  $sql = "SELECT EMP_NO, TimeIn, TimeOut  FROM attendance WHERE Date BETWEEN '$DATE1' AND '$DATE2'";

Thank you

[Orjan]

You don't. You use it at output after fetching the information from the database into php
something like this:

$sql = "SELECT EMP_NO, TimeIn, TimeOut  FROM attendance WHERE Date BETWEEN '$DATE1' AND '$DATE2'";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result) {
  $in = strtotime($row['TimeIn']);
  $out = strtotime($row['TimeIn']);
  echo "Employee: ".$row['EMP_NO'];."<br />";
  echo "TimeIn: ".date("d-m-Y h:i A", $in)."<br />"; 
  echo "TimeOut: ".date("d-m-Y h:i A", $out)."<br />";
  echo "Worked time: ".date("H:i",$out -$in)."<br />";
}

[newphpcoder]

I tried this:

 $sql = "SELECT EMP_NO, TimeIn, date('$TimeOut', 'd-m-y g:i:s:A')  FROM attendance WHERE Date BETWEEN '$DATE1' AND '$DATE2'";

But no data display

---------- Post added at 06:06 AM ---------- Previous post was at 06:01 AM ----------

This is my original code with datetime format:

<?php


include 'config.php';


 if(!isset($_POST['submit_'])){ 

     

    $DATE1 = $_POST['firstinput'];

    $DATE2   = $_POST['secondinput'];

    


 $sql = "SELECT EMP_NO, TimeIn, TimeOut  FROM attendance WHERE Date BETWEEN '$DATE1' AND '$DATE2'";


    $attendance = $conn->GetAll($sql);

    $smarty->assign('attendance', $attendance);


 }

 $smarty->display('header.tpl');

 $smarty->display('left.tpl');

 $smarty->display('empAttendance.tpl');

 $smarty->display('footer.tpl');

  

?> 

empattendance.tpl:

<div class="income" style="width: 550px; height: 190px; overflow: auto; padding: 5px">

<fieldset>

<legend>Employee Attendance</legend>

<table border="1">

<tr>

<td colspan="2" style="text-align:center">Employee No</td>

<td colspan="2" style="text-align:center">Date/Time In</td>

<td colspan="2" style="text-align:center">Date/Time Out</td>

</tr>


{section name=att loop=$attendance}

  <tr>

    <td colspan="2">{$attendance[att].EMP_NO}</td>

    <td colspan="2">{$attendance[att].TimeIn}</td>

    <td colspan="2">{$attendance[att].TimeOut}</td>  

  </tr>

{sectionelse}

  <tr><td colspan="1">No DATA</td></tr>

{/section}

</table>

</fieldset>

</div>

[Orjan]

You need to learn what differences there are between PHP and SQL. You mix apples with elephants in your code.

Quote

$sql = "SELECT EMP_NO, TimeIn, date('$TimeOut', 'd-m-y g:i:s:A') FROM attendance WHERE Date BETWEEN '$DATE1' AND '$DATE2'";

Here, you do all mistakes possible and a few more. date() is a php function, not an MySQL function (well, there is one in MySQL too, but does and work totally different), you change the order of the parameters to the function so it would get errors. Look at my PHP code and study it carefully. you can't just throw in php functions into an SQL query unless it evaluates to some information needed in the query, and if so, you need to have it evaluated by the PHP, not just written in the query string. Now it's in the field list of the select which is just odd and faulty usage.

I believe that your usage of Smarty can help you a lot though, as it has a date format function.
in your .tpl, do this:

 <td colspan="2">{$attendance[att].TimeOut|date_format:"%d-%m-%Y %I:%M %p"}</td>

if you do, you don't have to use the php date function, it replaces it's usage.

[newphpcoder]

Orjan said:

You need to learn what differences there are between PHP and SQL. You mix apples with elephants in your code.



Here, you do all mistakes possible and a few more. date() is a php function, not an MySQL function (well, there is one in MySQL too, but does and work totally different), you change the order of the parameters to the function so it would get errors. Look at my PHP code and study it carefully. you can't just throw in php functions into an SQL query unless it evaluates to some information needed in the query, and if so, you need to have it evaluated by the PHP, not just written in the query string. Now it's in the field list of the select which is just odd and faulty usage.

I believe that your usage of Smarty can help you a lot though, as it has a date format function.
in your .tpl, do this:

 <td colspan="2">{$attendance[att].TimeOut|date_format:"%d-%m-%Y %I:%M %p"}</td>

if you do, you don't have to use the php date function, it replaces it's usage.

Thank you it's work...