php请假审核怎么建立数据库,php – 请假申请月报

我有两个表,一个是员工姓名,员工ID和另一个表tblleaves有empid,Leave_Date,fromDate,toDate,Description.

如果员工选择一个日期,则将日期值存储到Leave_Date,如果员工选择多个日期,则存储从日期到日期的值.

在输出页面中,我想要一个员工姓名,Leave Days和Leave Dates.请假日期有Leave_date,FromDate和ToDate的日期.

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

$ym=$_POST['month'];

list($Year, $Month) = explode("-", "$ym", 2);

$sql = "SELECT

tblemployees.FirstName,

tblemployees.LastName,

count(tblleaves.empid) as Leave_Days,

GROUP_CONCAT( tblleaves.Leave_Date SEPARATOR ', ' ) AS leave_dates

FROM

tblleaves

JOIN tblemployees

ON tblleaves.empid = tblemployees.id

WHERE YEAR(Leave_Date) = $Year

AND MONTH(Leave_Date) = $Month

GROUP BY tblemployees.EmpId";

$query = $dbh -> prepare($sql);

$query->execute();

$results=$query->fetchAll(PDO::FETCH_OBJ);

$cnt=1;

if($query->rowCount() > 0)

{

foreach($results as $result)

{ ?>

<?php echo htmlentities($cnt);?><?php echo htmlentities($result->FirstName);?> <?php echo htmlentities($result->LastName);?><?php echo htmlentities($result->Leave_Days);

?>

<?php echo htmlentities($result->leave_dates);

?>

<?php $cnt++;}}}?>

我想要页面输出

employee name Leave Days Leave Dates

KrishnanR 3 12-06-2019, 13-06-2019, 14-06-2019

(FromDate and ToDate)

PrakashR 1 12-06-2019

(Leave_Date)

SelvaK 3 12-06-2019,13-06-2019&14-06-2019,| 14-06-2019

(FromDate and ToDate) | (Leave_Date)

解决方法:

根据您的要求更新了您的代码,未对其进行测试.试一试吧.

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

$ym=$_POST['month'];

list($Year, $Month) = explode("-", "$ym", 2);

$sql = "SELECT tblemployees.FirstName, tblemployees.LastName, count(tblleaves.empid) as Leave_Days,

IF( t2.FromDate IS NOT NULL AND t2.ToDate IS NOT NULL ,

CONCAT(t2.FromDate, ',', t2.ToDate, ',' ,t2.Leave_Date),

t2.Leave_Date) as 'Leave_Dates'

FROM tblleaves JOIN tblemployees ON tblleaves.empid = tblemployees.id

WHERE YEAR(Leave_Date) = $Year AND MONTH(Leave_Date) = $Month

GROUP BY tblemployees.EmpId";

$query = $dbh -> prepare($sql);

$query->execute();

$results=$query->fetchAll(PDO::FETCH_OBJ);

$cnt=1;

if($query->rowCount() > 0)

{

foreach($results as $result)

{

$leavedates = explode(',', $result->Leave_Dates);

$period = new DatePeriod(

new DateTime($leavedates[0]),

new DateInterval('P1D'),

new DateTime($leavedates[1])

);

$listofleaves = [];

foreach ($period as $key => $value) {

$listofleaves[] = $value->format('Y-m-d');

}

$listofleaves[] = $leavedates[2];

?>

<?php echo htmlentities($cnt);?><?php echo htmlentities($result->FirstName);?> <?php echo htmlentities($result->LastName);?><?php echo htmlentities($result->Leave_Days);

?>

<?php echo htmlentities(implode(',' , $listofleaves);

?>

<?php $cnt++;}}}?>

标签：php,pdo,mysqli

来源： https://codeday.me/bug/20191003/1850476.html