请求修复PHP代码：删除students表id同步删除voters表userid

Hey there! Let's get this sorted out for you. Since you're new to programming, I'll break this down step by step so it's easy to follow.

Your goal is to delete matching records in the voters table automatically when you delete a record from the students table. This is called a cascading delete, and there are two reliable ways to implement it—one at the database level (recommended for long-term reliability) and one directly in your PHP code.

Option 1: Database-Level Cascading Deletes (Best Practice) #

This method lets the database handle the关联 deletion automatically, which is more efficient and less error-prone than coding it manually. Here's how to set it up:

1. First, make sure your tables use the InnoDB storage engine (MyISAM doesn't support foreign keys). You can check this in your database manager (like phpMyAdmin) or with this SQL query:

   SHOW TABLE STATUS LIKE 'students';
   SHOW TABLE STATUS LIKE 'voters';
   

2. Add a foreign key constraint to the voters table that links userid to students.id, with cascading delete enabled. Run this SQL query:

   ALTER TABLE voters
   ADD CONSTRAINT fk_voters_students
   FOREIGN KEY (userid) REFERENCES students(id)
   ON DELETE CASCADE;
   

   Once this is set up, any time you delete a record from students, all matching voters records with the same userid will be deleted automatically—no extra code needed!

Option 2: Manual Cascading Deletes in PHP #

If you can't modify the database right now, you can update your PHP code to run two delete queries (one for voters, one for students) and wrap them in a transaction to ensure both succeed or fail together. Here's a fixed version of your code:

<?php
ob_start();
session_start();

// Fix the typo in "Location" and add exit to stop code execution after redirect
if (!isset($_SESSION['id'])){
    header("Location: login.php");
    exit;
}

// Assume you're getting the student ID to delete via GET (e.g., ?delete_id=123)
if (isset($_GET['delete_id'])) {
    $deleteId = $_GET['delete_id'];

    // Replace with your actual database credentials
    $conn = mysqli_connect('localhost', 'your_username', 'your_password', 'your_database_name');

    if (!$conn) {
        die("Database connection failed: " . mysqli_connect_error());
    }

    // Start a transaction to ensure both deletes work or neither does
    mysqli_begin_transaction($conn);

    try {
        // First delete matching records from voters
        $voterSql = "DELETE FROM voters WHERE userid = ?";
        $voterStmt = mysqli_prepare($conn, $voterSql);
        mysqli_stmt_bind_param($voterStmt, "i", $deleteId);
        mysqli_stmt_execute($voterStmt);

        // Then delete the student record
        $studentSql = "DELETE FROM students WHERE id = ?";
        $studentStmt = mysqli_prepare($conn, $studentSql);
        mysqli_stmt_bind_param($studentStmt, "i", $deleteId);
        mysqli_stmt_execute($studentStmt);

        // Commit the transaction if both queries succeed
        mysqli_commit($conn);
        echo "Record deleted successfully!";
    } catch (Exception $e) {
        // Roll back if either query fails
        mysqli_rollback($conn);
        echo "Deletion failed: " . $e->getMessage();
    }

    // Clean up connections
    mysqli_stmt_close($voterStmt);
    mysqli_stmt_close($studentStmt);
    mysqli_close($conn);
}
?>

Key Fixes & Notes: #

• Prevent SQL Injection: Used prepared statements (mysqli_prepare/mysqli_stmt_bind_param) instead of directly inserting the ID into the query—this is critical for security.
• Transaction Safety: Wrapped both deletes in a transaction so you don't end up with orphaned records in voters if the students delete fails.
• Fixed Redirect: Corrected the typo in locatio to Location and added exit to stop code execution after redirecting.

Quick Checks to Ensure It Works #

• Make sure the id column in students is a primary key.
• Ensure userid in voters has the same data type as id in students (e.g., both INT).
• Always verify that the user performing the delete has proper permissions to avoid accidental or malicious deletions.

内容的提问来源于stack exchange，提问作者Jordan Jumaquio Santos