使用MySQL和PHP创建表

Question

我试图在索引页面上创建一个数据库，如果数据库不存在，它将自动创建数据库，然后创建一个表并向其中添加数据。

但是，我在创建表时遇到了问题，它总是会返回：

无法创建表

它创建数据库时没有任何问题，它只是引起问题的表。有人知道我哪里错了吗？

<?php

$servername = "localhost";
$username = "root";
$password = "admin";
$database = "jfitness";

// Create connection
$conn = mysqli_connect($servername, $username, $password);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Create database
$sql = "CREATE DATABASE IF NOT EXISTS '($database)'";
if (mysqli_query($conn, $sql)) {
    echo "Database created successfully";
} else {
    echo "Database already exists: " . mysqli_error($conn);
}

// make foo the current db
$db_selected = mysqli_select_db($conn, $database);
if (!$db_selected) 
    {
    die ('Can\'t use database : ' . mysqli_error());
}
else
{
    echo "Database Selected ";
}

// sql to create table
$sql = "CREATE TABLE customers( ".
       "id INT NOT NULL AUTO_INCREMENT, ".
       "name VARCHAR(40) NOT NULL, ".
       "password VARCHAR(50) NOT NULL, ".
       "email VARCHAR(50 NOT NULL), ".
       "PRIMARY KEY ( id )); ";

$retval = mysqli_query($conn, $sql );
if(! $retval )
{
  die('Could not create table: ' . mysqli_error());
}
echo "Table created successfully\n";

$sql = "INSERT INTO customers (username, password, email)
VALUES ('John', 'Doe', 'john@example.com')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

mysqli_close($conn);

?>

Answer 1

在create内的length声明之外取出NOT NULL：

email VARCHAR(50 NOT NULL) // its inside the length

其次，您有一个列不匹配：

INSERT INTO customers (username,    // username/name
name VARCHAR(40) NOT NULL

把它变成这样：

CREATE TABLE customers( 
    id INT NOT NULL AUTO_INCREMENT, 
    username VARCHAR(40) NOT NULL, // username not name
    password VARCHAR(50) NOT NULL, 
    email VARCHAR(50) NOT NULL, // put it after that
    PRIMARY KEY (`id`)
)

你有一个孤独的die('Could not create table: ' . mysql_error());。它不属于那里。

因此，总而言之：

$servername = "localhost";
$username = "root";
$password = "admin";
$database = "jfitness";

// Create connection
$conn = mysqli_connect($servername, $username, $password);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Create database
$sql = "CREATE DATABASE IF NOT EXISTS $database";
if (mysqli_query($conn, $sql)) {
    echo "Database created successfully <br/>";
} else {
    echo "Database already exists: " . mysqli_error($conn);
}

// make foo the current db
$db_selected = mysqli_select_db($conn, $database);
if (!$db_selected) 
    {
    die ('Can\'t use database : ' . mysqli_error($conn));
}
else
{
    echo "Database Selected <br/>";
}

// sql to create table
$sql = "CREATE TABLE customers( 
    id INT NOT NULL AUTO_INCREMENT, 
    username VARCHAR(40) NOT NULL, 
    password VARCHAR(50) NOT NULL, 
    email VARCHAR(50) NOT NULL,
    PRIMARY KEY (`id`)
)";


$retval = mysqli_query($conn, $sql );
if(! $retval ) {
    die('Could not create table: ' . $conn->error);
}

echo "Table created successfully<br/>";

$sql = "INSERT INTO customers (username, password, email) VALUES ('John', 'Doe', 'john@example.com')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully <br/>";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

mysqli_close($conn);

Answer 2

请按一行写着：

email VARCHAR(50 NOT NULL)

并将其改为：

email VARCHAR(50) NOT NULL

Answer 3

这是sql语句中的一个错误

在这一行：

"email VARCHAR(50 NOT NULL), ".

试试这个：

"email VARCHAR(50) NOT NULL, ".