CODEWITHSUNDEEP
phpmysqldatabasecreate-table
user1746391
user1746391

Reputation: 11

MySQL function Create Table gives me a Syntax error

I'm creating a login form, I created the database and now I'm creating the table with this function:

function CreateTable()
{
    $qry = "CREATE TABLE $this->tablename (
            'id' INT NOT NULL AUTO_INCREMENT ,
            'name' VARCHAR( 128 ) NOT NULL ,
            'email' VARCHAR( 64 ) NOT NULL ,
            'phone_number' VARCHAR( 16 ) NOT NULL ,
            'username' VARCHAR( 16 ) NOT NULL ,
            'password' VARCHAR( 32 ) NOT NULL ,
            'confirmcode' VARCHAR(32) ,
            PRIMARY KEY ('id')
            )";

    if(!mysql_query($qry,$this->connection))
    {
        $this->HandleDBError("Error creating the table \nquery was\n $qry");
        return false;
    }
    return true;
}

The problem is that every time I try to login to test the form I get this error:

Error creating the table query was CREATE TABLE ( 'id' INT NOT NULL AUTO_INCREMENT , 'name' VARCHAR( 128 ) NOT NULL , 'email' VARCHAR( 64 ) NOT NULL , 'phone_number' VARCHAR( 16 ) NOT NULL , 'username' VARCHAR( 16 ) NOT NULL , 'password' VARCHAR( 32 ) NOT NULL , 'confirmcode' VARCHAR(32) , PRIMARY KEY ('id') ) mysqlerror: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 '( 'id' INT NOT NULL AUTO_INCREMENT , 'name' VARC' at line 1

So obviously there's a syntax error on that line but as I have no experience in PHP I just can't see where the error is!

Any help very appreciated!

Upvotes: 0

Views: 1171

Answers (5)

Perception
Perception

Reputation: 80603

You are using quotes instead of back ticks. And yea, there's no tabe name being passed into the function it seems. Your function should look more like this:

function CreateTable($tableName)
{
    $qry = "CREATE TABLE IF NOT EXISTS $tableName (
            `id` INT NOT NULL AUTO_INCREMENT ,
            `name` VARCHAR( 128 ) NOT NULL ,
            `email` VARCHAR( 64 ) NOT NULL ,
            `phone_number` VARCHAR( 16 ) NOT NULL ,
            `username` VARCHAR( 16 ) NOT NULL ,
            `password` VARCHAR( 32 ) NOT NULL ,
            `confirmcode` VARCHAR(32) ,
            PRIMARY KEY (`id`)
            );";

    if(!mysql_query($qry,$this->connection))
    {
        $this->HandleDBError("Error creating the table \nquery was\n $qry");
        return false;
    }
    return true;
}

Upvotes: 2

Mr. Radical
Mr. Radical

Reputation: 1855

Do something like: 

function Insert($tableName)
{
$tableName = newtable;
$insert_query = "insert into $tableName( name, email, username, password, confirmcode ) values ("bob", "[email protected]", 911, "bob2013", "secrete", "1");";


    if(!mysql_query($qry,$this->connection))
    {
        $this->HandleDBError("Error inserting into the table \nquery was\n $qry");
        return false;
    }
    return true;
}

I don't know which code you have used for the insert, so I made some assumptions. Furthermore, it is basically another question.

Upvotes: 1

Johny
Johny

Reputation: 175

Why don't you just use HeidiSql to create your database and then make a connection you your form ???? it's much easier. And when using heidi you don't get any sql errors unless you write wrong php code for inserting,updating and deleting data into the database.

Upvotes: 0

helmbert
helmbert

Reputation: 38004

Try surrounding $this->tablename with brackets, i.e.

$qry = "CREATE TABLE {$this->tablename} ( ... )";

Furthermore, you cannot use singlequotes to quote field names (these usually quote strings in SQL). You should use backticks (`) instead.

Upvotes: 0

Prasanth Bendra
Prasanth Bendra

Reputation: 32740

table name is not came here

CREATE TABLE ( 'id' INT NOT NULL AUTO_INCREMENT , 'name' VARCHAR( 128 ) NOT NULL , 'email' VARCHAR( 64 ) NOT NULL , 'phone_number' VARCHAR( 16 ) NOT NULL , 'username' VARCHAR( 16 ) NOT NULL , 'password' VARCHAR( 32 ) NOT NULL , 'confirmcode' VARCHAR(32) , PRIMARY KEY ('id') )

It should be some thing like 

CREATE TABLE `table` ( 'id' INT NOT NULL AUTO_INCREMENT , 'name' VARCHAR( 128 ) NOT NULL , 'email' VARCHAR( 64 ) NOT NULL , 'phone_number' VARCHAR( 16 ) NOT NULL , 'username' VARCHAR( 16 ) NOT NULL , 'password' VARCHAR( 32 ) NOT NULL , 'confirmcode' VARCHAR(32) , PRIMARY KEY ('id') )

so check your $this->tablename

Upvotes: 0

Related Questions