如何在PHP中使用SQLSTATE'45000'MESSAGE

如何在PHP中使用SQLSTATE'45000'MESSAGE_TEXT？(How to use of SQLSTATE '45000' MESSAGE_TEXT in PHP?)

我有一个这样的触发器：

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 trigger

As 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.