通过PHP PDO创建MySQL表后无法插入
问题描述:
我对PDO和MySQL相当陌生,并且遇到了一个奇怪的问题。我查看了很多PDO和MySQL线程,但没有人似乎有这个确切的问题。通过PHP PDO创建MySQL表后无法插入
当我创建数据库,新的mysql新用户,一个表和几列后,我无法使用INSERT INTO来填充数据库。 PDO引发以下错误:
SQLSTATE [42S02]:基表或视图未找到:1146表“cms01db.cms_settings”不存在
如果我看在phpMyAdmin,数据库中存在,该表和列存在,排序正确等。如果我然后在PHPMyAdmin中删除表并在那里手动重新创建它,具有完全相同的规格,我可以使用相同的INSERT INTO脚本,它工作得很好。
我在做什么错?
脚本来创建数据库:
// define server and root login
$servername = 'localhost';
$username = 'root';
// define new user for new database and login password
$newUser = 'admin';
$newPass = 'password';
$dbname = 'cms01db';
// create database
try {
// connect to mysql and login
$conn = new PDO("mysql:host=$servername", $username);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// setup command to create database, new user and asign to new database
$sql = "CREATE DATABASE `$dbname` CHARACTER SET ascii COLLATE ascii_general_ci;
CREATE USER '$newUser'@'localhost' IDENTIFIED BY '$newPass';
GRANT ALL ON `$dbname`.* TO '$newUser'@'localhost';
FLUSH PRIVILEGES;";
// use exec() because no results are returned
$conn->exec($sql);
echo "Database created successfully<br>";
}
// if shit goes wrong...
catch(PDOException $e)
{
// display error why
echo $sql . "<br>" . $e->getMessage();
}
// terminate db connection
$conn = null;
脚本来创建表和列:
// login with new user and create database tables & columns
try {
// connect to mysql and login
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $newUser, $newPass);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// sql to create table and columns
$sql = "DROP TABLE IF EXISTS cms_setings;
CREATE TABLE cms_setings (
measurements VARCHAR(1) NOT NULL,
brasizes VARCHAR(3) NOT NULL,
shoesizes VARCHAR(3) NOT NULL
)";
// use exec() because no results are returned
$conn->exec($sql);
echo "CMS Settings Table created successfully<br>";
}
// if shit goes wrong...
catch(PDOException $e)
{
// display error why
echo $sql . "<br>" . $e->getMessage();
}
// terminate db connection
$conn = null;
脚本插入到:
//prepare variables
//measurements: 'm' for metric, 'i' for inches
$measurements_data = 'i';
//brasizes: 'us' for USA/Canada, 'uk' for UK, 'aus' for Australia, 'eu' for Europe, 'bef' for Belgium/Spain/France
$brasizes_data = 'us';
//shoesizes: 'us' for USA/Canada, 'uk' for UK, 'eu' for Europe, 'jap' for Japan
$shoesizes_data = 'us';
// login and insert data into columns
try {
// connect to mysql and login
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $newUser, $newPass);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// sql to create insert data
$sql = "INSERT INTO cms_settings (measurements,brasizes,shoesizes) VALUES (:measurements,:brasizes,:shoesizes)";
//prepare the statement
$stmt = $conn->prepare($sql);
// execute statement
$stmt->execute(array(':measurements' => $measurements_data,
':brasizes' => $brasizes_data,
':shoesizes' => $shoesizes_data));
echo "CMS Setting Populated Successfully<br>";
}
// if shit goes wrong...
catch(PDOException $e)
{
// display error why
echo $sql . "<br>Error: " . $e->getMessage();
}
// terminate db connection
$conn = null;
答
在你的代码有拼错。行
CREATE TABLE cms_setings (
更改为
CREATE TABLE cms_settings (
干杯
似乎是一个错字:'cms_setings',而不是'cms_settings'。 – Federkun
@Leggendario - 你打我吧:D – Jesse
亲爱的主,现在我觉得很蠢哈哈。非常感谢!现在完美的工作^ _ ^令人难以置信的是,你可以在几小时和几小时盯着相同的代码时发现这样的微小错误... – Insomniaboy