请求将指定PHP查询代码转换为jQuery/AJAX函数并指导调用方式

First off, let's address a critical issue with your original PHP code: it's vulnerable to SQL injection. We'll fix that first, then create a clean jQuery/AJAX function to fetch the autocomplete data, and integrate it into your existing code.

Step 1: Secure Your PHP Endpoint #

Replace your current PHP with this version that uses prepared statements to block SQL injection, plus basic error handling:

<?php
// Assuming your DB constants (DB_HOST, DB_USER, etc.) are already defined
$db = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

// Handle database connection errors
if ($db->connect_error) {
    die(json_encode(['error' => 'Database connection failed: ' . $db->connect_error]));
}

$searchTerm = isset($_GET['term']) ? trim($_GET['term']) : '';
$results = [];

if (!empty($searchTerm)) {
    // Use a prepared statement to safely insert the search term into the query
    $stmt = $db->prepare("SELECT name FROM ncic_names WHERE name LIKE ? ORDER BY name ASC");
    $likePattern = "%{$searchTerm}%";
    $stmt->bind_param("s", $likePattern);
    $stmt->execute();
    $resultSet = $stmt->get_result();
    
    while ($row = $resultSet->fetch_assoc()) {
        $results[] = $row['name'];
    }
    
    $stmt->close();
}

$db->close();
echo json_encode($results);
?>

Step 2: Create a Reusable AJAX Function #

This function encapsulates the AJAX logic, making it easy to fetch autocomplete data with success and error handling:

function fetchAutocompleteNames(searchTerm, onSuccess, onError) {
    $.ajax({
        url: 'search.php', // Ensure this path points to your updated PHP file
        method: 'GET',
        data: { term: searchTerm },
        dataType: 'json',
        success: function(response) {
            // Handle server-side errors returned from PHP
            if (response.error) {
                onError?.(response.error);
                return;
            }
            onSuccess(response);
        },
        error: function(xhr, status, err) {
            onError?.(`Failed to fetch names: ${err}`);
        }
    });
}

Step 3: Integrate with jQuery UI Autocomplete #

Instead of using a direct URL as the source, we'll use a function that calls our AJAX helper. The autocomplete widget expects us to pass results back via its built-in response callback:

$(function() {
    $("#ncic_name").autocomplete({
        source: function(request, response) {
            fetchAutocompleteNames(
                request.term,
                // Success: send the fetched names to the autocomplete widget
                function(names) {
                    response(names);
                },
                // Error: log the issue and send an empty array to keep the widget functional
                function(error) {
                    console.error(error);
                    response([]);
                }
            );
        },
        minLength: 2 // Optional: Only trigger search after 2 characters are typed (reduces server load)
    });
});

Key Notes #

• The prepared statement in PHP is non-negotiable—it eliminates the risk of SQL injection attacks targeting your search feature.
• The reusable AJAX function lets you call this search logic elsewhere in your code if needed, without repeating code.
• Error handling ensures your autocomplete doesn't break if there's a server or network issue.

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