PHP/MySQL - 调度程序 - 垂直列

Question

我正在编写一个需要在垂直列结构中显示销售人员约会的应用程序。我能找到的最接近我想做的事情的东西看起来像这样:

推销员的名字在最上面，他们本周的约会是垂直安排的。对于销售员有约会的每一天(本例中为星期四和星期五)，还有一个漂亮的蓝色标题。

这是我的 MySQL APPOINTMENTS 表的设置方式:约会表:

======================================================================
   id   |   id_sales   |   id_lead   |   appt_date   |   appt_time   |
----------------------------------------------------------------------
   1    |   1          |   2         |   2013-12-26  |   11:00       |
----------------------------------------------------------------------
   2    |   1          |   3         |   2013-01-05  |   13:00       |
----------------------------------------------------------------------
   3    |   2          |   5         |   2013-12-27  |   13:00       |
----------------------------------------------------------------------
   4    |   2          |   6         |   2013-12-28  |   17:00       |
----------------------------------------------------------------------
   5    |   1          |   7         |   2013-12-29  |   19:30       |
======================================================================

id_sales 指的是销售人员的个人 ID(在另一个表中处理)，id_lead 指的是约会(也在另一个表中处理)。

截至目前，我不需要完整解释如何处理此任务的各个方面。我被困住了，不知道如何继续。我很乐意将销售员的 ID 作为表格标题，并在其下方添加约会 ID (id_lead)。在你的帮助下，我会在你的帮助下弄清楚剩下的部分；)

我将附上我想出的非工作代码:

日程表.php:

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

db_functions.php:

function get_schedule_all() {
    $headers = get_table_headers();
    $schedule_builder = '';
    $schedule_builder .= '<table border="1">';
    $schedule_builder .= $headers;
    $schedule_builder .= '</table>';
    return $schedule_builder;
}

function get_table_headers() {
    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 = '';
    $schedule_headers = '';
    $schedule_headers .= '<tr>';
    while ($row = $stmt->fetchObject()) {
        $id_sales = $row->id_sales;
        $schedule_headers .= "<th id='$id_sales'>$id_sales</th>";
        $appointments .= fill_appts($id_sales);
    }
    $schedule_headers .= '</tr>';
    $db = null;
    $schedule_headers .= $appointments;
    return $schedule_headers;
}

function fill_appts($id_sales) {
    include 'scripts/mysql_login_pdo.php';
    $query = "SELECT `id_lead` FROM `appointments` WHERE `id_sales` = :id_sales";
    $stmt = $db->prepare($query);
    $stmt->execute(array(
        ':id_sales' => $id_sales
    ));

    $schedule_appts = '';
    $schedule_appts .= '<tr>';
    while ($row = $stmt->fetchObject()) {
        $id_lead = $row->id_lead;
        $schedule_appts .= "<td>$id_lead</td>";
    }
    $schedule_appts .= '</tr>';
    $db = null;
    return $schedule_appts;
}

结果截图:

再次感谢您的帮助!

----------------------------------------更新---- ----------------------------------
得到它的工作。需要很多样式，但这是朝着正确方向的开始。

function get_schedule_all() {
    $headers = get_table_headers();
    $appointments = appointments();
    $schedule_builder = '';
    $schedule_builder .= '<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 `id_sales` FROM `appointments` ORDER BY `id_sales` ASC";
    $stmt = $db->prepare($query);
    $stmt->execute();
    $schedule_headers = '';
    $schedule_headers .= '<tr>';
    while ($row = $stmt->fetchObject()) {
        $id_sales = $row->id_sales;
        $schedule_headers .= "<td id='$id_sales'>$id_sales</td>";
    }
    $schedule_headers .= '</tr>';
    $db = null;
    return $schedule_headers;
}

function fill_appts($id_sales) {
    include 'scripts/mysql_login_pdo.php';
    $query = "SELECT `id_lead` FROM `appointments` WHERE `id_sales` = :id_sales";
    $stmt = $db->prepare($query);
    $stmt->execute(array(
        ':id_sales' => $id_sales
    ));

    $schedule_appts = '';
    $schedule_appts .= '<table>';
    while ($row = $stmt->fetchObject()) {
        $id_lead = $row->id_lead;
        $schedule_appts .= "<tr><td class='appt'>$id_lead</td></tr>";
    }
    $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;
}

Answer 1

您可以做的是代替 <td>$id_lead</td> , 创建另一个 <table>在 id sales 的每个单元格内，然后使用 <tr><td>$id_lead</td></tr>这样每个 $id_lead 都将排在 id_sales 下方。

这样，您将在表中将 2,3 和 7 作为 3 行，在 id_sales 1 的单元格内。将 5,6 作为表中的 2 行，在 id_sales 2 的单元格内。