php PDO 和 INSERT INTO 通过准备好的语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18655706/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PDO with INSERT INTO through prepared statements
提问by Bob Desaunois
On my adventure through the jungles of PHP: Data Objects I've encountered a problem with executing MySQL queries through prepared statements.
在我穿越 PHP 丛林的冒险中：数据对象 我在通过准备好的语句执行 MySQL 查询时遇到了问题。
Observe the following code:
观察以下代码：
$dbhost = "localhost";
$dbname = "pdo";
$dbusername = "root";
$dbpassword = "845625";
$link = new PDO("mysql:host=$dbhost;dbname=$dbname","$dbusername","$dbpassword");
$statement = $link->prepare("INSERT INTO testtable(name, lastname, age)
VALUES('Bob','Desaunois','18')");
$statement->execute();
This is me, and I want to be in my database. However I keep getting lost in.. well.. I don't know! According to google this is the way to do it, though my database stays empty.
这就是我，我想在我的数据库中。然而我一直迷失在……好吧……我不知道！根据谷歌，这是这样做的方法，尽管我的数据库保持为空。
Am I missing something here? Because I've been stuck for a good hour now and would like to continue studying PDO!
我在这里错过了什么吗？因为我已经被困了一个小时，想继续学习 PDO！
回答by Novocaine
You should be using it like so
你应该像这样使用它
<?php
$dbhost = 'localhost';
$dbname = 'pdo';
$dbusername = 'root';
$dbpassword = '845625';
$link = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbusername, $dbpassword);
$statement = $link->prepare('INSERT INTO testtable (name, lastname, age)
VALUES (:fname, :sname, :age)');
$statement->execute([
'fname' => 'Bob',
'sname' => 'Desaunois',
'age' => '18',
]);
Prepared statements are used to sanitize your input, and to do that you can use :foowithoutany single quotes within the SQL to bindvariables, and then in the execute()function you pass in an associative array of the variables you defined in the SQL statement.
准备好的语句用于清理您的输入，为此您可以在 SQL 中使用:foo不带任何单引号的绑定变量，然后在execute()函数中传入您在 SQL 语句中定义的变量的关联数组。
You may also use ?instead of :fooand then pass in an array of just the values to input like so;
您也可以使用?代替:foo然后传入一个仅包含要输入的值的数组，如下所示；
$statement = $link->prepare('INSERT INTO testtable (name, lastname, age)
VALUES (?, ?, ?)');
$statement->execute(['Bob', 'Desaunois', '18']);
Both ways have their advantages and disadvantages. I personally prefer to bind the parameter names as it's easier for me to read.
这两种方式都有其优点和缺点。我个人更喜欢绑定参数名称，因为它更容易阅读。
回答by Bob Desaunois
I have just rewritten the code to the following:
我刚刚将代码重写为以下内容：
$dbhost = "localhost";
$dbname = "pdo";
$dbusername = "root";
$dbpassword = "845625";
$link = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbusername, $dbpassword);
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$statement = $link->prepare("INSERT INTO testtable(name, lastname, age)
VALUES(?,?,?)");
$statement->execute(array("Bob","Desaunois",18));
And it seems to work now. BUT. if I on purpose cause an error to occur, it does not say there is any. The code works, but still; should I encounter more errors, I will not know why.
现在它似乎可以工作了。但。如果我故意导致错误发生，它不会说有任何错误。代码有效，但仍然有效；如果我遇到更多错误，我不知道为什么。
回答by rohitcopyright
Please add try catch also in your code so that you can be sure that there in no exception.
请在您的代码中也添加 try catch ，以便您可以确保没有例外。
try {
$hostname = "servername";
$dbname = "dbname";
$username = "username";
$pw = "password";
$pdo = new PDO ("mssql:host=$hostname;dbname=$dbname","$username","$pw");
} catch (PDOException $e) {
echo "Failed to get DB handle: " . $e->getMessage() . "\n";
exit;
}
回答by Bob Desaunois
Thanks to Novocaine88's answer to use a try catch loop I have successfully received an error message when I caused one.
感谢 Novocaine88 使用 try catch 循环的回答，当我导致错误消息时，我已成功收到一条错误消息。
<?php
$dbhost = "localhost";
$dbname = "pdo";
$dbusername = "root";
$dbpassword = "845625";
$link = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbusername, $dbpassword);
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
$statement = $link->prepare("INERT INTO testtable(name, lastname, age)
VALUES(?,?,?)");
$statement->execute(array("Bob","Desaunois",18));
} catch(PDOException $e) {
echo $e->getMessage();
}
?>
In the following code instead of INSERT INTO it says INERT.
在下面的代码中，而不是 INSERT INTO，它表示 INERT。
this is the error I got.
这是我得到的错误。
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INERT INTO testtable(name, lastname, age) VALUES('Bob','Desaunoi' at line 1
SQLSTATE[42000]：语法错误或访问冲突：1064 你的 SQL 语法有错误；检查与您的 MariaDB 服务器版本相对应的手册，了解在第 1 行的 'INERT INTO testtable(name, lastname, age) VALUES('Bob','Desaunoi' 附近使用的正确语法
When I "fix" the issue, it works as it should. Thanks alot everyone!
当我“修复”问题时，它会正常工作。非常感谢大家！
