Issue creating a table with MySQL and PHP

Question

I am trying to create a database on the index page which will automatically create a database if it doesn't exist, which will then create a table and add data to it.

I am having an issue with creating the table though, it will always return:

could not create table

It creates the database with no problems, it's just the table that causing the issue. Anyone know where I'm wrong?

<?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);

?>

may be you need to re-connect to the newly created DB with a proper user and pass before creating a table in that DB. — Abhik Chakraborty, Nov 24 '14 at 13:23
In the database you have created using your script, are you able to create a table with PHPMyAdmin or similar tool? — Alexander, Nov 24 '14 at 13:26

Kevin · Accepted Answer · 2014-11-24T13:43:20.717

Take off the NOT NULL outside the length declaration inside the create table:

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

Second, you have a column mismatch:

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

Turn it into this:

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`)
)

And you have a lone die('Could not create table: ' . mysql_error());. It doesn't belong in there.

So all in all:

$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);

That was it, must have looked over this 50 times and never noticed that, cheers. Have to wait 3 minutes to select your answer — user2757842, Nov 24 '14 at 13:32

score 0 · Answer 2 · answered Nov 24 '14 at 13:27

0

Take the line that reads:

email VARCHAR(50 NOT NULL)

and change it to:

email VARCHAR(50) NOT NULL

answered Nov 24 '14 at 13:27

Len_D

1,422
1
12
21

score 0 · Answer 3 · answered Nov 24 '14 at 13:28

0

It's an error at your sql statement

at this line:

"email VARCHAR(50 NOT NULL), ".

try this:

"email VARCHAR(50) NOT NULL, ".

answered Nov 24 '14 at 13:28

Giwwel

347
1
4

score 0 · Answer 4 · answered Nov 24 '14 at 13:30

0

 "email VARCHAR(50 NOT NULL), ".

should be

 "email VARCHAR(50) NOT NULL, ".

answered Nov 24 '14 at 13:30

markt

903
7
21

score 0 · Answer 5 · edited Nov 24 '14 at 15:58

You've a syntax error in your table syntax where you defined email field. You can use below complete solution for your problem :

$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) === TRUE) {
    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, ".
       "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: ' . mysql_error());
}
echo "Table created successfully\n";

It's not clear what you've changed here - it's usually best to point out the error. In this case it is trivial, so it is even better just to vote to close the question. There is a specific close reason for this sort of thing. — halfer, Nov 24 '14 at 13:30
@halfer,Thanks for info. I have added comments in my answer.Please check updated answer. — , Nov 24 '14 at 13:35

Issue creating a table with MySQL and PHP

5 Answers5