使用jQuery DateTimePicker结合PHP过滤MySQL数据失败求助

Let's fix your DateTimePicker filtering issue step by step. I've gone through your code and spotted several key problems that are causing it to fail:

1. Critical PHP Variable Mistake #

In your original PHP code, you’re assigning the from_datetime value to both $f_date and $t_date — this means you’re filtering data where the timestamp is between the same date/time, which will never return meaningful results. You also mixed mysqli and outdated mysql functions for error handling, which won’t work.

2. Form Submission vs Ajax Conflict #

You had both a native form submission and an Ajax click handler on the submit button. When you clicked the button, the form refreshed the page before the Ajax request could complete. We need to block this default behavior.

3. DateTimePicker Format Mismatch #

If your MySQL timestamp column uses the standard YYYY-MM-DD HH:MM:SS format, the DateTimePicker needs to output dates in the same format. Otherwise, the database won’t recognize the date strings you’re sending.

4. SQL Injection Vulnerability #

Your original query uses string interpolation, which is a major security risk. We’ll switch to prepared statements with mysqli to fix this.

5. Ajax Response Structure #

Right now, your PHP script returns the entire HTML page when handling Ajax requests. We’ll adjust it to only return the filtered table content instead.

Corrected Full Code (index2.php) #

<?php
$conn = mysqli_connect('localhost','root','','canvasjs_db');
if(!$conn){
    die("Connection failed: ". mysqli_connect_error());
}

// Handle Ajax filter request
if(isset($_POST['from_date']) && isset($_POST['to_date'])){
    $f_date = $_POST['from_date'];
    $t_date = $_POST['to_date'];
    
    // Use prepared statement to avoid SQL injection
    $query = "SELECT * FROM temp_log WHERE timestamp BETWEEN ? AND ? ORDER BY uid asc";
    $stmt = mysqli_prepare($conn, $query);
    mysqli_stmt_bind_param($stmt, "ss", $f_date, $t_date);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    
    // Only output table content for Ajax response
    ?>
    <table class="table table-bordered">
        <tr>
            <th width="5%">ID</th>
            <th width="30%">Device ID</th>
            <th width="43%">lat</th>
            <th width="43%">long</th>
            <th width="43%">Temperature</th>
            <th width="10%">Humidity</th>
            <th width="12%">TimestampDevice</th>
            <th width="12%">Time</th>
            <th width="12%">Timestamp</th>
        </tr>
        <?php while($row = mysqli_fetch_array($result)) { ?>
        <tr>
            <td><?php echo $row["uid"]; ?></td>
            <td><?php echo $row["deviceid"]; ?></td>
            <td><?php echo $row["lat"]; ?></td>
            <td><?php echo $row["long"]; ?></td>
            <td><?php echo $row["temp"]; ?></td>
            <td><?php echo $row["humidity"]; ?></td>
            <td><?php echo $row["notedtime"]; ?></td>
            <td><?php echo $row["formatedate"]; ?></td>
            <td><?php echo $row["timestamp"]; ?></td>
        </tr>
        <?php } ?>
    </table>
    <?php
    mysqli_close($conn);
    exit; // Stop execution after sending Ajax response
}

// Default load: fetch all data
$query = "SELECT * FROM temp_log ORDER BY uid asc";
$result1 = mysqli_query($conn, $query);
?>
<!DOCTYPE html>
<html>
<head>
    <title>Temperature Log Filter</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
    <link rel="stylesheet" type="text/css" href="jquery.datetimepicker.min.css">
    <script src="jquery.js"></script>
    <script src="jquery.datetimepicker.full.js"></script>
</head>
<body>
    <form id="filterForm" class="mb-3 mt-3">
        <input type="text" name="from_datetime" id="from_datetime" placeholder="From Date & Time" class="mr-2">
        <input type="text" name="to_datetime" id="to_datetime" placeholder="To Date & Time" class="mr-2">
        <button type="submit" class="btn btn-primary">Filter Data</button>
    </form>
    <div id="order_table">
        <table class="table table-bordered">
            <tr>
                <th width="5%">ID</th>
                <th width="30%">Device ID</th>
                <th width="43%">lat</th>
                <th width="43%">long</th>
                <th width="43%">Temperature</th>
                <th width="10%">Humidity</th>
                <th width="12%">TimestampDevice</th>
                <th width="12%">Time</th>
                <th width="12%">Timestamp</th>
            </tr>
            <?php while($row = mysqli_fetch_array($result1)) { ?>
            <tr>
                <td><?php echo $row["uid"]; ?></td>
                <td><?php echo $row["deviceid"]; ?></td>
                <td><?php echo $row["lat"]; ?></td>
                <td><?php echo $row["long"]; ?></td>
                <td><?php echo $row["temp"]; ?></td>
                <td><?php echo $row["humidity"]; ?></td>
                <td><?php echo $row["notedtime"]; ?></td>
                <td><?php echo $row["formatedate"]; ?></td>
                <td><?php echo $row["timestamp"]; ?></td>
            </tr>
            <?php } ?>
        </table>
    </div>
    <script type="text/javascript">
        $(function(){
            // Configure DateTimePicker to match MySQL timestamp format
            $("#from_datetime").datetimepicker({
                format: 'Y-m-d H:i:s',
                step: 15 // Optional: set time interval to 15 minutes
            });
            $("#to_datetime").datetimepicker({
                format: 'Y-m-d H:i:s',
                step: 15,
                // Prevent selecting a date before the "from" date
                onShow:function( ct ){
                    this.setOptions({
                        minDate: $('#from_datetime').val() ? $('#from_datetime').val() : false
                    })
                }
            });

            // Handle form submission with Ajax (block default page refresh)
            $('#filterForm').submit(function(e){
                e.preventDefault();
                var from_date = $('#from_datetime').val();
                var to_date = $('#to_datetime').val();
                
                if(from_date != '' && to_date != ''){
                    $.ajax({
                        url:"index2.php",
                        method:"POST",
                        data:{from_date:from_date, to_date:to_date},
                        success:function(data){
                            $('#order_table').html(data);
                        },
                        error:function(){
                            alert("Failed to load filtered data. Please try again.");
                        }
                    });
                } else {
                    alert("Please select both start and end date/time!");
                }
            });
        });
    </script>
</body>
</html>
<?php mysqli_close($conn); ?>

Key Improvements Breakdown #

• Fixed Variable Assignment: Corrected the $t_date value to use the submitted to_date instead of repeating from_date
• Prepared Statements: Eliminated SQL injection risk by using mysqli parameter binding
• DateTimePicker Alignment: Set the date format to match MySQL’s standard timestamp format (Y-m-d H:i:s)
• Prevent Page Refresh: Used e.preventDefault() in the form submit handler to let Ajax handle filtering without reloading
• User Experience: Added input placeholders, styled the submit button, and restricted the "to" date to be after the "from" date
• Optimized Ajax Response: Only returns the table content for filtered requests, not the full HTML page

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