PHP数据存储（新数据置顶）及MySQL网页日期异常、表格展示咨询

阿华AIGC实验室

2026-5-19

Solutions for Your MySQL-PHP Web Issues

Hey there! Let's work through your three main issues with clear, actionable code examples and explanations.

1. Fixing Incorrect Datetime Display After Database Updates

Most wonky datetime displays come down to timezone mismatches or misaligned formatting between MySQL and PHP. Here are two straightforward fixes:

Fix 1: Sync Timezones Across MySQL & PHP

Make sure both systems use the same timezone (e.g., Asia/Shanghai or UTC):

Add this at the top of all your PHP files to set PHP's timezone:

date_default_timezone_set('Asia/Shanghai'); // Replace with your local timezone

Set MySQL's session timezone right after connecting to the database:

$conn = new mysqli($servername, $username, $password, $dbname);
$conn->query("SET time_zone = '+08:00'"); // Match your PHP timezone offset

Fix 2: Explicitly Format Datetime on Output

If MySQL stores the correct time but PHP displays it wrong, reformat the value before showing it:

// Fetch raw datetime from database
$raw_datetime = $row['updated_at'];
// Convert to a human-friendly format
$formatted_datetime = date('Y-m-d H:i:s', strtotime($raw_datetime));
echo $formatted_datetime;

2. Displaying Data in a Table Format

We’ll split this into two files: one to handle database retrieval, and another to render the HTML table.

File 1: `fetch_data.php` (Data Retrieval Logic)

This file connects to the database and fetches your data:

<?php
date_default_timezone_set('Asia/Shanghai');
$servername = "localhost";
$username = "your_db_user";
$password = "your_db_pass";
$dbname = "your_db_name";

// Connect to database
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// Fetch data (we'll adjust this later for the "new data first" requirement)
$sql = "SELECT id, title, content, created_at FROM your_table";
$result = $conn->query($sql);

$data_rows = [];
if ($result->num_rows > 0) {
  while($row = $result->fetch_assoc()) {
    // Format datetime here if needed
    $row['created_at'] = date('Y-m-d H:i:s', strtotime($row['created_at']));
    $data_rows[] = $row;
  }
}
$conn->close();
?>

File 2: `display_page.php` (Web Page with Table)

This file includes the retrieval logic and renders the table:

<?php include 'fetch_data.php'; ?>
<!DOCTYPE html>
<html>
<head>
  <title>Data Table</title>
  <style>
    table { border-collapse: collapse; width: 90%; margin: 30px auto; }
    th, td { border: 1px solid #eee; padding: 10px; text-align: left; }
    th { background-color: #f8f9fa; font-weight: bold; }
  </style>
</head>
<body>
  <h2>Database Records</h2>
  <table>
    <thead>
      <tr>
        <th>ID</th>
        <th>Title</th>
        <th>Content</th>
        <th>Created At</th>
      </tr>
    </thead>
    <tbody>
      <?php if (!empty($data_rows)): ?>
        <?php foreach ($data_rows as $row): ?>
          <tr>
            <td><?php echo $row['id']; ?></td>
            <td><?php echo htmlspecialchars($row['title']); ?></td>
            <td><?php echo htmlspecialchars($row['content']); ?></td>
            <td><?php echo $row['created_at']; ?></td>
          </tr>
        <?php endforeach; ?>
      <?php else: ?>
        <tr>
          <td colspan="4">No records found</td>
        </tr>
      <?php endif; ?>
    </tbody>
  </table>
</body>
</html>

Pro tip: Use htmlspecialchars() to prevent cross-site scripting (XSS) attacks when outputting user-provided data.

3. Storing New Data & Showing It at the Top

To make new entries appear first, you need to:

Insert data correctly into the database
Sort retrieved data in descending order (by auto-increment ID or timestamp)

File: `submit_data.php` (Store New Records)

This file handles form submissions and inserts data into your table:

<?php
date_default_timezone_set('Asia/Shanghai');
$servername = "localhost";
$username = "your_db_user";
$password = "your_db_pass";
$dbname = "your_db_name";

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// Get data from form (use POST for secure submissions)
$title = $_POST['title'] ?? '';
$content = $_POST['content'] ?? '';

// Sanitize input to prevent SQL injection
$title = $conn->real_escape_string($title);
$content = $conn->real_escape_string($content);

// Insert data (NOW() uses MySQL's current time, aligned with your timezone)
$sql = "INSERT INTO your_table (title, content, created_at) VALUES ('$title', '$content', NOW())";

if ($conn->query($sql) === TRUE) {
  echo "New record added successfully! <a href='display_page.php'>View Table</a>";
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>

Update `fetch_data.php` to Show New Data First

Modify the SQL query to sort by id (or created_at) in descending order:

// Original query:
// $sql = "SELECT id, title, content, created_at FROM your_table";
// Updated query (sort by newest first):
$sql = "SELECT id, title, content, created_at FROM your_table ORDER BY id DESC";
// OR use created_at if you prefer:
// $sql = "SELECT id, title, content, created_at FROM your_table ORDER BY created_at DESC";

This will pull the newest records first, so they appear at the top of your table.

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