mysql创建表如果不存在,创建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.

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 "

Connected to MySQL

";

$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 "

Connected to Database '$db'

";

$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 "

" . $table . " table does not exist

";

$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 "

" . $table . "table exists

";

} // else

?>

解决方案

A few things.

There was a missing semi-colon ; in and at the end of )"

if(empty($result)) {

echo "

" . $table . " table does not exist

";

$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

)" //

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.

Plus, you were not using mysql_query in your table creation.

Here is a mysqli_ method, where I commented out your original codes.

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.

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 "

Connected to MySQL

";

$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 "

Connected to Database '$db'

";

$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 "

" . $table . " table does not exist

";

$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 "

" . $table . "table exists

";

} // else

?>