PHP：通过ID变量显示外键数据的问题排查

Got it, let's break down what you're missing here—you've got the full user list working, but need to narrow it down to a single user and pull in their linked address data. Here are the critical pieces you're likely overlooking:

• Add a WHERE clause to target the specific user
  Right now, you're fetching every user with a query like SELECT * FROM users. To use your $value input, you need to filter results with a WHERE clause. For example, if $value is a user ID:

  SELECT * FROM users WHERE id = ?
  

  If it's a username or email, swap id for the matching column name (adjust to fit your table structure).

• Join the address table to pull in foreign key data
  To get the linked address info, you need to combine the users and address tables using their foreign key relationship (let's assume users.id links to address.user_id). Update your query to include a JOIN:

  SELECT u.*, a.* 
  FROM users u
  INNER JOIN address a ON u.id = a.user_id
  WHERE u.id = ?
  

  This pulls all user data alongside their associated address in one result set. Use LEFT JOIN instead if some users might not have an address (to avoid excluding them).

• Use prepared statements to stay secure (non-negotiable!)
  Never plug $value directly into your query string—that's a massive SQL injection risk. Use prepared statements to bind the value safely. Here's an example with PDO:

  $stmt = $pdo->prepare("SELECT u.*, a.* FROM users u JOIN address a ON u.id = a.user_id WHERE u.id = ?");
  $stmt->execute([$value]);
  $userDetails = $stmt->fetch(PDO::FETCH_ASSOC);
  

  For mysqli:

  $stmt = $mysqli->prepare("SELECT u.*, a.* FROM users u JOIN address a ON u.id = a.user_id WHERE u.id = ?");
  $stmt->bind_param("i", $value); // Use "s" instead of "i" if $value is a string (like username)
  $stmt->execute();
  $result = $stmt->get_result();
  $userDetails = $result->fetch_assoc();
  

• Handle empty results gracefully
  After running the query, check if $userDetails is empty. If no user matches the input, display a clear message like "No user found with that value" instead of showing nothing or the full user list.

• Verify your input is being passed correctly
  Quick sanity check: Use var_dump($value) right before your query to make sure it's actually receiving the input from your admin form. If it's empty or has an unexpected value, fix your form/submission logic first.

Once you implement these steps, you'll be able to fetch a single user's full details (including their address) based on the input value.

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