PHP/MySQL医疗系统：如何通过HTML表单用外键创建就诊记录

Hey Andy, let's walk through building that HTML form that ties together all your tables to create new Visit records smoothly. Since you already have data in Patients, Doctors, Conditions, and Medication, we'll use dropdowns to let users select existing entries (way better than typing raw IDs!) and then handle the submission to insert into the Visit table.

First, create a form that includes both fields specific to visits (like date, notes) and dropdowns for the related tables. Each dropdown will pull existing data from your MySQL tables so users can pick valid records:

<form method="POST" action="submit_visit.php">
  <h3>New Visit Record</h3>
  
  <!-- Patient Dropdown -->
  <div>
    <label for="patient_id">Patient:</label>
    <select id="patient_id" name="patient_id" required>
      <option value="">Select a Patient</option>
      <!-- We'll populate these options with PHP next -->
    </select>
  </div>

  <!-- Doctor Dropdown -->
  <div>
    <label for="doctor_id">Doctor:</label>
    <select id="doctor_id" name="doctor_id" required>
      <option value="">Select a Doctor</option>
    </select>
  </div>

  <!-- Condition Dropdown -->
  <div>
    <label for="condition_id">Diagnosed Condition:</label>
    <select id="condition_id" name="condition_id" required>
      <option value="">Select a Condition</option>
    </select>
  </div>

  <!-- Medication Dropdown -->
  <div>
    <label for="medication_id">Prescribed Medication:</label>
    <select id="medication_id" name="medication_id" required>
      <option value="">Select a Medication</option>
    </select>
  </div>

  <!-- Visit-Specific Fields -->
  <div>
    <label for="visit_date">Visit Date:</label>
    <input type="date" id="visit_date" name="visit_date" required>
  </div>

  <div>
    <label for="visit_notes">Visit Notes:</label>
    <textarea id="visit_notes" name="visit_notes" rows="4"></textarea>
  </div>

  <button type="submit">Create Visit Record</button>
</form>

Next, add PHP code above the HTML form to connect to your MySQL database and fetch data from each related table. We'll use prepared statements to keep things secure:

<?php
// Database connection (update with your credentials)
$host = 'localhost';
$dbname = 'your_medical_db';
$username = 'root';
$password = '';

try {
  $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
  die("Database connection failed: " . $e->getMessage());
}

// Fetch patients for dropdown
$patients = $pdo->query("SELECT id, full_name FROM Patients ORDER BY full_name")->fetchAll(PDO::FETCH_ASSOC);

// Fetch doctors
$doctors = $pdo->query("SELECT id, name FROM Doctors ORDER BY name")->fetchAll(PDO::FETCH_ASSOC);

// Fetch conditions
$conditions = $pdo->query("SELECT id, condition_name FROM Conditions ORDER BY condition_name")->fetchAll(PDO::FETCH_ASSOC);

// Fetch medications
$medications = $pdo->query("SELECT id, medication_name FROM Medication ORDER BY medication_name")->fetchAll(PDO::FETCH_ASSOC);
?>

Now, replace the empty <select> sections with PHP loops to generate options. For example, the patient dropdown becomes:

<select id="patient_id" name="patient_id" required>
  <option value="">Select a Patient</option>
  <?php foreach($patients as $patient): ?>
    <option value="<?= $patient['id'] ?>"><?= htmlspecialchars($patient['full_name']) ?></option>
  <?php endforeach; ?>
</select>

Repeat this pattern for the doctor, condition, and medication dropdowns, swapping in the corresponding variables and field names.

Create a new file called submit_visit.php to process the form data and insert a new record into the Visit table. Again, use prepared statements to prevent SQL injection:

<?php
// Database connection (same as above)
$host = 'localhost';
$dbname = 'your_medical_db';
$username = 'root';
$password = '';

try {
  $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // Check if form was submitted
  if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    // Get form data
    $patient_id = $_POST['patient_id'];
    $doctor_id = $_POST['doctor_id'];
    $condition_id = $_POST['condition_id'];
    $medication_id = $_POST['medication_id'];
    $visit_date = $_POST['visit_date'];
    $visit_notes = $_POST['visit_notes'];

    // Insert into Visit table
    $stmt = $pdo->prepare("INSERT INTO Visit (patient_id, doctor_id, condition_id, medication_id, visit_date, visit_notes) 
                          VALUES (:patient_id, :doctor_id, :condition_id, :medication_id, :visit_date, :visit_notes)");
    $stmt->bindParam(':patient_id', $patient_id);
    $stmt->bindParam(':doctor_id', $doctor_id);
    $stmt->bindParam(':condition_id', $condition_id);
    $stmt->bindParam(':medication_id', $medication_id);
    $stmt->bindParam(':visit_date', $visit_date);
    $stmt->bindParam(':visit_notes', $visit_notes);

    if ($stmt->execute()) {
      echo "Success! New visit record created.";
      // Optional: Redirect back to the form or a dashboard
      // header("Location: visit_form.php");
      // exit;
    } else {
      echo "Error creating record. Please try again.";
    }
  }
} catch(PDOException $e) {
  die("Error: " . $e->getMessage());
}
?>

• Foreign Key Constraints: Make sure your Visit table has foreign key constraints set up for patient_id, doctor_id, etc. This ensures you can't insert invalid IDs that don't exist in the related tables (you can set this up in phpMyAdmin under the "Structure" tab for the Visit table).
• Input Sanitization: We used htmlspecialchars() when rendering dropdown options to prevent XSS attacks. For the notes field, the prepared statement handles sanitization for the database.
• Error Handling: Add more user-friendly error messages if needed (e.g., "Invalid patient selected" if the ID doesn't exist, though foreign keys should catch this).
• Security: Never hardcode your database credentials in production—use environment variables or a config file outside your web root.

That should do it! You'll have a form that lets users select existing records from your other tables and creates a properly linked Visit entry every time.

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