Sortable HTML table using MYSQL query in Drupal

Question

Ok I am going to try to make this question as clear as I can. We just recently started using Drupal and a third party built out part of our site for us. We have a views file which queries our Mysql database and pulls the courses from the table that match a particular search criteria. For example all Fall 2014 classes. They have it ordered by CID or Course ID. The Views page includes the Schedules results PHP page which then renders the information into a table. We want to be able to have sortable columns in the table. I have tried adding a jscript file which will do this without reloading the table info but it hasn't worked and I am not sure why. I have tried using the Drupal_add_js as well as just inserting the JS inline. I have also tried doing the href='/shedules?orderby=cname" but that doesn't seem to keep the data from the query from the views page. I am getting highly frustrated with this and I don't know where else to look. Any guidance you can give me would be greatly appreciated!

here is the views page

 <!--
    <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');
?>

And here is the Results page that is included

<?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>

Here is the table output example: The styling is done dynamically based on queried results this is from the view source after executing a search.

<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>

Could you provide an example of what the table looks like after the PHP has parsed it and output the html? I have used the sortable plugin in the past and it requires almost no configuration whatsoever. As long as you have loaded the script and set your table class as "sortable" then it should work. — Kez
– Kez, Commented Aug 18, 2014 at 17:22
You can take as example this project: drupal.org/project/privatemsg — hugronaphor
– hugronaphor, Commented Aug 18, 2014 at 17:57
@Kez added an example of the output of the table on the results page. — Inky1231
– Inky1231, Commented Aug 18, 2014 at 18:18
@CornelAndreev what does PrivateMSg have to do with sorting table columns? — Inky1231
– Inky1231, Commented Aug 18, 2014 at 18:19

Kez · Accepted Answer · 2014-08-18 19:04:19Z

1

Okay I was able to sort your table using the sortable.js I mentioned earlier. Just by loading the script and setting the table class to "sortable".

i.e.:

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

Here is a JSFiddle example: http://jsfiddle.net/yfbedzt4/

Also your life will be a lot easier if you start using CSS style sheets to style your table rather than adding style to every element manually.

Edit: I have tidied up the table a bit and added some CSS styling here: http://jsfiddle.net/yfbedzt4/1/

edited Aug 18, 2014 at 19:04

answered Aug 18, 2014 at 18:53

Kez

7625 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Inky1231 Over a year ago

I have tried that and for some reason it does not work. I don't know if I am adding the reference to the jscript file wrong or what. I added ` drupal_add_js('/jscript/sorttable.js');` inside a php code section at the top of the page. Is that correct or am I doing it incorrectly?

Kez Over a year ago

As you are coding much of the table in html anyway, it may be easier to just use <script src="jscript/sorttable.js"></script>.

Inky1231 Over a year ago

YEs I plan on doing that but I am confused about where to place it as the pages I am working with don't have a <head> section which is where I am used to placing that info.

Inky1231 Over a year ago

Ok I put the <script src> statement above the table on the page but it is not working. the page I am working on is this: dacc.edu/schedules Just doing a search fro Fall 2014

Inky1231 Over a year ago

BTW I usually use CSS this page wasn't created by me but a third party. I don't know why they didn't use a CSS. and the styling on that particular table is created dynamically to represent different things. the styling you see there was from the view page source after doing a search :)

|

oknate · Accepted Answer · 2014-08-18 18:02:06Z

0

The drupal way to output data to a table is using function theme_table

It's not standard drupal technique to render the table manually.

Here's another article on how this works: http://zgadzaj.com/drupal-for-beginners-how-to-create-a-table-using-themetable

answered Aug 18, 2014 at 18:02

oknate

1,16810 silver badges14 bronze badges

1 Comment

Inky1231 Over a year ago

The table isn't done inside drupal it is linked to a file in the themes folder attached to the views template.

vitorsdcs · Accepted Answer · 2014-08-19 00:30:50Z

0

If you're looking for the proper "Drupal way" to do that, check out theme_table(). This function allows you to specify which columns you want to be sortable.

Also, you should be using db_query() or db_select() to build your SQL query.

Here is a good example on how to properly build a query and render its results on a HTML table: http://www.rahulsingla.com/blog/2011/05/drupal-7-creating-drupal-style-tables-with-paging-sorting-and-sticky-headers

answered Aug 19, 2014 at 0:30

vitorsdcs

6927 silver badges33 bronze badges

1 Comment

Inky1231 Over a year ago

thank you for the reply. My biggest issue is I don't want to rewrite the whole thing as we paid to have this done and if it requires complete rewriting we will have them do it. The query is done on a view-views page and includes the page that renders the table. I have found jscript functions that are "supposed" to enable sorting of the data already there but I don't know if I am putting the call to the jscript in the correct spot or not as I am not used to putting it in with out a <head> tag.

Inky1231 · Accepted Answer · 2014-08-19 14:02:34Z

0

Ok thanks to some help from @Kez I was eventually able to find a solution. @Kez helped me fix a few issues that the third party had in there but ultimately in order to sort the table I had to add the following code AFTER the table <script type="text/javascript"> var st1 = new SortableTable(document.getElementById("scheduletable")); </script>

answered Aug 19, 2014 at 14:02

Inky1231

854 silver badges16 bronze badges

Collectives™ on Stack Overflow

Sortable HTML table using MYSQL query in Drupal

4 Answers 4

7 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related