php - Sortable HTML table using MYSQL query in Drupal -
ok going try make question clear can. started using drupal , third party built out part of our site us. have views file queries our mysql database , pulls courses table match particular search criteria. example fall 2014 classes. have ordered cid or course id. views page includes schedules results php page renders information table. want able have sortable columns in table. have tried adding jscript file without reloading table info hasn't worked , not sure why. have tried using drupal_add_js inserting js inline. have tried doing href='/shedules?orderby=cname" doesn't seem keep data query views page. getting highly frustrated , don't know else look. guidance can give me appreciated!
here views page
<!-- <pre> <? print_r($_post); ?> </pre> --> <?php $query = " select * "; $semester = $_post['semester']; if(empty($_post['text'])){ if($_post['currentcourse'] == "1"){ $query .= " $semester (sdate <= now() or sdate null)"; } else { $query .= " $semester (1=1)"; } } else { //$query .= " $semester (1=1)"; $query .= " $semester (sdate >= now() or sdate null)"; } if( isset( $_post['online'] ) && 'on' == $_post['online'] ) $query .= " , ( `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 $w ){ $part = array(); foreach( $fields $f ){ $part[] = " $f '%$w%' "; } $q[] = $part; } $final_parts = array(); foreach( $q $query_part ) $final_parts[] = implode( ' or ', $query_part); $final_query = ' , (' . implode(') , (', $final_parts) . ')'; }else{ // single word search $part = array(); foreach( $fields $f ){ $part[] = " $f '%$_post[text]%' "; } $final_query = ' , ('.implode( ' or ', $part).')'; } $query .= " " . $final_query . " "; } if( isset( $_post['area_id'] ) && '-1' != $_post['area_id'] ) $query .= " , cid '" . $_post['area_id'] . "%'"; if( isset( $_post['division_id'] ) && '-1' != $_post['division_id'] ) $query .= " , division = '" . $_post['division_id'] . "'"; if( isset( $_post['duration'] ) && '-1' != $_post['duration'] ) $query .= " , duration_min " . ( ( $_post['durcomp'] == 'more' ) ? ' >= ' : ' <= ' ) . $_post['duration'] . " "; $query .= " , ( 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 .= " , 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 .= " , ( 1=1 "; if( isset( $_post['t'] ) && 'on' == $_post['t'] ) $query .= ( $_post['dayop'] == 'and' ) ? ' , ' : ' or ' . " day_t = 1 "; if( isset( $_post['w'] ) && 'on' == $_post['w'] ) $query .= ( $_post['dayop'] == 'and' ) ? ' , ' : ' or ' . " day_w = 1 "; if( isset( $_post['r'] ) && 'on' == $_post['r'] ) $query .= ( $_post['dayop'] == 'and' ) ? ' , ' : ' or ' . " day_r = 1 "; if( isset( $_post['f'] ) && 'on' == $_post['f'] ) $query .= ( $_post['dayop'] == 'and' ) ? ' , ' : ' or ' . " day_f = 1 "; if( isset( $_post['s'] ) && 'on' == $_post['s'] ) $query .= ( $_post['dayop'] == 'and' ) ? ' , ' : ' or ' . " day_s = 1 "; $query .= " ) order cid"; /* echo $query; */ $string_query = $query; $query = db_query($query); $result = $query->fetchall(); $in_progress = 0; foreach ($result $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 results page 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 $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 table output example: styling done dynamically based on queried results view source after executing 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>
okay able sort table using sortable.js mentioned earlier. loading script , setting table class "sortable".
i.e.:
<table id="scheduletable" class="sortable"> here jsfiddle example: http://jsfiddle.net/yfbedzt4/
also life lot easier if start using css style sheets style table rather adding style every element manually.
edit: have tidied table bit , added css styling here: http://jsfiddle.net/yfbedzt4/1/
Comments
Post a Comment