功能/触发器已在使用中?
Im having problems getting an update function to work. The function marks badges as seen so that they are hidden from a notification window.
The function is called when the user clicks a button to mark them as seen.
I have two triggers on the table its trying to update which I think may be causing the problem.
The problem is : Can't update table 'users' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
Triggers:
Function:
function markAsSeen() {
require "connect.php";
$seen = mysqli_query($connection,"Update userbadges
INNER JOIN users ON users.id = userbadges.user_id
SET seen='1'
WHERE studentid = '".$_SESSION["studentid"]."' && seen=0") or die(mysqli_error($connection));
while ($data = mysqli_fetch_array($seen)) {
echo 'Done';
}
}
Is there any way around this?
Your issue is that the update_users_trigger
trigger makes changes to the contents of the table users
, while the query that is triggering the execution of this trigger also uses the table users
.
You will need to adjust your query so that this deadlock doesn't occur. It isn't clear which fields are from each table, but I suspect that in your initial query you need to join on users
so that you can query on studentid
.
You could create a different function to get the userID that you need something like the following:
require_once "connect.php";
function getUserIDFromStudentID($student_id, mysqli $connection)
{
$query = 'SELECT id FROM users WHERE studentid = ? LIMIT 1';
$stmt = $connection->prepare($query);
// Replace the below s to an i if it's supposed to be an integer
$stmt->bind_param("s", $student_id);
$stmt->execute();
$result = $stmt->get_result();
$record = $result->fetch_object();
$result->free();
if ($record) {
return $record->id;
}
}
function markAsSeen(mysqli $connection) {
$user_id = getUserIDFromStudentID($_SESSION["studentid"], $connection);
if (! $user_id) {
throw new Exception('Unable to get user id');
}
$seen_query = 'UPDATE userbadges SET seen = 1 WHERE user_id = ? and seen = 0';
$stmt = $connection->prepare($seen_query);
// Replace the below s to an i if it's supposed to be an integer
$stmt->bind_param("s", $user_id);
$result = $stmt->execute();
if (! $result) {
die(mysqli_error($connection));
}
echo 'Done';
}
Passing the connection object around rather than requiring a global file to be required every time will allow for more flexibility.