Unable to create table using php script

Question

This is one of my script that I was working on but I just can't figure out what's wrong.

$dbhost = "localhost";
$dbusername = "root";
$dbpassword = "";
$dbname = "str_academy";

$connect = mysqli_connect("$dbhost", "$dbusername", "$dbpassword");

if (!$connect) {
   echo("<div class='container'>");         
   echo("<div class='alert alert-danger'><strong>Warning! </strong>Couldn't create the         specified database</div>");
   echo("</div>");;
exit();
}else {
   echo("<div class='container'>");
   echo("<div class='alert alert-success'><strong>Success! </strong>Database connection established successfully</div>");
   echo("</div>");;
}

if ($connect) {
   $database_create = 'CREATE DATABASE str_academy';
   $database_query = mysqli_query($connect, $database_create);
if (!$database_query) {
echo("<div class='container'>");            
    echo("<div class='alert alert-danger'><strong>Warning! </strong>Couldn't create the specified database</div>");
    echo("</div>");
    exit();         
}else {
    echo("<div class='container'>");
    echo("<div class='alert alert-success'><strong>Success! </strong>Database : str_academy successfully created</div>");
    echo("</div>");
}
}

$database_select = mysqli_select_db($connect, "str_academy");

if (!$database_select) {
   echo "Select a database first";
}else {
   echo "Database Selected";
}
$contact_table_create = "CREATE TABLE contact_information (
contact_id INT NOT NULL AUTO_INCREMENT,
contact_name VARCHAR(255) NOT NULL,
contact_email TEXT NOT NULL,
phonenumber INT(11) REAL NULL,
message TEXT NOT NULL,
PRIMARY KEY(contact_id)
)";
$contact_table_query = mysqli_query($connect, $contact_table_create);

if (!$contact_table_query) {
   echo "Table not created";
}else {
   echo "Table created";
}   

?>

I've created this simple test script. Everything seems to be running perfectly except that it is not creating the table contact_information. Can anyone help me solve this problem?

@Jocelyn It will complain about some syntax error near near 'REAL :-) But yes, that's the way to get into the debugging of scripts. — VMai
– VMai, Commented Jul 15, 2014 at 18:00
When I try this: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REAL NULL, message TEXT NOT NULL, PRIMARY KEY(contact_id) )' at line 5. Going to postulate that this is something to do with your usage of the REAL keyword — esqew
– esqew, Commented Jul 15, 2014 at 18:01
change phonenumber INT(11) REAL NULL, to phonenumber INT(11) NOT NULL, and see if it works — Grasper
– Grasper, Commented Jul 15, 2014 at 18:05

esqew · Accepted Answer · 2014-07-15 18:07:42Z

1

The keyword REAL used in this context is throwing a syntax error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REAL NULL, message TEXT NOT NULL, PRIMARY KEY(contact_id) )' at line 5

Removing REAL from the query (and, if necessary, replacing it with something else) should fix the problems you're having.

answered Jul 15, 2014 at 18:07

esqew

44.9k28 gold badges139 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Grasper · Accepted Answer · 2014-07-15 18:08:43Z

1

change phonenumber INT(11) REAL NULL, to phonenumber INT(11) NOT NULL, and see if it works

answered Jul 15, 2014 at 18:08

Grasper

1,31512 silver badges27 bronze badges

Comments

S.Pols · Accepted Answer · 2014-07-15 18:11:10Z

0

In phpmyadmin / innoDb REAL becomes double so i think you need to replace REAL for double DEFAULT. Your query will be like:

CREATE TABLE contact_information (
    contact_id INT NOT NULL AUTO_INCREMENT,
    contact_name VARCHAR(255) NOT NULL,
    contact_email TEXT NOT NULL,
    phonenumber double DEFAULT NULL,
    message TEXT NOT NULL,
    PRIMARY KEY(contact_id)
)

answered Jul 15, 2014 at 18:11

S.Pols

3,4592 gold badges24 silver badges44 bronze badges

1 Comment

S.Pols Over a year ago

As the comments above shown you can also use INT(11) instead of double DEFAULT

Orion_Blackstock · Accepted Answer · 2014-07-15 18:30:56Z

0

The error is at the column phonenumber INT(11) REAL NULL. This should be phonenumber INT(11) NOT NULL

answered Jul 15, 2014 at 18:30

Orion_Blackstock

1894 bronze badges

Comments

Samir Patel · Accepted Answer · 2014-07-15 18:02:15Z

-1

Output the mysqli error message. I suspect the user "root" may not have the CREATE DATABASE privileges.

if (!$database_query) {
    $error = mysqli_error($connect);
    echo("<div class='container'>");            
    echo("<div class='alert alert-danger'>{$error}</div>");
}

answered Jul 15, 2014 at 18:02

Samir Patel

9237 silver badges9 bronze badges

1 Comment

Korvo Over a year ago

Although they have given negative vote, I agree with the part of the privileges. Recommend that this type of response you keep in the comments. :)

Collectives™ on Stack Overflow

Unable to create table using php script

5 Answers 5

Comments

Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related