Dynamic PHP Table Filter and Sort

php css mysql sorting filter

18,051

Solution 1

You can add a sorting dropdown list in your form and use it in your query. This way you can let the user choose a sorting method and handle it server-side.

<form action="index.php" method="post">
      <select name="family">
         <option value="" selected="selected">Any family</option>
         <option value="capacitor">capacitor</option>
         <option value="resistor">resistor</option>
         <option value="ferrite bead">ferrite bead</option>
      </select>
      <select name="sort">
         <option value="" selected="selected">Any Order</option>
         <option value="ASC">Ascending</option>
         <option value="DESC">Descending</option>
      </select>
      <input name="search" type="submit" value="Search"/>
   </form>

In PHP:

<?php
      $family = "";
      $sort = "";
      if(isset($_POST['family'])) {
         $family = $_POST['family'];
      }

In your if Statement:

if(!empty($family)) {
        $query = 'SELECT * FROM testv2 WHERE family = "'.$family.'" ORDER BY "'.$sort'"';
         }
         else {
        $query = "SELECT * FROM testv2";
         }

Solution 2

Datatables https://datatables.net/ is really cool too. The normal functioning is using JavaScript, but you can configure it to use server resources and process the date on server and just display the results. Once you get the hang of it it's pretty easy.

Each time you either order or filter the data, datatable sends an array with all the info necessary so you just scan the array and generate your queries accordingly.

Solution 3

If you don't want to use a dedicated table sorting library, you should be able to do this yourself. Here is a solution that pulls all data from a provided array of data which you should be able to provide easily using PHP.

// Initially populate the table
populateTable(data);

// Listen for a click on a sort button
$('.sort').on('click', function() {
  // Get the key based on the value of the button
  var key = $(this).html();
  // Sort the data and update our data
  data = sortBy(data, key);
  // Fill the table with our data
  populateTable(data);
});

// Modified from: https://www.sitepoint.com/sort-array-index/
function sortBy(inputData, key) {
  // Sort our data based on the given key
  inputData.sort(function(a, b) {
    var aVal = a[key],
      bVal = b[key];
    if (aVal == bVal) return 0;
    return aVal > bVal ? 1 : -1;
  });
  
  return inputData;
}

// Modified from: https://stackoverflow.com/questions/5361810/fast-way-to-dynamically-fill-table-with-data-from-json-in-javascript
function populateTable(inputData) {
  var keys = new Array(),
    i = -1;

  // Create an array of keys
  $.each(inputData[0], function(key, value) {
    keys[++i] = key;
  });

  var r = new Array(),
    j = -1;

  // Populate the table headers
  r[++j] = '<tr>';
  $.each(keys, function(key, value) {
    r[++j] = '<th>' + keys[key] + '</th>';
  });
  r[++j] = '</tr>';

  for (var index = 0, size = inputData.length; index < size; index++) {
    // Populate the table values
    r[++j] = '<tr>';
    $.each(keys, function(key, value) {
      r[++j] = '<td>' + inputData[index][value] + '</td>';
    });
    r[++j] = '</tr>';
  }

  // Join everything together
  $('#data-table').html(r.join(''));
}

table {
  border-collapse: collapse;
  width: 100%;
}

th,
td {
  text-align: left;
  padding: 8px;
}

th {
  background-color: skyblue;
}

tr:nth-child(odd) {
  background-color: #f2f2f2;
}

tr:hover {
  background-color: aliceblue;
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>

<script>
  // Set our data
  var data = [{
      ID: 1,
      Family: 'resistor',
      Capacitance: 7,
      Voltage: 6,
      Price: 25.6
    },
    {
      ID: 2,
      Family: 'capacitor',
      Capacitance: 10,
      Voltage: 10,
      Price: 100.2
    },
    {
      ID: 3,
      Family: 'ferrite bead',
      Capacitance: 1,
      Voltage: 5,
      Price: 35.6
    },
    {
      ID: 4,
      Family: 'resistor',
      Capacitance: 1,
      Voltage: 4,
      Price: 35.6
    },
    {
      ID: 5,
      Family: 'capacitor',
      Capacitance: 9,
      Voltage: 4,
      Price: 25.6
    }
  ];
</script>


<table id="data-table"></table>

<p>Sort by:</p>
<button class="sort">ID</button>
<button class="sort">Family</button>
<button class="sort">Capacitance</button>
<button class="sort">Voltage</button>
<button class="sort">Price</button>

18,051

Author by

Jonny1998

Updated on June 04, 2022

Comments

Jonny1998 almost 2 years

I've written a bit of CSS and PHP to query a MySQL table. I also have a filter in the form of a drop down box which allows the user to choose a 'family', whether it be "capacitor", "resistor", or "ferrite bead" (I've included pictures below of what this looks like).

My question is this: how can I create a sorting system for the elements once they have been filtered by family? That is, if I wanted to query the table from MySQL corresponding to ASC values of "voltage" for example, how would I go about this? I need to retain the filter when the sorting method is selected. I have included my code so far below the images. Thanks for the help!

(Below: 1, full table is loaded : 2, only family entries that match "capacitor" are loaded)

CODE: (File name, index.php)

<html>
   <form action="index.php" method="post">
      <select name="family">
         <option value="" selected="selected">Any family</option>
         <option value="capacitor">capacitor</option>
         <option value="resistor">resistor</option>
         <option value="ferrite bead">ferrite bead</option>
      </select>
      <input name="search" type="submit" value="Search"/>
   </form>
   <head>
      <meta charset = "UTF-8">
      <title>test.php</title>
         <style>
            table {
            border-collapse: collapse;
            width: 50%;
            }
            th, td {
            input: "text";
            text-align: left;
            padding: 8px;
            }
            th {
            background-color: SkyBlue;
            }
            tr:nth-child(odd) {background-color: #f2f2f2;}
            tr:hover {background-color: AliceBlue;} 
         </style>
   </head>

<body>
   <p>
   <?php
      $family = "";
      if(isset($_POST['family'])) {
         $family = $_POST['family'];
      }

      try {
         $con= new PDO('mysql:host=localhost;dbname=mysql', "root", "kelly188");
         $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

         if(!empty($family)) {
        $query = 'SELECT * FROM testv2 WHERE family = "'.$family.'"';
         }
         else {
        $query = "SELECT * FROM testv2";
         }

         //first pass just gets the column names
         print "<table>";
         $result = $con->query($query);

         //return only the first row (we only need field names)
         $row = $result->fetch(PDO::FETCH_ASSOC);
         print " <tr>";
         foreach ($row as $field => $value){
        print " <th>$field</th>";
         }
         // end foreach
         print " </tr>";

         //second query gets the data
         $data = $con->query($query);
         $data->setFetchMode(PDO::FETCH_ASSOC);
         foreach($data as $row){
        print " <tr>";
        foreach ($row as $name=>$value){
           print " <td>$value</td>";
        } //end field loop
        print " </tr>";
         } //end record loop
         print "</table>";
      } catch(PDOException $e) {
      echo 'ERROR: ' . $e->getMessage();
      } // end try
   ?>
   </p>
</body>

</html>

Chase Ingebritson almost 6 years

Can you include the table that is rendered after your PHP has run as well?
Jonny1998 almost 6 years

I'm a little confused by your request. The images I've included are before and after I have run the entire script, PHP included. Before, the entire table is loaded by default. After, a selection is made (Capacitor) and the table is filtered by querying MySQL with that filter choice.
Chase Ingebritson almost 6 years

Sorry, I meant the rendered HTML of the table.
Jonny1998 almost 6 years

Well the images are screenshots of the localhost website I'm running the script on. Is this what you mean?
Chase Ingebritson almost 6 years

I've recreated the table in my answer. I had meant like the final HTML text so I didn't have to rewrite it.
tadman almost 6 years

WARNING: When using PDO you should be using prepared statements with placeholder values and supply any user data as separate arguments. In this code you have potentially severe SQL injection bugs. Never use string interpolation or concatenation and instead use prepared statements and never put $_POST, $_GET or any user data directly in your query. Refer to PHP The Right Way for general guidance and advice.

Jonny1998 almost 6 years

Haha, why hello again! Your approach looks great, but my one trouble is that I don't have predefined column headers. They are dynamically loaded with the rest of the table as a single row. I can't tell each of them to individually run the sortable(column) function.
Jonny1998 almost 6 years

Thanks for the suggestion. I think I tried this method before, and because the $_POST method can only curry one selection per script run, it will unfortunately get rid of the users filter selection and just load the entire table again when the sort choice is selected...
AraByte almost 6 years

Just put in the footer of page, just before </body>
Jonny1998 almost 6 years

Unfortunately, the mouse doesn't recognize the headers as valid event fields
AraByte almost 6 years

That's just visual, you can add : <th style="cursor:pointer" (just like the example) to make it looks like a clickable button :)
Jonny1998 almost 6 years

I'm getting a few errors now, mind if we start a chat?
AraByte almost 6 years

Let us continue this discussion in chat.
BlackMarker almost 6 years

@Jonny1998 I'm more of a C# developer. Doing this with Datasets in C# is a very powerful solution as it gives you more control. Unfortunately, PHP does not have dataset struct. However, using arrays in PHP, you can almost do similar things. Of course its a learning curve but hey we learn everyday
Jonny1998 almost 6 years

Wow, thanks for the details! I will be trying this soon :)
Chase Ingebritson almost 6 years

No problem! It was a nice refresher into sorting for myself as well. If you need any explanations or suggestions feel free to comment.
tadman almost 6 years

This is crying out for some loops, not just rampant copy-pasting.