动态php表过滤和排序

xjreopfe  于 2021-06-24  发布在  Mysql
关注(0)|答案(6)|浏览(260)

我已经编写了一些css和php来查询mysql表。我还有一个下拉框形式的过滤器，允许用户选择一个“系列”，无论是“电容器”、“电阻器”还是“铁氧体磁珠”（我已经在下面附上了这看起来像什么的图片）。
我的问题是：一旦元素按族过滤，如何为它们创建排序系统？也就是说，如果我想从mysql查询对应于asc值“voltage”的表，我该怎么做？当选择排序方法时，我需要保留过滤器。到目前为止，我已经在图片下面包含了我的代码。谢谢你的帮助！
（下面：1，加载整个表：2，只加载与“capacitor”匹配的族条目）


代码：（文件名，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>
mysqlFilterphpsortingcss

6条答案

按热度按时间
wsxa1bj1

wsxa1bj11#

如果您不想使用专用的表排序库，您应该可以自己完成。下面是一个从提供的数据数组中提取所有数据的解决方案，您应该能够使用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>
赞(0）分享  回复(0）举报  2021-06-24
yquaqz18

yquaqz182#

以下是您如何对表格进行数字排序：
1） 给目标表一个id（在我的代码中是主表）
2） 每次单击表头时调用排序函数（包括列号，第一个是0，每次添加更多列时，函数名内的数字增加1，在本例中为sorttable（0），sorttable（1），。。。。
最后的结果是这样的（测试这个例子，它是有效的）：

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

下面是生成列号所需的操作：

$counter = 0;
foreach ($row as $field => $value){
print " <th onclick='sortTable($counter)'>$field</th>";
$counter = $counter+1;
}
赞(0）分享  回复(0）举报  2021-06-24
tgabmvqs

tgabmvqs3#

数据表https://datatables.net/ 也很酷。正常的功能是使用javascript，但是您可以将其配置为使用服务器资源，在服务器上处理日期，并只显示结果。一旦你掌握了窍门就很容易了。
每次对数据进行排序或筛选时，datatable都会发送一个包含所有必要信息的数组，这样您只需扫描该数组并相应地生成查询。

赞(0）分享  回复(0）举报  2021-06-24
a1o7rhls

a1o7rhls4#

// 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>
赞(0）分享  回复(0）举报  2021-06-24
nfzehxib

nfzehxib5#

您可以在表单中添加排序下拉列表，并在查询中使用它。通过这种方式，您可以让用户选择排序方法并在服务器端进行处理。

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

在php中：

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

在您的if声明中：

if(!empty($family)) {
        $query = 'SELECT * FROM testv2 WHERE family = "'.$family.'" ORDER BY "'.$sort'"';
         }
         else {
        $query = "SELECT * FROM testv2";
         }
赞(0）分享  回复(0）举报  2021-06-24
f45qwnt8

f45qwnt86#

您可以将$\u post['family']保存在一个隐藏字段中（可能是$\u post['hidden\u family']）。当您进行下一级搜索时，您可以检查它，如果它不是空的，则每次都将其附加到您的搜索中。

赞(0）分享  回复(0）举报  2021-06-24
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com