可修复.php

<?php

// Connect to the database
$db = new mysqli('localhost', 'username', 'password', 'repair_shop');
if ($db->connect_error) {
  die("Connection failed: " . $db->connect_error);
}

// Get the search input from the form
$search = isset($_GET['search']) ? $_GET['search'] : '';

// Get the sorting parameters from the query string
$sort = isset($_GET['sort']) && !empty($_GET['sort']) ? $_GET['sort'] : 'work_orders.id';
$order = isset($_GET['order']) && in_array($_GET['order'], array('asc', 'desc')) ? $_GET['order'] : 'asc';
$status = isset($_GET['status']) && !empty($_GET['status']) ? $_GET['status'] : '';



$where = "WHERE customers.name LIKE '%$search%' OR devices.type LIKE '%$search%' OR devices.issues LIKE '%$search%'";
if ($status) {
  $where .= " AND work_orders.status='$status'";
} else {
  $where .= " AND work_orders.status IN ('Awaiting Repair', 'Repair In Progress', 'Ready For Pickup', 'Repair Complete', 'Sale Complete')";
}

$query = "SELECT work_orders.id, devices.type AS device_name, customers.name, work_orders.status, devices.service_request_id, devices.issues
          FROM work_orders
          INNER JOIN devices ON work_orders.device_id = devices.id
          INNER JOIN customers ON devices.customer_id = customers.id
          $where
          ORDER BY work_orders.status $order
          LIMIT 10 OFFSET 0";

echo("status: " . $status);


$result = $db->query($query);

// Check for errors
if ($db->error) {
  die("Query failed: " . $conn->error);
}

// Check if there are any results
if ($result->num_rows > 0) {?>
<table class='table table-striped'>
<tr class='thead-dark'>
  <th>
    <a href="#" onclick="loadRepairs('id', '<?php echo !empty($sort) && !empty($order) ? ($sort == 'id' && $order == 'asc') ? 'desc' : 'asc' : ''; ?>'); return false;">ID</a>
  </th>
  <th>
    <a href="#" onclick="loadRepairs('device_name', '<?php echo !empty($sort) && !empty($order) ? ($sort == 'device_name' && $order == 'asc') ? 'desc' : 'asc' : ''; ?>'); return false;">Device Name</a>
  </th>
  <th>
    <a href="#" onclick="loadRepairs('name', '<?php echo !empty($sort) && !empty($order) ? ($sort == 'name' && $order == 'asc') ? 'desc' : 'asc' : ''; ?>'); return false;">Customer Name</a>
  </th>
  <th>
<form method="get" id="form" action="#">
  <select id="status" name="status" onchange="loadRepairs('<?php echo $sort; ?>', '<?php echo $order; ?>');">
        <option value="">Status</option>
        <option value='Awaiting Repair'>Awaiting Repair</option>
        <option value='Repair In Progress'>Repair In Progress</option>
        <option value='Ready For Pickup'>Ready For Pickup</option>
        <option value='Repair Complete'>Repair Complete</option>
        <option value='Sale Complete'>Sale Complete</option>
      </select>
    </form>
      </th>
      <th>
  <a href="#" onclick="loadRepairs('service_request_id', '<?php echo !empty($sort) && !empty($order) ? ($sort == 'service_request_id' && $order == 'asc') ? 'desc' : 'asc' : ''; ?>'); return false;">Service Request ID</a>
</th>

      <th>issues</th>
      <th>View/Edit</th>
    </tr>
  <?php
  

  // Print the table rows
  while ($row = $result->fetch_assoc()) {
    echo "<tr>";
    echo "<td class='align-middle'>" . htmlspecialchars($row['id']) . "</td>";
    echo "<td class='align-middle'>" . htmlspecialchars($row['device_name']) . "</td>";
    echo "<td class='align-middle'>" . htmlspecialchars($row['name']) . "</td>";
    echo "<td class='align-middle'>" . htmlspecialchars($row['status']) . "</td>";
    echo "<td class='align-middle'>" . htmlspecialchars($row['service_request_id']) . "</td>";
    echo "<td class='align-middle'>" . htmlspecialchars($row['issues']) . "</td>";
    echo "<td class='align-middle'><button class='btn btn-primary' data-toggle='modal' data-target='#repairModal' data-id='" . $row['id'] . "' data-device-name='" . $row['device_name'] . "' data-customer-name='" . $row['name'] . "' data-service-request-id='" . $row['service_request_id'] . "' data-status='" . $row['status'] . "' data-issues='" . $row['issues'] . "'>View/Edit</button></td>";
    echo "</tr>";
  }
  // Close the table
  ?>
  
  </table>
  <?php
// Close the database connection
$db->close();
}
?>

加载修复功能：

function loadRepairs(sort = 'id', order = 'asc') {
    // Get the selected status value
    var status = $('#status').val();
    console.log(status);
    // Load the repairtable.php file into the repairsTable element,
    // and include the selected status value, as well as the sort and order values,
    // in the query string
    $.get(`includes/repairtable.php?status=${status}&sort=${sort}&order=${order}`, function(response) {
      $('#repairsTable').html(response);
    });
  }

具有选定状态的查询：

SELECT work_orders.id, devices.type AS device_name, customers.name, work_orders.status, devices.service_request_id, devices.issues FROM work_orders INNER JOIN devices ON work_orders.device_id = devices.id INNER JOIN customers ON devices.customer_id = customers.id WHERE customers.name LIKE '%%' OR devices.type LIKE '%%' OR devices.issues LIKE '%%' AND work_orders.status='Awaiting Repair' ORDER BY work_orders.status asc LIMIT 10 OFFSET 0

因此，这将生成一个带有标题的表，标题是用于对表进行排序的链接，除了状态，它是一个下拉列表，用于过滤表以仅显示选定的状态。
麻烦的是，没有排序和过滤似乎工作的预期。当我点击一个表格标题，它会按升序或降序排序，但它有时有随机混合的结果，而不是在所需的顺序，像21出现在20之前，另外，当我用状态下拉列表过滤结果，它只是不过滤。
我猜我的sql查询构造得很差，因为那是我最薄弱的地方，但我真的不确定了。我已经调试了链中的每一个环节，但我似乎找不到bug。我真的希望别人能找到。
如果需要更多的细节，请告诉我。任何帮助都是感激的。
感谢大家提醒我SQL注入，你们是100%正确的，但我也设计了这个要使用总计3人，他们都是我工作的商店的员工，所以我不是超级担心攻击向量，但谢谢你照顾我.