PHP/MySQL - 在适当的工作日安排约会

Question

-----更新-----
非常感谢@Tin Tran。以下是任何感兴趣的人的工作代码:

db_functions.php:

function fill_appts($id_sales) {
include '../scripts/mysql_login_pdo.php';

$query = "SELECT leads.fname,
   leads.lname,
   leads.addr_city,
   appointments.appt_date,
   appointments.appt_time,
   appointments.id_lead,
   d.dayDesc
FROM (      SELECT 'Monday' as dayDesc,1 as dayOrder
  UNION SELECT 'Tuesday',          2
  UNION SELECT 'Wednesday',        3
  UNION SELECT 'Thursday',         4
  UNION SELECT 'Friday',           5
  UNION SELECT 'Saturday',         6
  UNION SELECT 'Sunday',           7
  )as d
LEFT JOIN appointments ON (DAYNAME(appointments.appt_date) = d.dayDesc)
INNER JOIN leads ON (leads.id = appointments.id_lead)
WHERE MONTH(appointments.appt_date) = MONTH(NOW())
AND appointments.id_sales = :id_sales
ORDER BY appointments.appt_date ASC,appointments.appt_time ASC,d.dayOrder ASC";

$stmt = $db->prepare($query);
$stmt->execute(array(
    ':id_sales' => $id_sales
));

$lastDay = '';
$last_appt_date = '';
$schedule_appts = '';
$schedule_appts .= '<table class="appointments">';

while ($row = $stmt->fetchObject()) {

    $id_lead = $row->id_lead;
    $fname = $row->fname;
    $lname = $row->lname;
    $addr_city = $row->addr_city;
    $appt_date = $row->appt_date;
    $appt_time = $row->appt_time;
    $dayName = $row->dayDesc;

    if (($dayName != $lastDay) && ($appt_date != $last_appt_date)) {
        $schedule_appts .= "<tr class='weekday' value='$appt_date'><td>" . date('l m/d', strtotime($appt_date)) . "</td></tr>";
    }

    $schedule_appts .= "<tr id='$id_lead'><td><div class='appt'>" .
            $fname . ' ' . $lname . '<br />' .
            $appt_date . '<br />' .
            $appt_time . "</div></td></tr>";
    $lastDay = $dayName;
    $last_appt_date = $appt_date;
}
$schedule_appts .= '</table>';
$db = null;
return $schedule_appts;

结果: -----更新结束-----

-----更新-----
今天早上做了一点工作。我使用更改的代码获得了重复的日期名称。关于如何选择不同的工作日有什么想法吗？

db_functions.php:

...
function fill_appts($id_sales) {
    include '../scripts/mysql_login_pdo.php';
    $query = "SELECT `leads`.`fname`, "
            . "`leads`.`lname`, "
            . "`leads`.`addr_city`, "
            . "`appointments`.`appt_date`, "
            . "`appointments`.`appt_time`, "
            . "`appointments`.`id_lead` "
            . "FROM `leads`,`appointments` "
            . "WHERE `leads`.`id` = `appointments`.`id_lead` "
            . "AND MONTH(`appointments`.`appt_date`) = MONTH(NOW()) "
            . "AND `appointments`.`id_sales` = :id_sales "
            . "ORDER BY `appointments`.`appt_time` ASC";

    $stmt = $db->prepare($query);
    $stmt->execute(array(
        ':id_sales' => $id_sales
    ));

    $schedule_appts = '';
    $schedule_appts .= '<table class="appointments">';

    while ($row = $stmt->fetchObject()) {
        $id_lead = $row->id_lead;
        $fname = $row->fname;
        $lname = $row->lname;
        $addr_city = $row->addr_city;
        $appt_date = $row->appt_date;
        $appt_time = $row->appt_time;
//------------------------Problematic area-----------------------
        $schedule_appts .= "<tr class='weekday' value='$appt_date'><td>" . date('l', strtotime($appt_date)) . "</td></tr>";
//---------------------------------------------------------------
        $schedule_appts .= "<tr id='$id_lead'><td><div class='appt'>" .
                $fname . ' ' . $lname . '<br />' .
                $appt_date . '<br />' .
                $appt_time . "</div></td></tr>";
    }
    $schedule_appts .= '</table>';
    $db = null;
    return $schedule_appts;
}
...

-----结束更新-----

这是以下帖子的延续: PHP/MySQL - Dispatch Scheduler - Vertical Column

如前一篇文章所述，我离目标越来越近了。我正处于我希望约会在适当的工作日下方垂直堆叠的地步。我将展示我所拥有的和我想要的东西的图像，因为这会更容易理解。

我有什么:

我想要的:

这是我的代码，它生成第一张图片。我在“fill_appts”函数中指出了我认为我的问题所在:

日程表.php:

...
<div id="schedule_wrapper_all">
  <?php print get_schedule_all(); ?>
</div>
...

db_functions.php:

function get_schedule_all() {
    $headers = get_table_headers();
    $appointments = appointments();
    $schedule_builder = '';
    $schedule_builder .= '<table class="appt_table">';
    $schedule_builder .= $headers;
    $schedule_builder .= $appointments;
    $schedule_builder .= '</table>';
    return $schedule_builder;
}
function get_table_headers() {
    include '../scripts/mysql_login_pdo.php';
    $query = "SELECT DISTINCT `appointments`.`id_sales`,`users`.`fname`,`users`.`lname` "
            . "FROM `appointments`,`users` "
            . "WHERE `appointments`.`id_sales` = `users`.`id` "
            . "ORDER BY `appointments`.`id_sales` ASC";
    $stmt = $db->prepare($query);
    $stmt->execute();
    $schedule_headers = '';
    $schedule_headers .= '<tr>';
    while ($row = $stmt->fetchObject()) {
        $id_sales = $row->id_sales;
        $fname = $row->fname;
        $lname = $row->lname;
        $schedule_headers .= "<th id='$id_sales'>" . $fname . ' ' . $lname . "</th>";
    }
    $schedule_headers .= '</tr>';
    $db = null;
    return $schedule_headers;
}
function fill_appts($id_sales) {
    include '../scripts/mysql_login_pdo.php';
    $query = "SELECT `leads`.`fname`, "
            . "`leads`.`lname`, "
            . "`leads`.`addr_city`, "
            . "`appointments`.`appt_date`, "
            . "`appointments`.`appt_time`, "
            . "`appointments`.`id_lead` "
            . "FROM `leads`,`appointments` "
            . "WHERE `leads`.`id` = `appointments`.`id_lead` "
            . "AND MONTH(`appointments`.`appt_date`) = MONTH(NOW()) "
            . "AND `appointments`.`id_sales` = :id_sales "
            . "ORDER BY `appointments`.`appt_time` ASC";

    $stmt = $db->prepare($query);
    $stmt->execute(array(
        ':id_sales' => $id_sales
    ));

    $schedule_appts = '';
    $schedule_appts .= '<table class="appointments">';
    $schedule_appts .= get_weekdays();
    while ($row = $stmt->fetchObject()) {
        $id_lead = $row->id_lead;
        $fname = $row->fname;
        $lname = $row->lname;
        $addr_city = $row->addr_city;
        $appt_date = $row->appt_date;
        $appt_time = $row->appt_time;
------------------------------------------------------------------------------------
//THIS IS WHERE I BELIEVE I NEED TO LOOP THROUGH RESULTS AGAIN:
//        foreach ($day as $weekday){
        $schedule_appts .= "<tr id='$id_lead'><td><div class='appt'>" .
                $fname . ' ' . $lname . '<br />' .
                $appt_date . '<br />' .
                $appt_time . "</div></td></tr>";
//        }
//END LOOP
------------------------------------------------------------------------------------**
    }
    $schedule_appts .= '</table>';
    $db = null;
    return $schedule_appts;
}
function appointments() {
    include '../scripts/mysql_login_pdo.php';
    $query = "SELECT DISTINCT `id_sales` FROM `appointments` ORDER BY `id_sales` ASC";
    $stmt = $db->prepare($query);
    $stmt->execute();
    $appointments = '';
    $appointments .= '<tr>';
    while ($row = $stmt->fetchObject()) {
        $id_sales = $row->id_sales;
        $appointments .= '<td>';
        $appointments .= fill_appts($id_sales);
        $appointments .= '</td>';
    }
    $appointments .= '</tr>';
    $db = null;
    return $appointments;
}

function get_weekdays() {
    $monday = date("Y-m-d",strtotime('monday this week'));
    $tuesday = date("Y-m-d",strtotime('tuesday this week'));
    $wednesday = date("Y-m-d",strtotime('wednesday this week'));
    $thursday = date("Y-m-d",strtotime('thursday this week'));
    $friday = date("Y-m-d",strtotime('friday this week'));
    $saturday = date("Y-m-d",strtotime('saturday this week'));
    $sunday = date("Y-m-d",strtotime('sunday this week'));
    $week = "<tr class='weekday' value='$monday'><td>Monday</td></tr>"
            . "<tr class='weekday' value='$tuesday'><td>Tuesday</td></tr>"
            . "<tr class='weekday' value='$wednesday'><td>Wednesday</td></tr>"
            . "<tr class='weekday' value='$thursday'><td>Thursday</td></tr>"
            . "<tr class='weekday' value='$friday'><td>Friday</td></tr>"
            . "<tr class='weekday' value='$saturday'><td>Saturday</td></tr>"
            . "<tr class='weekday' value='$sunday'><td>Sunday</td></tr>";
    return $week;
}

如您在我的函数中所见，这显示了给定月份内所有员工的所有约会。我同意脚本为每个月的每一天生成工作日名称，或者动态地只在当天安排约会时才生成工作日。我基于一周编写了“get_weekdays”函数，需要对其进行更新以检查一个月内的每一天。如果那部分太多了，我会继续研究答案。

提前感谢您的指导。与上一篇文章一样，我不是在找人为我编写函数，只是在正确的方向上给我一记 ;)

Answer 1

看看DAYNAME()功能。然后也许不是获取所有工作日，而是一次获取一个并循环遍历您的结果集以查看它是否是同一天并将其打印出来。要重置到结果集的开头，您可以使用 http://www.php.net/manual/en/mysqli-result.data-seek.php

正如我的评论中提到的:尝试下面的查询并查看

SELECT leads.fname,
       leads.lname,
       leads.addr_city,
       appointments.appt_date,
       appointments.appt_time,
       appointments.id_lead,
       d.dayDesc
FROM (      SELECT 'Monday' as dayDesc,1 as dayOrder
      UNION SELECT 'Tuesday',          2
      UNION SELECT 'Wednesday',        3
      UNION SELECT 'Thursday',         4
      UNION SELECT 'Friday',           5
      UNION SELECT 'Saturday',         6
      UNION SELECT 'Sunday',           7
      )as d
LEFT JOIN appointments ON (DAYNAME(appointments.appt_date) = d.dayDesc)
INNER JOIN leads ON (leads.id = appointments.id_lead)
WHERE MONTH(appointments.appt_date) = MONTH(NOW())
AND appointments.id_sales = :id_sales
ORDER BY d.dayOrder ASC,appointments.appt_time ASC