Board index   FAQ   Search  
Register  Login
Board index php forum :: php coding PHP coding => General

how to find out the missing dates between the given 2 dates.

Ask about general coding issues or problems here.

Moderators: macek, egami, gesf

how to find out the missing dates between the given 2 dates.

Postby Divyatamizh » Fri Apr 05, 2013 4:32 am

Hi here is my code which retrieves the records from database and calculates the total hours worked by an employee.
Code: Select all

<?php

$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("first_db", $con);


$count = 0;

$result = mysql_query("SELECT total_hours FROM emp_entry  ");

while($row = mysql_fetch_array($result))
  {
   $values[] = $row['total_hours'];

   $count++;
}

for($i=0;$i<$count;$i++)
{
   $hrs = date('H', strtotime($values[$i]));
   $min = date('i', strtotime($values[$i]));
   $sec = date('s', strtotime($values[$i]));
   $hrstot = $hrs * 3600;
   $mintot = $min * 60;
   $tottime = $hrstot + $mintot + $sec;
   $addtime[$i] = $tottime;
}

$timess = 0;

for($i=0;$i<$count;$i++)
{
   $timess = $timess +  $addtime[$i];
}

$final = $timess;

$hr = floor($final / 3600);
$mn = floor(($final / 60) % 60);
$sd = $final % 60;

echo $hr.":".$mn.":".$sd;


?>


this works perfectly to calculate total hours worked. And i can get the records of worked hours for particular dates also.
Code: Select all

<?php

echo "<table >";
         echo "<form action='time.php' method='post'";
         echo "<tr><td>Emp ID </td><td>: <input type='text' value='' name='empid'></td></tr>";
         echo "<tr><td>A Date </td><td>: <input readonly type='text' id='datepicker' name='d1' value='' /></td></tr>";

      echo "<tr><td>To Date </td><td>: <input readonly type='text' id='datepicker1' name='d2' value='' /></td></tr>";

         echo "<tr></td><td><td><input type='submit' value='Submit' name='submit'></td></tr>";
         echo "</form>";
         echo "</table>";

if(isset($_POST['submit']))
{

$eid=$_POST['empid'];

$from_date=date('Y-m-d', strtotime($_POST['d1']));
$to_date=date('Y-m-d', strtotime($_POST['d2']));

$count = 0;

$result=mysql_query("SELECT total_hours FROM emp_entry WHERE emp_id='$eid' AND (date BETWEEN '$from_date' AND '$to_date') ");

while($row = mysql_fetch_array($result))
  {
   $values[] = $row['total_hours'];
//   $id=$row['empid'];
   $count++;
}

//code
?>


Now i want to find the missing dates in between the two given dates...... :? help need
thanks in advance....
Divyatamizh
New php-forum User
New php-forum User
 
Posts: 9
Joined: Sun Mar 03, 2013 11:56 pm

Re: how to find out the missing dates between the given 2 da

Postby seandisanti » Fri Apr 05, 2013 8:39 am

there are a few ways to do what you want, the easiest would probably be to create a date table, that just has dates, then you can use a join or a subquery depending on your comfort level to return only dates not present in the date field of your other table. Also, mysql_ functions are deprecated. look into PDO before they go away. here's a short video tutorial showing how easy PDO is to use. http://jream.com/learning/videos/php-oo ... o-examples
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Re: how to find out the missing dates between the given 2 da

Postby johnj » Fri Apr 05, 2013 7:56 pm

johnj
php-forum Super User
php-forum Super User
 
Posts: 1805
Joined: Thu Mar 10, 2011 5:07 pm

Re: how to find out the missing dates between the given 2 da

Postby Divyatamizh » Mon Apr 08, 2013 1:35 am

Hi friends, the following code works to find the missing dates in between 2 given dates...
Code: Select all

<?php

$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("first_db", $con);

?>



<?php




echo "<table>";
echo "<form action='date.php' method='post'";
         echo "<tr><td>Emp ID </td><td>: <input type='text' value='' name='empid'></td></tr>";
         echo "<tr><td>A Date </td><td>: <input readonly type='text' id='datepicker' name='d1' value='' /></td></tr>";

         echo "<tr><td>To Date </td><td>: <input readonly type='text' id='datepicker1' name='d2' value='' /></td></tr>";

         echo "<tr></td><td><td><input type='submit' value='Submit' name='submit'></td></tr>";
echo "</form>";
echo "</table>";


if(isset($_POST['submit']))
{

$eid=$_POST['empid'];

$from_date=date('Y-m-d', strtotime($_POST['d1']));
$to_date=date('Y-m-d', strtotime($_POST['d2']));




$count = 0;

$result=mysql_query("SELECT * FROM emp_entry WHERE emp_id='$eid' AND (date BETWEEN '$from_date' AND '$to_date') ");

while($row = mysql_fetch_array($result))
  {
   $values[] = $row['date'];
   $arrtm[]=$row['total_hours'];

   $count++;
  }

for($i=0;$i<$count;$i++)
{
    $val=$values[$i]; print"<br>";
   $t=$arrtm[$i];


}


$a1= print_r($values,true);

$start = $from_date;
$end = $to_date;

$init_date = strtotime($start);
$dst_date = strtotime($end);

$offset = $dst_date-$init_date;

$dates = floor($offset/60/60/24) + 1;

for ($i = 0; $i < $dates; $i++)
{
   $newdate = date("Y-m-d", mktime(12,0,0,date("m", strtotime($start)),
   (date("d", strtotime($start)) + $i), date("Y", strtotime($start))));
   $num_dates[]=$newdate ;
   



   
}


print_r(array_diff($num_dates,$values));



}
?>






compare the difference of two array gives the result....
Divyatamizh
New php-forum User
New php-forum User
 
Posts: 9
Joined: Sun Mar 03, 2013 11:56 pm


Return to PHP coding => General

Who is online

Users browsing this forum: Google [Bot] and 0 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.