# show total number of leave from table

I've two tables called work_details and holy_days.

Holy_days table contains a row for each holiday in each year. work_details contains a row for each day's work report for each employee.

I want to get the leave report (yearly & monthly) of all employees.

I want to show how many days of leave they took per year and also per month (with dates)

Calculate leave days except saturday, sunday and the dates in holy_days table. Am using the following code, but this is not working properly..anybody plz help.

```if (\$request->isPost()) {
\$sdate1 = \$request->get('txt_date1','post');
\$d1=explode('/', \$sdate1);
\$sdate= \$d1['2'].'-'.\$d1['0'].'-'.\$d1['1'];
\$edate1 = \$request->get('txt_date2','post');
\$d2=explode('/', \$edate1);
\$edate= \$d2['2'].'-'.\$d2['0'].'-'.\$d2['1'];
\$count=dates_between(\$emp_id,\$sdate,\$edate);

echo \$count;

}

function dates_between(\$emp_id,\$start_date, \$end_date = false)
{
if ( !\$end_date )
{
\$end_date = date("Y-m-d");
}

\$test_date = \$start_date;
\$day_incrementer = 1;
\$count_leaves=0;
\$flag=0;
echo "SELECT DISTINCT date FROM `work_details` WHERE  employee_id='".\$emp_id."' and date between '".\$start_date."' and '".\$end_date."'";
\$work_res = mysql_query("SELECT DISTINCT date FROM `work_details` WHERE  employee_id='".\$emp_id."' and date between '".\$start_date."' and '".\$end_date."'");

do
{
while(\$row=mysql_fetch_array(\$work_res))
{
while((date("Y-m-d",\$test_date)<\$row['date'])&&(\$flag=0))

{

if(!(date('N', strtotime(date("Y-m-d", \$test_date))) >= 6))
{
echo  "<tr><td align=center class=fontclass style=color:FF0000>".date("Y-m-d", \$test_date)."</td></tr>";
\$count_leaves++;
}
\$test_date = \$test_date + (\$day_incrementer * 60 * 60 * 24);

}
\$flag=1;

while((date("Y-m-d",\$test_date)!=\$row['date']))
{
if(!(date('N', strtotime(date("Y-m-d", \$test_date))) >= 6))
{
echo  "<tr><td align=center class=fontclass style=color:FF0000>".date("Y-m-d", \$test_date)."</td></tr>";
\$count_leaves++;
}
\$test_date = \$test_date + (\$day_incrementer * 60 * 60 * 24);

}
\$test_date = \$test_date + (\$day_incrementer * 60 * 60 * 24);

}
if(!(date('N', strtotime(date("Y-m-d", \$test_date))) >= 6)&&(\$test_date <= \$end_date))
{
echo  "<tr><td align=center class=fontclass style=color:FF0000>".date("Y-m-d", \$test_date)."</td></tr>";
\$count_leaves++;
\$test_date = \$test_date + (\$day_incrementer * 60 * 60 * 24);

}

}
while ( \$test_date <= \$end_date);

return(\$count_leaves);
}
```

Natasha . Check this out! You ll surely surprise :)

```function dates_between(\$emp_id,\$start_date, \$end_date = false)
{
\$qsdate=\$start_date;
\$qedate=\$end_date;
//echo \$start_date.\$end_date;
if ( !\$end_date )
{
\$end_date = date("Y-m-d");
}

\$start_date = is_int(\$start_date) ? \$start_date : strtotime(\$start_date);
\$end_date = is_int(\$end_date) ? \$end_date : strtotime(\$end_date);

\$test_date = \$start_date;
\$day_incrementer = 1;
\$count_leaves=0;
\$flag=0;

\$work_res = mysql_query("SELECT DISTINCT date FROM `work_details` WHERE  employee_id='".\$emp_id."' and date between '".\$qsdate."' and '".\$qedate."'");

while(\$row=mysql_fetch_array(\$work_res))
{
while((date("Y-m-d",\$test_date)<\$row['date'])&&(\$flag=0))

{

if(!(date('N', strtotime(date("Y-m-d", \$test_date))) >= 6))

{
echo  "<tr><td align=center class=fontclass style=color:FF0000>".date("Y-m-d", \$test_date)."</td></tr>";
\$count_leaves++;
}
\$test_date = \$test_date + (\$day_incrementer * 60 * 60 * 24);

}
\$flag=1;

while((date("Y-m-d",\$test_date)!=\$row['date']))
{
if(!(date('N', strtotime(date("Y-m-d", \$test_date))) >= 6))

{
echo  "<tr><td align=center class=fontclass style=color:FF0000>".date("Y-m-d", \$test_date)."</td></tr>";
\$count_leaves++;
}
\$test_date = \$test_date + (\$day_incrementer * 60 * 60 * 24);

}
\$test_date = \$test_date + (\$day_incrementer * 60 * 60 * 24);

}

while ( date("Y-m-d", \$test_date) <= date("Y-m-d", \$end_date))
{
if(!(date('N', strtotime(date("Y-m-d", \$test_date))) >= 6))

{
echo  "<tr><td align=center class=fontclass style=color:FF0000>".date("Y-m-d", \$test_date)."</td></tr>";
\$count_leaves++;

}
\$test_date = \$test_date + (\$day_incrementer * 60 * 60 * 24);

}

return(\$count_leaves);
}
```

```SELECT DISTINCT date
FROM `work_details`
WHERE  employee_id='".\$emp_id."'
and date between '".\$start_date."' and '".\$end_date."'
and date NOT IN(SELECT date from Holy_Days)
```

Assuming there is a date column in the Holy_Days table.