几个月前,我解决了从两个表中获取数据并制作事件日历的问题。你可以在这里看到:原始问题。现在我想要更多:我想在同一时间为每个用户做一个活动的广告。比方说这是一个节日(像圣诞节),我想为所有用户添加此活动。我使用两个查询,第一个是选择假期,第二个是选择用户是否在场。如果存在tip_id=0,如果不存在tip_id=1。然后我把所有的东西都放在一张桌子上。如果当天是星期六或星期日,则颜色为灰色(使用css1),如果用户在场,则单元格颜色为白色,如果用户不在场,则颜色是红色。我需要什么?如果我在那个月有假期,我需要单元格颜色也为灰色(如周六和周日)。我尝试了不同的想法,但或者我只有在第一个假期才变灰,或者只有在最后一个假期,或者当那个月没有假期时我犯了错误。代码(只提取了我需要帮助的部分)如下:
$db_luna=11;
$db_an=2013;
$days_in_month = 31;
mysql_select_db($database_dbconfig, $dbconfig);
$query_Holidays = "SELECT substring(data,9,2) AS zile_sarbatoare, data FROM sarbatori WHERE sarbatori.`data` LIKE '".$db_an."-".$db_luna."-%' ORDER BY zile_sarbatoare";
$Holidays = mysql_query($query_Holidays, $dbconfig) or die(mysql_error());
$row_Holidays = mysql_fetch_assoc($Holidays);
$totalRows_Holidays = mysql_num_rows($Holidays);
mysql_select_db($database_dbconfig, $dbconfig);
$query_Presence = "SELECT c.full_name, c.id_personal, COALESCE(MIN(CASE WHEN CONCAT($db_an, '-', $db_luna, '-01') BETWEEN start_date AND end_date THEN tip_id END),0) '1', COALESCE(MIN(CASE WHEN CONCAT($db_an, '-', $db_luna, '-02') BETWEEN start_date AND end_date THEN tip_id END),0) '2', COALESCE(MIN(CASE WHEN CONCAT($db_an, '-', $db_luna, '-03') BETWEEN start_date AND end_date THEN tip_id END),0) '3', COALESCE(MIN(CASE WHEN CONCAT($db_an, '-', $db_luna, '-04') BETWEEN start_date AND end_date THEN tip_id END),0) '4', COALESCE(MIN(CASE WHEN CONCAT($db_an, '-', $db_luna, '-05') BETWEEN start_date AND end_date THEN tip_id END),0) '5' FROM calendar c";
$Presence = mysql_query($query_Presence, $dbconfig) or die(mysql_error());
$row_Presence = mysql_fetch_assoc($Presence);
$totalRows_Presence = mysql_num_rows($Presence);}
<?php do { ?>
<tr>
<td><?php echo $nr_crt, '. ' , $row_Presence['full_name']; ?></td>
<?php
$css1 = 'white';
$trim = '';
for ($a = 1; $a <= $days_in_month; $a++){
$data_calculata = date("Y-m-d",mktime(0,0,0, $db_luna, $b, $db_an ));
if ($row_Presence[$a] == 0) {$legend = $row_Presence['full_name']."".$a;
$day_of_week = date("N",mktime(0,0,0, $db_luna,$a ,$db_an));
if ($day_of_week == 6 OR $day_of_week == 7) {$css1 = 'grey';}
else {$css1 = 'white';}
if ($row_Presence[$a] == 1) {$css1 = 'red'; $legend = $row_Presence['full_name']." - Absent";}
echo '<td " title= "'.htmlspecialchars($legend).'" class='.$css1.'></td>';
$nr_crt = $nr_crt + 1;
?>
</tr>
<?php } while ($row_Presence = mysql_fetch_assoc($Presence)); ?>
</table>
我找到的解决方案如下:
$db_luna=11;
$db_an=2013;
$days_in_month = 31;
mysql_select_db($database_dbconfig, $dbconfig);
$query_Holidays = "SELECT substring(data,9,2) AS zile_sarbatoare, data FROM sarbatori WHERE sarbatori.`data` LIKE '".$db_an."-".$db_luna."-%' ORDER BY zile_sarbatoare";
$Holidays = mysql_query($query_Holidays, $dbconfig) or die(mysql_error());
$totalRows_Holidays = mysql_num_rows($Holidays);
$days_holidays = array();
while ($row_Holidays = mysql_fetch_array($Holidays))
{
$days_holidays[] = $row_Holidays['zile_sarbatoare'];
}
// so, now I have an array with all the days I need
mysql_select_db($database_dbconfig, $dbconfig);
$query_Presence = "SELECT c.full_name, c.id_personal, COALESCE(MIN(CASE WHEN CONCAT($db_an, '-', $db_luna, '-01') BETWEEN start_date AND end_date THEN tip_id END),0) '1', COALESCE(MIN(CASE WHEN CONCAT($db_an, '-', $db_luna, '-02') BETWEEN start_date AND end_date THEN tip_id END),0) '2', COALESCE(MIN(CASE WHEN CONCAT($db_an, '-', $db_luna, '-03') BETWEEN start_date AND end_date THEN tip_id END),0) '3', COALESCE(MIN(CASE WHEN CONCAT($db_an, '-', $db_luna, '-04') BETWEEN start_date AND end_date THEN tip_id END),0) '4', COALESCE(MIN(CASE WHEN CONCAT($db_an, '-', $db_luna, '-05') BETWEEN start_date AND end_date THEN tip_id END),0) '5' FROM calendar c";
$Presence = mysql_query($query_Presence, $dbconfig) or die(mysql_error());
$row_Presence = mysql_fetch_assoc($Presence);
$totalRows_Presence = mysql_num_rows($Presence);}
<?php do { ?>
<tr>
<td><?php echo $nr_crt, '. ' , $row_Presence['full_name']; ?></td>
<?php
$css1 = 'white';
$trim = '';
for ($a = 1; $a <= $days_in_month; $a++){
$data_calculata = date("Y-m-d",mktime(0,0,0, $db_luna, $b, $db_an ));
if ($row_Presence[$a] == 0) {$legend = $row_Presence['full_name']."".$a;
$day_of_week = date("N",mktime(0,0,0, $db_luna,$a ,$db_an));
$css1 = 'white';
if ($day_of_week == 6 OR $day_of_week == 7) {$css1 = 'grey';}
if (in_array($a, $days_holidays)) {$css1 = 'grey';} //here I compare each day if it is in the array
if ($row_Presence[$a] == 1) {$css1 = 'red'; $legend = $row_Presence['full_name']." - Absent";}
echo '<td " title= "'.htmlspecialchars($legend).'" class='.$css1.'></td>';
$nr_crt = $nr_crt + 1;
?>
</tr>
<?php } while ($row_Presence = mysql_fetch_assoc($Presence)); ?>
对我来说,它正在发挥作用!使用函数"in_array"的想法来自本网站的另一个问题。谢谢stackoverflow:)