Encountering a puzzling issue with a MySQL stored procedure. Here is the procedure:
DROP PROCEDURE IF EXISTS `removeSubscription`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `removeSubscription`(IN `userId` int,IN `channelId` int,IN `channelTypeTitle` varchar(255))
BEGIN
SET @userId = userId;
SET @channelId = channelId;
SET @channelTypeTitle = channelTypeTitle;
DELETE FROM subscriptions
WHERE
userid = @userId AND
channelid = @channelId AND
channeltypeid = (SELECT id FROM channeltypes WHERE `name` = @channelTypeTitle)
LIMIT 1;
END
;;
DELIMITER ;
Issue arises when calling this stored procedure from PHP; it ignores the 'WHERE' clause and deletes the first row encountered. Without 'LIMIT 1', everything in the table gets deleted :s
This is the PHP code being used:
$stmt = $db->prepare("CALL removeSubscription(:userId, :channelId, :channelTypeTitle)");
$stmt->bindValue('userId', $userId);
$stmt->bindValue('channelId', $channelId);
$stmt->bindValue('channelTypeTitle', $channelTypeTitle);
$stmt->execute();
Interestingly, renaming the passed parameters in both PHP's prepare statement and the Stored Procedure fixes the issue. Any obvious oversight on my end here?