Complete Implementation Guide for Retrieving Data from MySQL Database Using jQuery Ajax

Introduction

In modern web development, dynamic data retrieval and display are common requirements. jQuery Ajax technology combined with PHP backend and MySQL database provides an efficient data interaction solution. Based on practical development cases, this article deeply analyzes common implementation issues and provides complete solutions.

Problem Analysis

The original code has several key issues: first, it uses deprecated mysql_* functions which pose security risks; second, the Ajax request uses synchronous mode, affecting user experience; finally, the data return format is unclear, causing difficulties in frontend processing.

Improved Solution

Frontend Implementation

Using jQuery's $.ajax method for asynchronous data requests:

$(document).ready(function() {
    $("#display").click(function() {
        $.ajax({
            type: "GET",
            url: "display.php",
            dataType: "html",
            success: function(response) {
                $("#responsecontainer").html(response);
            }
        });
    });
});

This implementation avoids blocking the user interface and triggers data requests through event-driven approach.

Backend Data Processing

Using mysqli extension for database operations:

<?php
$con = mysqli_connect("localhost", "root", "");
mysqli_select_db($con, "samples");
$result = mysqli_query($con, "select * from student");

echo "<table border='1' >
<tr>
<td align=center> <b>Roll No</b></td>
<td align=center><b>Name</b></td>
<td align=center><b>Address</b></td>
<td align=center><b>Stream</b></td>
<td align=center><b>Status</b></td></tr>";

while($data = mysqli_fetch_row($result)) {
    echo "<tr>";
    echo "<td align=center>$data[0]</td>";
    echo "<td align=center>$data[1]</td>";
    echo "<td align=center>$data[2]</td>";
    echo "<td align=center>$data[3]</td>";
    echo "<td align=center>$data[4]</td>";
    echo "</tr>";
}
echo "</table>";
?>

Data Format Optimization

Referring to supplementary materials, JSON format provides more flexible data processing. Using json_encode in PHP to convert data to JSON format:

<?php
$stmt = $link->prepare("SELECT `user_name` FROM `users` ORDER BY `user_name` DESC");
$stmt->execute();
$result = $stmt->get_result();

$return_array = array();
while($row = $result->fetch_assoc()) {
    $return_array[] = array("user_name" => $row['user_name']);
}

echo json_encode($return_array);
?>

Adjusting data reception and processing logic on the frontend accordingly:

success: function(data) {
    $.each(data, function(i, item) {
        $("#phpcontent").append(
            $("<li/>").text(item.user_name)
        );
    });
}

Performance Considerations

For simple data display, server-side HTML rendering might be more efficient. Generating complete HTML structure directly in PHP, with frontend only needing simple insertion:

success: function(data) {
    $("#phpcontent").html(data);
}

This approach reduces frontend processing burden, especially suitable for static or simple dynamic content display.

Security Recommendations

In actual deployment, consider the following security measures: using prepared statements to prevent SQL injection, validating user input, implementing appropriate permission controls. Also ensure error messages don't leak sensitive system information.

Conclusion

Through reasonable architecture design and code implementation, the combination of jQuery Ajax with PHP and MySQL can provide efficient and secure data interaction solutions. Developers should choose appropriate data formats and rendering strategies based on specific requirements, balancing performance and flexibility.