我有一个这样的触发器:
CREATE TRIGGER prevent_self_votes BEFORE INSERT ON votes FOR EACH ROW BEGIN IF(new.user_id = (SELECT author_id FROM posts WHERE id=new.post_id)) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "you cannot vote for yourself"; END IF; END;现在我想知道,我怎样才能使用那些you cannot vote for yourself在PHP中you cannot vote for yourself文本?
注意:我使用PDO。
I have a trigger like this:
CREATE TRIGGER prevent_self_votes BEFORE INSERT ON votes FOR EACH ROW BEGIN IF(new.user_id = (SELECT author_id FROM posts WHERE id=new.post_id)) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "you cannot vote for yourself"; END IF; END;Now I want to know, how can I use of that text you cannot vote for yourself in PHP?
Note: I use PDO.
最满意答案
您可以尝试...捕获并检查语句执行的结果以打印错误信息,如下所示:
存根
create table votes (user_id int); delimiter // create trigger prevent_self_votes before insert on votes for each row begin if (new.user_id = 12) then signal sqlstate '45000' set message_text = 'You cannot vote for yourself, dude!'; end if; end // delimiter ;PHP脚本
<?php $db = new PDO('mysql:host=localhost;dbname=test', 'test', 'test'); $sql = 'insert into votes values (:user_id)'; $statement = $db->prepare($sql); if ($statement === false) { echo 'statement is false'; exit(); } try { $result = $statement->execute(array(':user_id'=>12)); if ($result === false) { $error = $statement->errorInfo(); print_r($error); echo "$error[2] ... is the error reported by trigger\n"; } else { print_r($result); echo 'Inserted', "\n"; } } catch (PDOException $e) { echo $e->getMessage(); } ?>结果
$ php test.php Array ( [0] => 45000 [1] => 1644 [2] => You cannot vote for yourself, dude! ) You cannot vote for yourself, dude! ... is the error reported by trigger正如您在此处注意到的,您可以使用$statement->errorInfo()[2]来提取触发器提供的信息。
http://php.net/manual/en/pdo.errorinfo.php表示数组中的第一项是SQLSTATE ANSI SQL错误代码,第二项是驱动程序特定的错误代码,第三项是驱动程序特定的错误消息。
You can do a try...catch and check for the result of statement execution to print error information like this:
Stub
create table votes (user_id int); delimiter // create trigger prevent_self_votes before insert on votes for each row begin if (new.user_id = 12) then signal sqlstate '45000' set message_text = 'You cannot vote for yourself, dude!'; end if; end // delimiter ;PHP script
<?php $db = new PDO('mysql:host=localhost;dbname=test', 'test', 'test'); $sql = 'insert into votes values (:user_id)'; $statement = $db->prepare($sql); if ($statement === false) { echo 'statement is false'; exit(); } try { $result = $statement->execute(array(':user_id'=>12)); if ($result === false) { $error = $statement->errorInfo(); print_r($error); echo "$error[2] ... is the error reported by trigger\n"; } else { print_r($result); echo 'Inserted', "\n"; } } catch (PDOException $e) { echo $e->getMessage(); } ?>Result
$ php test.php Array ( [0] => 45000 [1] => 1644 [2] => You cannot vote for yourself, dude! ) You cannot vote for yourself, dude! ... is the error reported by triggerAs you notice here, you could use the output of $statement->errorInfo()[2] to extract information provided by the trigger.
http://php.net/manual/en/pdo.errorinfo.php says that the first item in the array is SQLSTATE ANSI SQL error code, second item is driver specific error code and third item is driver specific error message.
更多推荐
发布评论