PHP/MySQL技术疑问：跨页面获取列表ID与外键插入异常

Hey there! Let's break down your two technical issues one by one to get your to-do list up and running smoothly.

1. How to Get the Selected List ID Across Different Pages #

There are three reliable ways to pass and retrieve a list's ID between pages—pick the one that fits your workflow best:

Option 1: Pass via URL Parameters (GET Method) #

This is the most common approach for linking to a specific list's page.

• On your lists overview page, add the list ID to the link for each list:

  <!-- Example: Listing all user's lists -->
  <?php foreach ($userLists as $list): ?>
    <a href="tasks.php?id_liste=<?php echo intval($list['id_liste']); ?>">
      <?php echo htmlspecialchars($list['name']); ?>
    </a>
  <?php endforeach; ?>
  

• On the target page (like tasks.php), retrieve the ID and validate it to avoid security risks:

  // Check if the ID exists and is a valid integer
  if (isset($_GET['id_liste']) && is_numeric($_GET['id_liste'])) {
    $selectedListId = intval($_GET['id_liste']);
    // Use $selectedListId in your queries for tasks, etc.
  } else {
    // Redirect back to lists page if no valid ID is provided
    header("Location: lists.php");
    exit;
  }
  

Option 2: Store in Session Variables #

Use this if you need to keep the selected list ID persistent across multiple actions (like adding tasks without re-passing the ID each time):

• When the user selects a list, save the ID to the session:

  // On list selection (e.g., after clicking a list link or button)
  session_start();
  if (isset($_GET['id_liste']) && is_numeric($_GET['id_liste'])) {
    $_SESSION['selected_list_id'] = intval($_GET['id_liste']);
  }
  

• Retrieve it on any other page:

  session_start();
  if (isset($_SESSION['selected_list_id'])) {
    $selectedListId = $_SESSION['selected_list_id'];
  } else {
    // Handle case where no list is selected
  }
  

Option 3: Hidden Form Field (POST Method) #

Use this if you're submitting a form (like creating a task for the selected list):

• Add a hidden field to your form that holds the list ID:

  <form action="add_task.php" method="POST">
    <input type="hidden" name="id_liste" value="<?php echo intval($selectedListId); ?>">
    <!-- Other form fields for task details -->
    <button type="submit">Add Task</button>
  </form>
  

• Retrieve it in add_task.php:

  if (isset($_POST['id_liste']) && is_numeric($_POST['id_liste'])) {
    $listId = intval($_POST['id_liste']);
    // Insert task with this $listId as the foreign key
  }
  

2. Fixing Foreign Key Insert Issues & Accessing Created Lists #

Let's tackle why you can't insert foreign keys or access existing lists—these usually boil down to table structure, data validation, or query logic.

Step 1: Verify Your Database Table Structure #

First, make sure your foreign key constraints are set up correctly and data types match:

• Example user table (primary key):

  CREATE TABLE user (
    id_user INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    -- Other user fields
  );
  

• Example list table (foreign key to user):

  CREATE TABLE list (
    id_liste INT AUTO_INCREMENT PRIMARY KEY,
    list_name VARCHAR(255) NOT NULL,
    id_utilisateur INT NOT NULL,
    -- Foreign key constraint linking to user.id_user
    FOREIGN KEY (id_utilisateur) REFERENCES user(id_user)
      ON DELETE CASCADE -- Optional: Delete lists if user is deleted
  );
  

• Example task table (foreign key to list):

  CREATE TABLE task (
    id_task INT AUTO_INCREMENT PRIMARY KEY,
    task_name VARCHAR(255) NOT NULL,
    id_liste INT NOT NULL,
    FOREIGN KEY (id_liste) REFERENCES list(id_liste)
      ON DELETE CASCADE -- Optional: Delete tasks if list is deleted
  );
  

Key Checks:

• The foreign key field (e.g., id_utilisateur) must have the same data type as the primary key it references (e.g., user.id_user—both should be INT, same length/sign).
• Ensure foreign key constraints are actually created (some older MySQL setups use MyISAM, which doesn't support foreign keys—switch to InnoDB).

Step 2: Fix Foreign Key Insert Failures #

When inserting a new list, you must pass a valid id_utilisateur that exists in the user table. Always use the currently logged-in user's ID:

// Assuming you store the logged-in user's ID in the session
session_start();
if (!isset($_SESSION['user_id'])) {
  // Redirect to login if no user is logged in
  header("Location: login.php");
  exit;
}

$userId = $_SESSION['user_id'];
$listName = $_POST['list_name']; // From your create list form

// Use prepared statements to prevent SQL injection
$pdo = new PDO("mysql:host=localhost;dbname=your_db_name", "username", "password");
$stmt = $pdo->prepare("INSERT INTO list (list_name, id_utilisateur) VALUES (?, ?)");
$stmt->execute([$listName, $userId]);

// Check if insertion succeeded
if ($stmt->rowCount() > 0) {
  echo "List created successfully!";
} else {
  // Debug: Get the last error
  $error = $pdo->errorInfo();
  echo "Error creating list: " . $error[2];
}

Common Causes of Insert Failures:

• Using a NULL or non-existent id_utilisateur value.
• Forgetting to start the session or not storing the user ID correctly after login.
• SQL injection issues (always use prepared statements!).

Step 3: Accessing Created Lists #

To display only the lists belonging to the currently logged-in user, filter your query by id_utilisateur:

session_start();
if (!isset($_SESSION['user_id'])) {
  header("Location: login.php");
  exit;
}

$userId = $_SESSION['user_id'];
$pdo = new PDO("mysql:host=localhost;dbname=your_db_name", "username", "password");

// Fetch all lists for the logged-in user
$stmt = $pdo->prepare("SELECT * FROM list WHERE id_utilisateur = ?");
$stmt->execute([$userId]);
$userLists = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Display the lists
foreach ($userLists as $list) {
  echo "<a href='tasks.php?id_liste=" . intval($list['id_liste']) . "'>" . htmlspecialchars($list['list_name']) . "</a><br>";
}

If you're still not seeing lists, double-check:

• The id_utilisateur was correctly inserted when creating the list.
• Your query isn't missing the WHERE id_utilisateur = ? clause (without it, you might be fetching all lists, not just the user's).

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