MySQL与PHP单记录环境搭建及用户个人中心单记录功能问询

Let’s break down your requirements into actionable, step-by-step implementations that align with standard web development practices.

1. Environment Setup: MySQL Database & PHP Connection #

First, we’ll set up the database structure and core PHP connection logic to support your user records.

Step 1: Create MySQL Database & User Table #

Log into your MySQL interface (phpMyAdmin or terminal) and run this SQL to create the necessary database and table:

CREATE DATABASE IF NOT EXISTS user_profile_db;
USE user_profile_db;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL, -- Always store hashed passwords!
    email VARCHAR(100) NOT NULL UNIQUE,
    wallet DECIMAL(10,2) DEFAULT 0.00,
    login_date DATETIME NULL -- Tracks the user's last login timestamp
);

-- Insert a test user (password is 'test123' hashed with PHP's password_hash)
INSERT INTO users (username, password, email, wallet) 
VALUES ('testuser', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'test@example.com', 50.00);

Important: Never store plain-text passwords—always use password_hash() to encrypt them before saving to the database.

Step 2: PHP Database Connection File #

Create a db_connect.php file to reuse database connections across your pages:

<?php
$host = 'localhost'; // Default for local servers like XAMPP/WAMP
$dbname = 'user_profile_db';
$db_user = 'root'; // Default local MySQL username
$db_pass = ''; // Default local MySQL password (empty for XAMPP/WAMP)

// Establish connection
$conn = mysqli_connect($host, $db_user, $db_pass, $dbname);

// Check for connection errors
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
?>

2. Implement User Personal Center Features #

Now we’ll build the login system and profile page that meets your specific requirements.

Step 1: User Login Page (login.php) #

This page handles authentication, updates the login date, and starts a session to track the logged-in user:

<?php
session_start();
include 'db_connect.php';

$error = '';

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $username = mysqli_real_escape_string($conn, $_POST['username']);
    $password = $_POST['password'];

    // Fetch user from database
    $query = "SELECT * FROM users WHERE username = '$username'";
    $result = mysqli_query($conn, $query);
    $user = mysqli_fetch_assoc($result);

    // Verify password and log user in
    if ($user && password_verify($password, $user['password'])) {
        // Update login date to current time
        $update_login = "UPDATE users SET login_date = NOW() WHERE id = " . $user['id'];
        mysqli_query($conn, $update_login);

        // Store user data in session
        $_SESSION['user_id'] = $user['id'];
        $_SESSION['username'] = $user['username'];
        header("Location: profile.php");
        exit();
    } else {
        $error = "Invalid username or password";
    }
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>User Login</title>
</head>
<body>
    <h2>Login to Your Profile</h2>
    <?php if ($error): ?>
        <p style="color: red;"><?php echo $error; ?></p>
    <?php endif; ?>
    <form method="POST">
        <label>Username:</label>
        <input type="text" name="username" required><br><br>
        <label>Password:</label>
        <input type="password" name="password" required><br><br>
        <button type="submit">Login</button>
    </form>
</body>
</html>

Step 2: User Profile Page (profile.php) #

This page displays the logged-in user’s details (replacing the hardcoded ID 1 with session-based user data):

<?php
session_start();
include 'db_connect.php';

// Redirect to login if user isn't authenticated
if (!isset($_SESSION['user_id'])) {
    header("Location: login.php");
    exit();
}

// Fetch current user's data
$user_id = $_SESSION['user_id'];
$query = "SELECT * FROM users WHERE id = $user_id";
$result = mysqli_query($conn, $query);
$user = mysqli_fetch_assoc($result);
?>

<!DOCTYPE html>
<html>
<head>
    <title>Your Profile</title>
</head>
<body>
    <h2>Welcome, <?php echo $user['username']; ?>!</h2>
    <p>Last Login: <?php 
        echo $user['login_date'] 
            ? date("F j, Y, g:i a", strtotime($user['login_date'])) 
            : "This is your first login!"; 
    ?></p>
    <p>Wallet Balance: $<?php echo number_format($user['wallet'], 2); ?></p>
    <a href="logout.php">Logout</a>
</body>
</html>

Step 3: Logout Page (logout.php) #

End the user session to log them out:

<?php
session_start();
session_unset();
session_destroy();
header("Location: login.php");
exit();
?>

Key Security & Improvement Notes #

• SQL Injection Prevention: We used mysqli_real_escape_string() here, but for production, switch to prepared statements for even stronger protection.
• Session Security: Always validate sessions before loading user-specific data to block unauthorized access.
• Error Handling: Add more robust error handling (e.g., database query failure alerts) for production environments.

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