PHP output mySQL results into sortable columns

Question

My app executes a query that returns it's results to a listview. The results are currently displayed in a single column, with a variable amount of rows depending on the query results. The single column displays a patient names and a birth dates for each patient.

I want to make my results display in two columns the first will contain names and the second birth dates. I also need to allow users to sort the result rows by either column. I've tried incorporating tables into my code and rearranging the set up of my <UL> but I can't get anything to work.

Here is my query/row output code:

if(isset($_POST['dt']) && $_POST['dt'] != '')
{
  $dts = $_POST['dt'];
  $dts = mysql_real_escape_string($dts);
  $edit_date = str_replace("/", "-", $dts);
  $edit_date = explode(" ", $edit_date);
  $edit_date = explode("-", $edit_date[0]);
  $string = $edit_date[2] . "-" . $edit_date[0] . "-" . $edit_date[1];
  $query = "select * from VISIT JOIN PATIENT ON VISIT.PATIENT_ID=PATIENT.ID where VISIT.VISIT_DATE like '%".$string."%' ORDER BY PATIENT.LAST_NAME;";

  $res = mysql_query($query);
  $count = mysql_num_rows($res);
  $i = 0;
  if($count > 0)
  {
    $previous_letter = '';
    while($row = mysql_fetch_array($res))
    {
    $id = $row['ID'];
    $letter = strtoupper(substr($row['LAST_NAME'],0,1));
    echo "<li data-theme=\"c\" id=\"patient_name\">\n";
    echo "<a href=\"deeschdeesch.php?id=" . $id . "&fname=" . $row['FIRST_NAME'] . "&lname=" . $row['LAST_NAME'] . "\" rel=\"external\">\n";
    $date = $row['BIRTH_DATE']; 
    $date = explode(" ", $date);
    $date = explode("-", $date[0]);
    echo ucwords(strtolower($row['FIRST_NAME'] . " " . $row['LAST_NAME'])) . " - " . $date[1] . "-" . $date[2] . "-" . $date[0];
    echo "</a>\n";
    echo "</li>\n";
    $i++;
    }
  }
  else
  {
    $edit_date = str_replace("/", "-", $dts);
    $edit_date = explode(" ", $edit_date);
    $edit_date = explode("-", $edit_date[0]);
    $string = $edit_date[2] . "-" . $edit_date[0] . "-" . $edit_date[1];
    echo "<div id='no_result'>There were " . $count . " results found, but the input was " . $string . "</div>";
  }
}

Results are output here:

<div data-role="content" style="padding: 15px">
    <ul class="ui-body ui-body-d ui-corner-all" id="results" data-role="listview" data-divider-theme="b" data-filter-theme="a" data-inset="true" data-filter="false">
    </ul>
</div>

JS involved:

$(function(){
  $('#datepicker').datepicker({
      inline: true,
      showOn: "button",
      buttonImage: "images/calendar.gif",
      showAnim: "slideDown",
      changeMonth: true,
      showOtherMonths: true,
      selectOtherMonths: true,
      onSelect: function(dateText, inst) {
      var dt = dateText;

      if(dt != '')
       {
        $.ajax
        ({
        type: "POST",
        url: "search_date.php",
        data: "dt="+ dt,
        success: function(option)
        {
          $("#results").html(option).listview("refresh");
        }
        });
       }
       else
       {
         $("#results").html("");
       }
      return false;
      }
  });
});

Can anyone point me in the right direction on how to separate my results into multiple columns and/or make them sortable? Please and thank you all.

I strongly recommend not to use the old deprecated mysql_* functions and I'd suggest switching to mysqli_* extension or PDO. Here's why: http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — pilsetnieks, Apr 16 '13 at 14:52

score 2 · Accepted Answer · answered Apr 16 '13 at 21:11

2

There are many javascript plugins out there to fulfil the result you want. Take a look at these two:

tablesorter - tablesorter is a jQuery plugin for turning a standard HTML table with THEAD and TBODY tags into a sortable table without page refreshes;
DataTables - DataTables is a plug-in for the jQuery Javascript library. It is a highly flexible tool, based upon the foundations of progressive enhancement, which will add advanced interaction controls to any HTML table.

answered Apr 16 '13 at 21:11

Rolando Isidoro

4,983
2
31
43

Thank you Rolando. Made my day... or at least my morning. I went with `tablesorter` Your link didn't work, but I found it here: [tableSorter](http://mottie.github.io/tablesorter/docs/) This one had better documentation compared to `DataTables`, IMO – reubenCanowski Apr 17 '13 at 13:50

PHP output mySQL results into sortable columns

1 Answers1