向MySQL插入数据时报错：Unknown column 'work_order_id' in 'field list'

Hey Chandu, let's walk through why you're hitting this error and how to fix it—plus some critical improvements to your code along the way.

First, Verify Your Table Structure #

The error directly says MySQL can't find the work_order_id column in your workorder_category table. Before diving into code:

• Run this command in your MySQL client to check the table's columns:

  DESCRIBE workorder_category;
  

• Double-check if the column is actually named work_order_id (maybe it's workorder_id without the underscore? Typos happen all the time!)

You're Not Handling the Query Result Correctly #

Right now, you're assigning the result of mysqli_query() directly to $workorderid—but that function returns a result set object, not the actual ID value. You need to fetch the data from that result set:

<?php
require 'connection.php';
// Turn on error reporting to catch hidden issues
error_reporting(E_ALL);
ini_set('display_errors', 1);

$workordername = $_POST["workordername"];
$submitted_on = $_POST["submitted_on"];

// First, run the query and check for errors
$result = mysqli_query($conn, "SELECT `work_order_id` FROM `workorder_category` WHERE `workorder_name` = '$workordername'");
if (!$result) {
    die("Query failed: " . mysqli_error($conn)); // This will show you exact SQL issues
}

// Fetch the actual ID from the result set
$row = mysqli_fetch_assoc($result);
$workorderid = $row['work_order_id'] ?? null; // Handle cases where no matching work order exists

// Now use $workorderid in your INSERT query
if ($workorderid) {
    // Insert into ticket_raising (example—adjust columns to match your table)
    $insertQuery = mysqli_query($conn, "INSERT INTO `ticket_raising` (`work_order_id`, `submitted_on`) VALUES ('$workorderid', '$submitted_on')");
    if (!$insertQuery) {
        die("Insert failed: " . mysqli_error($conn));
    }
    echo "Ticket submitted successfully!";
} else {
    echo "No work order found with name: " . htmlspecialchars($workordername);
}

Critical Fix: Stop Using Raw User Input in SQL #

Your current code is wide open to SQL injection attacks, which is a huge security risk. Replace your raw queries with prepared statements (they're safer and avoid syntax issues with user input):

// Safe version using prepared statements for the SELECT
$stmt = mysqli_prepare($conn, "SELECT `work_order_id` FROM `workorder_category` WHERE `workorder_name` = ?");
mysqli_stmt_bind_param($stmt, "s", $workordername); // "s" means string type
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$row = mysqli_fetch_assoc($result);
$workorderid = $row['work_order_id'] ?? null;

// Safe INSERT using prepared statements too
if ($workorderid) {
    $insertStmt = mysqli_prepare($conn, "INSERT INTO `ticket_raising` (`work_order_id`, `submitted_on`) VALUES (?, ?)");
    mysqli_stmt_bind_param($insertStmt, "ss", $workorderid, $submitted_on); // Adjust types if needed (i=integer, d=double)
    mysqli_stmt_execute($insertStmt);
    echo "Ticket submitted successfully!";
}

Final Checks #

• Make sure the ticket_raising table's work_order_id foreign key is correctly linked to workorder_category's primary key (usually work_order_id).
• If you're still getting the error, print out the full SQL query that's failing (using mysqli_error($conn) will show you exactly what MySQL is seeing).

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