PHP按域名排序数据库记录的技术实现求助

Hey there! Let's tackle this problem step by step. You want to create buttons that filter and sort users by their email domain (like Gmail, Yahoo), and you're stuck on linking the buttons to the right logic. Let's break down what's wrong with your current code and fix it.

First, Let's Identify the Issues in Your Current Code #

• You're generating submit buttons inside your table rows, which breaks HTML structure and doesn't properly pass the domain filter to your backend.
• You're not collecting unique domains, so you'll get duplicate buttons for every user with the same domain.
• The filter logic isn't connected to your database query—right now, clicking those buttons does nothing to filter records.
• Your table has mismatched <td> elements and incomplete checkbox HTML, which can cause layout bugs.

Step-by-Step Solution #

Here's a revised version of your code with working domain filter buttons, plus fixes for the other issues:

1. Updated PHP Logic (Top Section)

We'll add domain filter handling, collect unique domains for buttons, and build a dynamic query that respects both filters and sorting.

<!DOCTYPE html>
<html>
<head>
<title>Display all records from Database</title>
</head>
<body>
<?php
include "php/server.php"; // Database connection
include "php/search.php"; // Search functionality

// Sort configuration
$validSortOptions = ['email', 'date'];
$currentSort = isset($_GET['orderBy']) && in_array($_GET['orderBy'], $validSortOptions) 
    ? $_GET['orderBy'] 
    : 'date'; // Default sort by date

// Domain filter configuration
$currentDomain = isset($_GET['filterDomain']) 
    ? mysqli_real_escape_string($db, $_GET['filterDomain']) 
    : ''; // Empty = show all

// Build database query
$query = "SELECT * FROM users";
// Add domain filter if specified
if (!empty($currentDomain)) {
    $query .= " WHERE email LIKE '%@{$currentDomain}'";
}
// Add sort order
$query .= " ORDER BY {$currentSort}";

// Handle search submission
if (isset($_POST["submit"])) {
    $records = mysqli_query($db, $searchquery);
} else {
    $records = mysqli_query($db, $query);
}

// Collect unique domains for buttons (avoid duplicates)
$uniqueDomains = [];
if ($records && mysqli_num_rows($records) > 0) {
    while ($data = mysqli_fetch_array($records)) {
        if (strpos($data['email'], '@') !== false) { // Skip invalid emails
            $parts = explode('@', $data['email']);
            $domain = end($parts);
            $uniqueDomains[$domain] = $domain; // Use associative array to auto-deduplicate
        }
    }
    mysqli_data_seek($records, 0); // Reset pointer to re-fetch for table
}
?>

2. Updated HTML & Buttons

We'll add domain filter buttons above the table, fix the table structure, and ensure filter/sort settings persist when switching between buttons.

<h2>All emails</h2>

<!-- Domain Filter Buttons -->
<div style="margin: 10px 0;">
    <a href="?filterDomain=&orderBy=<?php echo urlencode($currentSort); ?>">
        <button>Show All Users</button>
    </a>
    <?php foreach ($uniqueDomains as $domain): ?>
        <a href="?filterDomain=<?php echo urlencode($domain); ?>&orderBy=<?php echo urlencode($currentSort); ?>">
            <button>Filter by <?php echo htmlspecialchars($domain); ?></button>
        </a>
    <?php endforeach; ?>
</div>

<!-- Sort Buttons (with filter persistence) -->
<div style="margin: 10px 0;">
    <a href="?orderBy=email&filterDomain=<?php echo urlencode($currentDomain); ?>">
        <button>Sort By E-mail</button>
    </a>
    <a href="?orderBy=date&filterDomain=<?php echo urlencode($currentDomain); ?>">
        <button>Sort By Date</button>
    </a>
    <a href="?orderBy=">
        <button>Export as CSV</button>
    </a>
</div>

<!-- Fixed Table Structure -->
<table border="2">
    <tr>
        <td>Sr.No.</td>
        <td>E-mail</td>
        <td>Date</td>
        <td>Domain</td>
        <td>Delete</td>
        <td>CSV</td>
    </tr>
    <?php $serialNumber = 1; ?>
    <?php if ($records && mysqli_num_rows($records) > 0): ?>
        <?php while($data = mysqli_fetch_array($records)): ?>
            <?php
            $domain = 'N/A';
            if (strpos($data['email'], '@') !== false) {
                $parts = explode('@', $data['email']);
                $domain = end($parts);
            }
            ?>
            <tr>
                <td><?php echo $serialNumber++; ?></td>
                <td><?php echo htmlspecialchars($data['email']); ?></td>
                <td><?php echo htmlspecialchars($data['date']); ?></td>
                <td><?php echo htmlspecialchars($domain); ?></td>
                <td><a href="php/delete.php?id=<?php echo $data['id']; ?>">Delete</a></td>
                <td><input type="checkbox" id="csv-<?php echo $data['id']; ?>" name="csv[]" value="<?php echo $data['id']; ?>"></td>
            </tr>
        <?php endwhile; ?>
    <?php else: ?>
        <tr>
            <td colspan="6">No records found.</td>
        </tr>
    <?php endif; ?>
</table>

<h3>Current View: 
    <?php if (!empty($currentDomain)): ?>
        Filtered by <?php echo htmlspecialchars($currentDomain); ?> & 
    <?php endif; ?>
    Sorted by <?php echo $currentSort; ?>
</h3>

</body>
</html>

Key Improvements Explained #

• Unique Domain Buttons: We use an associative array to collect only unique domains, so you won't see duplicate buttons for the same domain.
• Filter + Sort Persistence: When you click a domain button, it keeps your current sort order; when you click a sort button, it keeps your current domain filter. No more losing your place!
• Security: We use mysqli_real_escape_string to prevent SQL injection, urlencode for safe URL parameters, and htmlspecialchars to prevent XSS attacks when displaying user data.
• Robust Email Handling: We add a check for valid emails (with an @ symbol) to avoid errors if any records have malformed email addresses.
• Cleaner HTML: Fixed table column alignment, completed checkbox HTML, and moved buttons outside the table for better structure.

Quick Notes for Next Steps #

• For your CSV export button, you'll want to update it to respect the current domain filter and sort order—right now it ignores those settings.
• If you have a lot of domains, you might want to style the buttons to wrap into a new line instead of overflowing.

内容的提问来源于stack exchange，提问作者Natrien