如果不存在则创建mysql表 [英] create mysql table if it doesn't exist
问题描述
我在php/mysql上工作不多,但是我需要一个我认为相对简单的任务:检查一个表是否存在,如果不存在则创建一个表.我什至没有收到有用的错误消息,并且数据库中没有创建表.我的语法显然有问题.
I don't work much with php/mysql, but I need what I thought would be a relatively straightforward task: to check if a table exists and create it if it doesn't. I can't even get a useful error message and there's no table being created in the db. There is obviously something wrong with my syntax.
<?php
session_start();
error_reporting(E_ALL);
ini_set('display_errors', 1);
// 1. CONNECT TO THE DB SERVER, confirm connection
mysql_connect("localhost", "root", "") or die(mysql_error());
echo "<p>Connected to MySQL</p>";
$mysql_connexn = mysql_connect("localhost", "root", ""); // redundant ?
// 2. CONNECT TO THE SPECIFIED DB, confirm connection
$db = "weighttracker";
mysql_select_db($db) or die(mysql_error());
echo "<p>Connected to Database '$db'</p>";
$db_connexn = mysql_select_db($db)or die(mysql_error("can\'t connect to $db"));
// 3. if table doesn't exist, create it
$table = "WEIGHIN_DATA";
$query = "SELECT ID FROM " . $table;
//$result = mysql_query($mysql_connexn, $query);
$result = mysql_query($query, $mysql_connexn);
if(empty($result)) {
echo "<p>" . $table . " table does not exist</p>";
$query = "CREATE TABLE IF NOT EXISTS WEIGHIN_DATA (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
DATE DATE NOT NULL,
VALUE SMALLINT(4) UNSIGNED NOT NULL
)"
}
else {
echo "<p>" . $table . "table exists</p>";
} // else
?>
推荐答案
一些事情.
)"
和
if(empty($result)) {
echo "<p>" . $table . " table does not exist</p>";
$query = "CREATE TABLE IF NOT EXISTS WEIGHIN_DATA (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
DATE DATE NOT NULL,
VALUE SMALLINT(4) UNSIGNED NOT NULL
)" // <--- right there
可能会导致/引发解析错误,例如:
which would have caused/thrown a parse error, such as:
解析错误:语法错误,...中出现意外的'}'
Parse error: syntax error, unexpected '}' in...
在您最初发布的代码中我的评论中还显示了其他错误.
Amongst other errors as shown in my comments from your originally posted code.
此外,您在创建表时没有使用mysql_query
.
Plus, you were not using mysql_query
in your table creation.
这是一个mysqli_
方法,在这里我注释掉了您的原始代码.
Here is a mysqli_
method, where I commented out your original codes.
旁注:您正在对$query = "SELECT ID FROM " . $table;
中的列使用ID
,但是您将表和列创建为小写的id
;两个字母都必须匹配.
Sidenote: You're using ID
for your column in $query = "SELECT ID FROM " . $table;
and yet you create your table and column as id
in lowercase; both lettercase must match.
<?php
session_start();
error_reporting(E_ALL);
ini_set('display_errors', 1);
$DB_HOST = "xxx"; // put your own data
$DB_NAME = "xxx"; // put your own data
$DB_USER = "xxx"; // put your own data
$DB_PASS = "xxx"; // put your own data
$conn = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if($conn->connect_errno > 0) {
die('Connection failed [' . $conn->connect_error . ']');
}
/*
// 1. CONNECT TO THE DB SERVER, confirm connection
mysql_connect("localhost", "root", "") or die(mysql_error());
echo "<p>Connected to MySQL</p>";
$mysql_connexn = mysql_connect("localhost", "root", ""); // redundant ?
// 2. CONNECT TO THE SPECIFIED DB, confirm connection
$db = "weighttracker";
mysql_select_db($db) or die(mysql_error());
echo "<p>Connected to Database '$db'</p>";
$db_connexn = mysql_select_db($db)or die(mysql_error("can\'t connect to $db"));
// 3. if table doesn't exist, create it
$table = "WEIGHIN_DATA";
$query = "SELECT ID FROM " . $table; // that should be id and not ID
//$result = mysql_query($mysql_connexn, $query);
$result = mysql_query($query, $mysql_connexn);
*/
$table = "WEIGHIN_DATA";
$query = "SELECT ID FROM " . $table; // that should be id and not ID
//$result = mysql_query($mysql_connexn, $query); // your original code
// however connection comes last in mysql method, unlike mysqli
$result = mysqli_query($conn,$query);
if(empty($result)) {
echo "<p>" . $table . " table does not exist</p>";
$query = mysqli_query($conn,"CREATE TABLE IF NOT EXISTS WEIGHIN_DATA (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
DATE DATE NOT NULL,
VALUE SMALLINT(4) UNSIGNED NOT NULL
)");
}
else {
echo "<p>" . $table . "table exists</p>";
} // else
?>
这篇关于如果不存在则创建mysql表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!