使用PHP预准备语句插入工单数据至数据库失败问题排查

Hey there, let's work through why your ticket data isn't making it to the database. I’ve gone over your newticket.php code and problem details, so here are the key areas to check and fix:

1. First, Fix the Broken HTML in Your Form #

Looking at the end of your provided code, the final <option> tag is incomplete:

<option value="<?php echo $p->id; ?>"><?php echo $p->name." ".$p->lastname; ?></option&

This should be </option> to properly close the tag. Unclosed HTML elements can cause browsers to parse the form incorrectly, which might prevent some fields from being submitted entirely. Make sure the entire form (including all <select>, <form>, and <div> tags) is fully closed.

2. Check the Ticket Insertion Logic (The Missing Piece!) #

Your form submits to ./?action=addticket, but the actual database insertion code lives in whatever handles that addticket action (likely your index.php or a dedicated action handler file). This is where the problem is most likely hiding. Here’s what to verify:

• Are you using Prepared Statements correctly? Ensure you’re binding all form fields to your SQL query. The form sends these fields:
  kind_id, title, description, date_at, time_at, project_id, category_id, priority_id, status_id, tecnico_id
  Double-check that every one of these is included in your INSERT statement and bound properly (no missing parameters, no mismatched data types).
• Add error handling to see exactly what’s failing. For example, if you’re using PDO:

  try {
      $pdo = new PDO("your-dsn-here");
      $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  
      $stmt = $pdo->prepare("INSERT INTO tickets (kind_id, title, description, date_at, time_at, project_id, category_id, priority_id, status_id, tecnico_id) 
                            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
      
      $stmt->execute([
          $_POST['kind_id'],
          $_POST['title'],
          $_POST['description'],
          $_POST['date_at'],
          $_POST['time_at'],
          $_POST['project_id'],
          $_POST['category_id'],
          $_POST['priority_id'],
          $_POST['status_id'],
          $_POST['tecnico_id']
      ]);
  
      echo "Ticket added successfully!";
  } catch(PDOException $e) {
      die("Insert failed: " . $e->getMessage());
  }
  

  If you’re using mysqli, add error checking for the prepare and execute steps:

  $stmt = $mysqli->prepare("INSERT INTO tickets (kind_id, title, description, date_at, time_at, project_id, category_id, priority_id, status_id, tecnico_id) 
                            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
  if (!$stmt) {
      die("Prepare failed: " . $mysqli->error);
  }
  
  $stmt->bind_param("issssssiii", 
      $_POST['kind_id'],
      $_POST['title'],
      $_POST['description'],
      $_POST['date_at'],
      $_POST['time_at'],
      $_POST['project_id'],
      $_POST['category_id'],
      $_POST['priority_id'],
      $_POST['status_id'],
      $_POST['tecnico_id']
  );
  
  if (!$stmt->execute()) {
      die("Execute failed: " . $stmt->error);
  }
  

  This error output will tell you exactly what’s wrong—like a missing table column, data type mismatch, or required field left empty.

3. Verify Required Fields and Database Constraints #

• Some form fields (like project_id, category_id, tecnico_id) have a default empty option but are marked required. If a user submits without selecting these, browsers should block submission—but if they bypass that (e.g., with an older browser), your database might reject the insert if those columns are set to NOT NULL.
• Double-check your database table structure: ensure all columns you’re inserting into exist, have the correct data types (e.g., date_at is a DATE type, tecnico_id matches your users table’s id type), and no non-null columns are missing from your INSERT statement.

4. Confirm Form Submission Method #

Your form uses method="post", so make sure your action handler uses $_POST to retrieve values, not $_GET. The action=addticket is a GET parameter, but form data is sent via POST—don’t mix these up!

Start by fixing the broken HTML, then add error handling to your insertion code. That error message will give you the exact clue you need to resolve the issue.

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