“字段列表”中的未知列'problem_id'

Question

I am receiving this error, and I need some help. I have been stuck on this for a long time, and I feel as though the problem is staring me in the face.

This is my database structure. I am using MySQL

mysql> describe user_supplied_problem_solution;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id    | int(10) unsigned | NO   | MUL | NULL    |                |
| problem_id | int(10) unsigned | NO   | MUL | NULL    |                |
| solution   | text             | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

This is the PHP code that I receive the error when running:

public function createSolution($description, $user_id, $part_order = NULL) {
    $solution_id = NULL;
    if(!isset($part_order)) {
        print("running the problem level query
"); // Debug
        $query = "INSERT INTO user_supplied_problem_solution (problem_id, user_id, solution)
                VALUES (:problem_id, :user_id, :solution)";
    } else {
        print("running the part level query
"); // Debug
        $query = "INSERT INTO user_supplied_part_solution (user_id, part_id, solution)
                  VALUES (:user_id, :part_id, :solution)";
    }
    $this->dbh->beginTransaction();
    try {
        $statement = $this->dbh->prepare($query);
        if(!isset($part_order)) {
            print($query . PHP_EOL); // Debug
            print("running the problem level binding
"); // Debug
            print("The problem ID is $this->problem_id
"); // Debug
            $statement->bindParam(':problem_id', $this->problem_id, PDO::PARAM_INT);
        } else {
            // Get the part_id
            $part_id = $this->getPartIdFromOrder($part_order);
            if(!$part_id) {
                // Part_id does not exist -- rollback and return
                $this->dbh->rollBack();
                return false;
            }
            $statement->bindParam(':part_id', $part_id, PDO::PARAM_INT);
        }
    $statement->bindParam(':user_id', $user_id, PDO::PARAM_INT);
    $statement->bindParam(':solution', $description, PDO::PARAM_STR);
    $statement->execute();
    $solution_id = $this->dbh->lastInsertId();  
    } catch(PDOException $e) {
        $this->dbh->rollBack();
        print_r($e->errorInfo); // Debug
        return false;
    }
    $this->dbh->commit();
describe user_supplied_problem_solution;

Note the (debugging) print commands I put in. I left them in to show you the execution of the code. This is part of an object in which $this->dbh is created correctly when constructed. I ran this query via the command line and it was successful. This was my output:

mysql> INSERT INTO user_supplied_problem_solution (problem_id, user_id, solution)
-> VALUES (130, 40, "Solution");
Query OK, 1 row affected (0.00 sec)

This is what I got when I ran the code above with the method signature createSolution("this is a test solution", 40):

running the problem level query
INSERT INTO user_supplied_problem_solution (problem_id, user_id, solution)
                VALUES (:problem_id, :user_id, :solution)
running the problem level binding
The problem ID is 130
Array
(
    [0] => 42S22
    [1] => 1054
    [2] => Unknown column 'problem_id' in 'field list'
)

I apologize for how long this is, but I know the first response to questions like this is people asking for more information.

Answer 1

Solved -- This was due to code outside of this snippet. I was too narrow-minded and looked at the wrong piece of code. After running this query, the method calls another private method which had the issue.

Lesson learned: Don't copy and paste merrily and take a 10 minute break if you cannot figure something out.