Dynamic PHP Table Filter and Sort

Question

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>

Can you include the table that is rendered after your PHP has run as well? — Chase Ingebritson
– Chase Ingebritson, Commented Jun 29, 2018 at 15:52
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. — Jonny1998
– Jonny1998, Commented Jun 29, 2018 at 15:55
Well the images are screenshots of the localhost website I'm running the script on. Is this what you mean? — Jonny1998
– Jonny1998, Commented Jun 29, 2018 at 16:03
I've recreated the table in my answer. I had meant like the final HTML text so I didn't have to rewrite it. — Chase Ingebritson
– Chase Ingebritson, Commented Jun 29, 2018 at 17:06

Mihail Minkov · Accepted Answer · 2018-06-29 16:10:33Z

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.

answered Jun 29, 2018 at 16:10

Mihail Minkov

2,6632 gold badges29 silver badges50 bronze badges

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

Comments

BlackMarker · Accepted Answer · 2018-06-29 16:11:42Z

2

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";
         }

answered Jun 29, 2018 at 16:11

BlackMarker

13912 bronze badges

2 Comments

Jonny1998 Over a year ago

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

BlackMarker Over a year ago

@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

Chase Ingebritson · Accepted Answer · 2018-06-29 17:05:31Z

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>

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

Arthur Hylton · Accepted Answer · 2018-06-29 16:08:14Z

0

You could keep the $_POST['family'] in a hidden field (maybe $_POST['hidden_family']). When you do your next level search you can check for it and append that to your search each time if it is not blank.

answered Jun 29, 2018 at 16:08

Arthur Hylton

1311 silver badge7 bronze badges

Comments

AraByte · Accepted Answer · 2018-06-29 16:20:19Z

0

Here's how you numerically sort your table :
1) give the intended table an ID (in my code case it's main-table)
2) Call the sorting function everytime you click on the table headers (including the column number, first one is 0, everytime you add more columns increase the number inside the function name by one, in this case sortTable(0),sortTable(1),....
The final result will be something like this (test this example, it works):

<table id="main-table">
  <tr>
    <th style="cursor:pointer" onclick="sortTable(0)">colomn 0</th>
    <th style="cursor:pointer" onclick="sortTable(1)">colomn 1</th>
    <th style="cursor:pointer" onclick="sortTable(2)">colomn 2</th>
    <th style="cursor:pointer" onclick="sortTable(3)">colomn 3</th>
    <th style="cursor:pointer" onclick="sortTable(4)">colomn 4</th>
  </tr>
  <tr>
    <td><?php echo  rand(0,999);?></td>
    <td><?php echo  rand(0,999);?></td>
    <td><?php echo  rand(0,999);?></td>
    <td><?php echo  rand(0,999);?></td>
    <td><?php echo  rand(0,999);?></td>
  </tr>
   <tr>
    <td><?php echo  rand(0,999);?></td>
    <td><?php echo  rand(0,999);?></td>
    <td><?php echo  rand(0,999);?></td>
    <td><?php echo  rand(0,999);?></td>
    <td><?php echo  rand(0,999);?></td>
  </tr>
   <tr>
    <td><?php echo  rand(0,999);?></td>
    <td><?php echo  rand(0,999);?></td>
    <td><?php echo  rand(0,999);?></td>
    <td><?php echo  rand(0,999);?></td>
    <td><?php echo  rand(0,999);?></td>
  </tr>
   <tr>
    <td><?php echo  rand(0,999);?></td>
    <td><?php echo  rand(0,999);?></td>
    <td><?php echo  rand(0,999);?></td>
    <td><?php echo  rand(0,999);?></td>
    <td><?php echo  rand(0,999);?></td>
  </tr>
   <tr>
    <td><?php echo  rand(0,999);?></td>
    <td><?php echo  rand(0,999);?></td>
    <td><?php echo  rand(0,999);?></td>
    <td><?php echo  rand(0,999);?></td>
    <td><?php echo  rand(0,999);?></td>
  </tr>

</table>

<script>
function sortTable(column) {
  var table, rows, switching, i, x, y, shouldSwitch;
  table = document.getElementById("main-table");
  switching = true;
  while (switching) {
    switching = false;
    rows = table.getElementsByTagName("TR");
    for (i = 1; i < (rows.length - 1); i++) {
      shouldSwitch = false;
      x = rows[i].getElementsByTagName("TD")[column];
      y = rows[i + 1].getElementsByTagName("TD")[column];
      if (Number(x.innerHTML) > Number(y.innerHTML)) {
        shouldSwitch = true;
        break;
      }
    }
    if (shouldSwitch) {
      rows[i].parentNode.insertBefore(rows[i + 1], rows[i]);
      switching = true;
    }
  }
}
</script>

And here's what you need to do to generate the number of the column :

$counter = 0;
foreach ($row as $field => $value){
print " <th onclick='sortTable($counter)'>$field</th>";
$counter = $counter+1;
}

edited Jun 29, 2018 at 16:20

answered Jun 29, 2018 at 16:08

AraByte

1549 bronze badges

7 Comments

Jonny1998 Over a year ago

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.

AraByte Over a year ago

Just put in the footer of page, just before </body>

Jonny1998 Over a year ago

Unfortunately, the mouse doesn't recognize the headers as valid event fields

AraByte Over a year ago

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

Jonny1998 Over a year ago

I'm getting a few errors now, mind if we start a chat?

|

Collectives™ on Stack Overflow

Dynamic PHP Table Filter and Sort

5 Answers 5

Comments

2 Comments

2 Comments

Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

2 Comments

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related