在Drupal中使用MYSQL查询可排序的HTML表 - Sortable HTML table using MYSQL query in Drupal

好吧，我尽量把这个问题讲清楚。我们最近才开始使用Drupal，第三方为我们构建了部分站点。我们有一个视图文件，它查询Mysql数据库并从表中提取符合特定搜索条件的课程。比如2014年秋季的所有课程。他们会根据CID或课程ID来订购。Views页面包括Schedules结果PHP页面，该页面随后将信息呈现到一个表中。我们希望在表中有可排序的列。我试过添加一个jscript文件，它会这样做，而不重新加载表信息，但它没有工作，我不知道为什么。我试过使用Drupal_add_js以及只是插入JS内联。我也试过做href='/shedules?Orderby =cname"，但这似乎并没有从视图页面中保留查询的数据。我对此感到非常沮丧，我不知道还能去哪里找。如果您能给我任何指导，我将不胜感激!

这里是视图页面

 <!--
    <pre>
        <? print_r($_POST); ?>
    </pre>
 -->
    <?php
        $query = " SELECT * FROM ";
        $semester = $_POST['semester'];
        if(empty($_POST['text'])){
            if($_POST['currentcourse'] == "1"){
                $query .= " $semester WHERE (sdate <= NOW() OR sdate is NULL)";
            } else {
                $query .= " $semester WHERE (1=1)";
            }
        } else {
            //$query .= " $semester WHERE (1=1)";
            $query .= " $semester WHERE (sdate >= NOW() OR sdate is NULL)";
        }
        if( isset( $_POST['online'] ) && 'ON' == $_POST['online'] )
            $query .=  " AND ( `method`='WEB' OR `method`='Online' ) ";

        if( isset( $_POST['text'] ) && !empty( $_POST['text'] ) ){
            $words = explode(' ', urldecode( $_POST['text'] ) );
            $fields = array( 'division', 'cid', 'cname', 'instructor' );
            if( count($words) > 1){
                // multi word search
                $q = array();
                foreach( $words as $w ){
                    $part = array();
                    foreach( $fields as $f ){
                        $part[] = " $f LIKE '%$w%' ";
                    }
                    $q[] = $part;
                }
                $final_parts = array();
                foreach( $q as $query_part )
                    $final_parts[] = implode( ' OR ', $query_part);
                $final_query = ' AND (' . implode(') AND (', $final_parts) . ')';
            }else{
                // single word search
                $part = array();
                foreach( $fields as $f ){
                    $part[] = " $f LIKE '%$_POST[text]%' ";
                }
                $final_query = ' AND ('.implode( ' OR ', $part).')';
            }
            $query .= " " . $final_query . " ";
        }
        if( isset( $_POST['area_id'] ) && '-1' != $_POST['area_id'] )
            $query .= " AND cid LIKE '" . $_POST['area_id'] . "%'";
        if( isset( $_POST['division_id'] ) && '-1' != $_POST['division_id'] )
            $query .= " AND division = '" . $_POST['division_id'] . "'";
        if( isset( $_POST['duration'] ) && '-1' != $_POST['duration'] )
            $query .= " AND duration_min " . ( ( $_POST['durcomp'] == 'more' ) ? ' >= ' : ' <= ' )  .     $_POST['duration'] . " ";

        $query .= " AND ( 1=1 ";
        if( isset($_POST['time']) && isset($_POST['time'][0]) && !empty($_POST['time'][0]) ){
            $start_parts = explode(' ', $_POST['time'][0]);
            $start_time = ((int)$start_parts[0] * 60) + (int)$start_parts[1];
            $query .= " AND stime_min >= $start_time ";
        }
        if( isset($_POST['time']) && isset($_POST['time'][1]) && !empty($_POST['time'][1]) ){
            $end_parts = explode(' ', $_POST['time'][1]);
            $end_time = ((int)$end_parts[0] * 60) + (int)$end_parts[1];
            $query .= "AND etime_min <= $end_time ";
        }
        $query .= " ) ";

        $query .= " AND ( 1=1 ";

                if( isset( $_POST['T'] ) && 'ON' == $_POST['T'] )
                    $query .=  ( $_POST['dayop'] == 'AND' ) ? ' AND ' : ' OR ' . "  day_T = 1 ";
                if( isset( $_POST['W'] ) && 'ON' == $_POST['W'] )
                    $query .=  ( $_POST['dayop'] == 'AND' ) ? ' AND ' : ' OR ' . "  day_W = 1 ";
                if( isset( $_POST['R'] ) && 'ON' == $_POST['R'] )
                    $query .=  ( $_POST['dayop'] == 'AND' ) ? ' AND ' : ' OR ' . "  day_R = 1 ";
                if( isset( $_POST['F'] ) && 'ON' == $_POST['F'] )
                    $query .=  ( $_POST['dayop'] == 'AND' ) ? ' AND ' : ' OR ' . "  day_F = 1 ";
                if( isset( $_POST['S'] ) && 'ON' == $_POST['S'] )
                    $query .=  ( $_POST['dayop'] == 'AND' ) ? ' AND ' : ' OR ' . "  day_S = 1 ";

        $query .= " ) ORDER BY cid";
 /*             echo $query; */

        $string_query = $query;

        $query = db_query($query);
        $result = $query->fetchAll();
        $in_progress = 0;
        foreach ($result as $r) {
            if (!empty($r->sdate) && strtotime($r->sdate) < time()) {
                $in_progress++;
            }
        }

   endif; ?>

<?php
require( __dir__ . '/course-schedule-headings-table.php');
?>
<?php
require( __dir__ . '/course-schedule-results.php');
?>

这里是包含的结果页

<?php $now = time();?>
<table id="scheduletable" style="width:450px; font-size :10px;">

<thead>
    <tr valign="top">
        <th scope="col" bgcolor="#FFFFCC"><a href="/schedules?sort=cid">Course ID</a><br></th>
        <th scope="col" bgcolor="#E0E0E0" style="width: 12px"><a href="/schedules?sort=cname">Course name</a>    </th>
        <th scope="col" bgcolor="#E0E0E0"><a href="/schedules?sort=credits">Credits</a></th>
        <th scope="col" bgcolor="#E0E0E0"><a href="/schedules?sort=method">Type</a></th>
        <th scope="col" bgcolor="#E0E0E0"><a href="/schedules?sort=sdate">Start date</a></th>
        <th scope="col" bgcolor="#E0E0E0"><a href="/schedules?sort=edate">End date</a></th>
        <th scope="col" bgcolor="#E0E0E0"><a href="/schedules?sort=days">Days</a></th>
        <th scope="col" bgcolor="#E0E0E0"><a href="/schedules?sort=stime">Start time</a></th>
        <th scope="col" bgcolor="#E0E0E0"><a href="/schedules?sort=etime">End time</a></th>
        <th scope="col" bgcolor="#E0E0E0"><a href="/schedules?sort=instructor">Instructor</a></th>
    </tr>
</thead>

<?php foreach($result as $course): ?>

    <?php
        $color="#FFFFFF";
        if( !empty($course->sdate) && strtotime($course->sdate) < $now )
            $color = $color_in_progress;
        if( "Online" == trim( $course->method ) )
            $color = $color_online;
        if( "Hybrid" == trim( $course->method ) )
            $color = $color_hybrid;
        if( !empty($course->stime_min) && $course->stime_min >= 960 )
            $color = $color_evening;
    ?>
    <tbody>
        <tr>
        <tr bgcolor="<?=$color?>">
            <td align="center">
                <a href="/courses/?id=<?= $course->cid ?>">
                    <?= $course->cid ?>
                </a>
            </td>
            <td class="name" style="width: 1.5">
                <?= $course->cname ?>
            </td>
            <td class="small" align="center">
                <?= $course->credits ?>
                <?= ("COMP100 CE0" == $course->cid) ? '.50' : null?>
            </td>
            <td align="center">
                <?= $course->type?>
            </td>
            <td align="center">
                <?= date( "m/d", strtotime( $course->sdate ) )?>
            </td>
            <td align="center">
                <?= date( "m/d", strtotime( $course->edate ) )?>
            </td>
            <td align="center">
                <?= $course->days ?>
            </td>
            <td align="center">
                <?= date( "h:i", strtotime( $course->stime ) )?>
            </td>
            <td align="center">
                <?= date( "h:i", strtotime( $course->etime ) )?>
            </td>
            <td align="center">
                <?= $course->instructor ?>
            </td>
        </tr>
    </tbody>
<? endforeach; ?>
 </table>

下面是表输出示例:样式化是根据查询结果动态完成的，这是在执行搜索后来自视图源的。

<table id="scheduletable" style="width:450px; font-size :10px;">

<thead>
    <tr valign="top">
        <th scope="col" bgcolor="#FFFFCC"><a href="/schedules?sort=cid">Course ID</a><br></th>
        <th scope="col" bgcolor="#E0E0E0" style="width: 12px"><a href="/schedules?sort=cname">Course name</a>    </th>
        <th scope="col" bgcolor="#E0E0E0"><a href="/schedules?sort=credits">Credits</a></th>
        <th scope="col" bgcolor="#E0E0E0"><a href="/schedules?sort=method">Type</a></th>
        <th scope="col" bgcolor="#E0E0E0"><a href="/schedules?sort=sdate">Start date</a></th>
        <th scope="col" bgcolor="#E0E0E0"><a href="/schedules?sort=edate">End date</a></th>
        <th scope="col" bgcolor="#E0E0E0"><a href="/schedules?sort=days">Days</a></th>
        <th scope="col" bgcolor="#E0E0E0"><a href="/schedules?sort=stime">Start time</a></th>
        <th scope="col" bgcolor="#E0E0E0"><a href="/schedules?sort=etime">End time</a></th>
        <th scope="col" bgcolor="#E0E0E0"><a href="/schedules?sort=instructor">Instructor</a></th>
    </tr>
</thead>


    <tbody>
        <tr>
        <tr bgcolor="#FFCCCC">
            <td align="center">
                <a href="/courses/?id=ABED003 11">
                    ABED003 11                  </a>
            </td>
            <td class="name" style="width: 1.5">
                Basic Education             </td>
            <td class="small" align="center">
                6                                   </td>
            <td align="center">
                                </td>
            <td align="center">
                08/18               </td>
            <td align="center">
                10/10               </td>
            <td align="center">
                MTWTHF              </td>
            <td align="center">
                08:30               </td>
            <td align="center">
                10:50               </td>
            <td align="center">
                Melissa Escamilla               </td>
        </tr>
    </tbody>

好了，我可以使用前面提到的sortable.js对表进行排序。只需加载脚本并将table类设置为"sortable"。

即:

<table id="scheduletable" class="sortable">

下面是一个JSFiddle示例:http://jsfiddle.net/yfbedzt4/

如果你开始使用CSS样式表来为你的表添加样式，而不是手动为每个元素添加样式，你的生活会容易得多。

编辑:我整理了一下表格，并在这里添加了一些CSS样式:http://jsfiddle.net/yfbedzt4/1/

drupal使用函数theme_table向表输出数据

手动呈现表格不是标准的drupal技术。

这是另一篇关于如何工作的文章:http://zgadzaj.com/drupal-for-beginners-how-to-create-a-table-using-themetable

如果您正在寻找正确的"Drupal方式"，请查看theme_table()。此函数允许您指定要对哪些列进行排序。

此外，您应该使用db_query()或db_select()来构建您的SQL查询。

下面是一个关于如何正确构建查询并在HTML表上呈现其结果的好例子:http://www.rahulsingla.com/blog/2011/05/drupal-7-creating-drupal-style-tables-with-paging-sorting-and-sticky-headers

在@Kez的帮助下，我最终找到了一个解决方案。@Kez帮助我解决了第三方在那里的一些问题，但最终为了对表进行排序，我不得不在表后添加以下代码<script type="text/javascript"> var st1 = new SortableTable(document.getElementById("scheduletable")); </script>