CODEWITHSUNDEEP
phpmysql
Mee
Mee

Reputation: 83

error with update on duplicate key

Can you please tell me what I`m doing wrong?

I have a function which adds users_id, attribute_id and users entered answer in database. Until then it works. But when I add ON DUPLICATE KEY UPDATE part to update user answer not to create new one it throws an error:

Fatal error: Uncaught Error: Call to a member function execute() on boolean in ......\Attributes.php on line 39

And on that line I have $add_user->execute();

Below added function which inserts into database. If I remove "update on duplicate" part it inserts into database and everything works but I need that if user has already answered it, it updates existing not inserts a new data in database:

    public function updateAttributes($attribute_id, $attribute_text) {
    $dbobj = new Connection();
    $connection = $dbobj->getConnection();

    $user_id = $_SESSION['id'];
    $_attribute_id = mysqli_real_escape_string($connection, $attribute_id);
    $attribute = mysqli_real_escape_string($connection, $attribute_text);

    $add_user = $connection->prepare("INSERT into user_answers(user_id, attribute_id, answer) VALUES ('$user_id', '$_attribute_id', '$attribute') ON DUPLICATE KEY UPDATE answer = VALUES(answer) WHERE user_id = VALUES(user_id) AND attribute_id =  VALUES(attribute_id)");
    //$add_user->bind_param('iis', $user_id, $_attribute_id, $attribute);
    // ON DUPLICATE KEY UPDATE user_answer = VALUES(answer) WHERE user_id = VALUES(user_id) AND attribute_id =  VALUES(attribute_id)

    $add_user->execute();
    $add_user->close();
    $connection->close();
}

And here is sql code if needed. Contains two foreign keys and answer, which stores users entered answer:

CREATE TABLE IF NOT EXISTS `user_answers` (
  `user_id` int(11) DEFAULT NULL,
  `attribute_id` int(11) DEFAULT NULL,
  `answer` varchar(50) DEFAULT NULL,
  KEY `user_id` (`user_id`),
  KEY `attribute_id` (`attribute_id`),
  CONSTRAINT `attribute_id` FOREIGN KEY (`attribute_id`) REFERENCES `attributes` (`id`),
  CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Update: Added connection class:

class Connection {
    public function getConnection(){
        $conn = mysqli_connect("localhost","root","","dbname") or die("Couldn't connect");
        $conn->query("SET NAMES utf8");
        return $conn;
    }
}

EDIT

After updating the code I encountered this issue:

Below added function which inserts into database. If I remove "update on duplicate" part it inserts into database and everything works but I need that if user has already answered it, it updates existing not inserts a new data in database:

public function updateAttributes($attribute_id, $attribute_text) { $dbobj = new Connection(); $connection = $dbobj->getConnection();

$user_id = $_SESSION['id'];
$_attribute_id = mysqli_real_escape_string($connection, $attribute_id);
$attribute = mysqli_real_escape_string($connection, $attribute_text);

$add_user = $connection->prepare("INSERT into user_answers(user_id, attribute_id, answer) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE answer = VALUES(answer)");
$add_user->bind_param('iis', $user_id, $_attribute_id, $attribute);

$add_user->execute();
$add_user->close();
$connection->close();

}

Upvotes: 1

Views: 665

Answers (2)

Tschallacka
Tschallacka

Reputation: 28722

You're injecting variables in a string, defeating the purpose preparing the SQL statement... You are letting your app wide open for SQL injection attacks.

$add_user = $connection->prepare("INSERT into user_answers(user_id, attribute_id, answer)
                                  VALUES ('$user_id', '$_attribute_id', '$attribute') 
                                        --^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^-- this is is plain wrong
                                  ON DUPLICATE KEY UPDATE answer = VALUES(answer)  
                                  WHERE user_id = VALUES(user_id) AND attribute_id =  VALUES(attribute_id)");

The preparing probably fails, which causes it to return false

http://php.net/manual/en/pdo.prepare.php

If the database server cannot successfully prepare the statement, PDO::prepare() returns FALSE or emits PDOException (depending on error handling).

Please prepare it as you should.

$add_user = $connection->prepare("INSERT into user_answers(user_id, attribute_id, answer) VALUES (:user_id, :attribute_id, :attribute) ON DUPLICATE KEY UPDATE answer = VALUES(answer) WHERE user_id = VALUES(user_id) AND attribute_id =  VALUES(attribute_id)");
// Inject the variables you want in the prepared statement.

$result = $add_user->execute([
   ':user_id' => $user_id,
   ':attribute_id' => $_attribute_id,
   ':attribute' => $attribute,
]);

Also as Nick pointed out in his answer ditch the where.

Now, for the on duplicate key problem. How does your database discern which key is your main key? You have two keys defined now. That's why you have to define a primary key. You will probably have to truncate the table or remove the duplicates before executing this statement.

ALTER TABLE user_answers ADD PRIMARY KEY (user_id);

Upvotes: 1

Nick
Nick

Reputation: 147146

The error is caused by the prepare failing because ON DUPLICATE KEY UPDATE clauses should not have WHERE clauses. The row to be updated is already defined by the fact that the data to be inserted matched an existing key and so a WHERE clause is redundant. Try changing your code to:

$add_user = $connection->prepare("INSERT into user_answers(user_id, attribute_id, answer) VALUES ('$user_id', '$_attribute_id', '$attribute') ON DUPLICATE KEY UPDATE answer = VALUES(answer)");

Upvotes: 1

Related Questions