jQuery FullCalendar结合SQL Server与PHP的事件加载及添加故障求助

Hey there, let's tackle your FullCalendar problems one by one. The "Uncaught TypeError: Cannot read property 'hasTime' of undefined" error, missing event rendering, and failed inserts are all likely tied to data format mismatches and unhandled FullCalendar request parameters. Here's how to fix this:

1. First: Handle FullCalendar's start/end Parameters in load.php #

FullCalendar automatically appends start and end query parameters to fetch events only for the visible date range. If your backend ignores these, you might return invalid or excessive data that breaks parsing.

Updated load.php Example: #

<?php
// Connect to MS SQL Server (adjust credentials as needed)
$serverName = "your_server_name";
$connectionInfo = array( "Database"=>"your_db", "UID"=>"your_user", "PWD"=>"your_pass");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( !$conn ) {
    die( print_r( sqlsrv_errors(), true));
}

// Capture FullCalendar's date range parameters
$start = $_GET['start'];
$end = $_GET['end'];

// Query events within the requested range (adjust column names to match your table)
$sql = "SELECT event_title AS title, 
               CONVERT(varchar, event_start, 120) AS start, 
               CONVERT(varchar, event_end, 120) AS end, 
               CAST(all_day AS BIT) AS allDay
        FROM events 
        WHERE event_start >= ? AND event_end <= ?";

$params = array($start, $end);
$stmt = sqlsrv_query( $conn, $sql, $params);

if( $stmt === false ) {
    die( print_r( sqlsrv_errors(), true));
}

// Build valid event array for FullCalendar
$events = array();
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
    // Ensure allDay is a boolean (not a string like "0" or "1")
    $row['allDay'] = (bool)$row['allDay'];
    $events[] = $row;
}

// Output properly formatted JSON
header('Content-Type: application/json');
echo json_encode($events);

sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>

Key Fixes Here:

• Filters events to only the date range FullCalendar requests, reducing unnecessary data.
• Converts SQL dates to ISO 8601 format (via CONVERT(varchar, ..., 120)), which FullCalendar parses reliably.
• Casts the all_day field to a boolean, since FullCalendar expects a boolean value for allDay.

2. Fix the "Cannot read property 'hasTime' of undefined" Error #

This error occurs when FullCalendar tries to parse an event object that's missing required fields (like start) or is malformed.

Verify Your JSON Output: #

Visit load.php?start=2024-01-01&end=2024-01-31 directly in your browser. You should see valid JSON like this:

[
  {"title":"Team Sync","start":"2024-01-15T14:00:00","end":"2024-01-15T15:00:00","allDay":false},
  {"title":"Company Holiday","start":"2024-01-20","end":"2024-01-21","allDay":true}
]

If you see empty arrays, invalid syntax, or missing start/title fields, your query or database data is the root issue.

3. Fix Event Insertion into MS SQL Server #

To add events successfully, you need to capture frontend input, send it to a backend script, and insert it safely into your table.

Example Frontend Code (in index.php): #

document.addEventListener('DOMContentLoaded', function() {
    var calendarEl = document.getElementById('calendar');
    var calendar = new FullCalendar.Calendar(calendarEl, {
        initialView: 'dayGridMonth',
        events: 'load.php', // Pull events from your backend
        selectable: true,
        select: function(info) {
            var title = prompt('Enter event title:');
            if (title) {
                // Send event data to backend for insertion
                fetch('insert_event.php', {
                    method: 'POST',
                    headers: {
                        'Content-Type': 'application/x-www-form-urlencoded',
                    },
                    body: 'title=' + encodeURIComponent(title) +
                          '&start=' + encodeURIComponent(info.startStr) +
                          '&end=' + encodeURIComponent(info.endStr) +
                          '&allDay=' + info.allDay
                })
                .then(response => response.json())
                .then(data => {
                    if (data.success) {
                        calendar.refetchEvents(); // Refresh calendar to show new event
                    } else {
                        alert('Failed to add event: ' + data.error);
                    }
                })
                .catch(error => {
                    alert('Error adding event: ' + error);
                });
            }
            calendar.unselect();
        }
    });
    calendar.render();
});

Example Backend insert_event.php: #

<?php
$serverName = "your_server_name";
$connectionInfo = array( "Database"=>"your_db", "UID"=>"your_user", "PWD"=>"your_pass");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( !$conn ) {
    echo json_encode(array('success'=>false, 'error'=>print_r(sqlsrv_errors(), true)));
    exit;
}

// Get POST data from frontend
$title = $_POST['title'];
$start = $_POST['start'];
$end = $_POST['end'];
$allDay = $_POST['allDay'] === 'true' ? 1 : 0;

// Insert with parameter binding (prevents SQL injection)
$sql = "INSERT INTO events (event_title, event_start, event_end, all_day)
        VALUES (?, ?, ?, ?)";

$params = array($title, $start, $end, $allDay);
$stmt = sqlsrv_query( $conn, $sql, $params);

if( $stmt === false ) {
    echo json_encode(array('success'=>false, 'error'=>print_r(sqlsrv_errors(), true)));
} else {
    echo json_encode(array('success'=>true));
}

sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>

Key Fixes for Insertion:

• Uses parameter binding to avoid SQL injection and ensure data types match your table.
• Converts the frontend's allDay boolean to an integer (MS SQL's BIT type uses 0/1).
• Returns a JSON response to let the frontend know if the insert succeeded, then refreshes the calendar.

Final Checks #

• Confirm your MS SQL table has correct data types:
  • event_start/event_end: datetime or datetime2
  • all_day: bit
  • event_title: nvarchar(255) or similar
• Check browser console for additional errors (e.g., AJAX failures, invalid JSON).
• Ensure your server allows POST requests to insert_event.php.

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