MySQL Not creating Table through PHP

Question

I'm currently working on a PHP/MySQL ranking system, but I've come into a problem with my CREATE TABLE statement.

Here's my code:

mysql_select_db("DB1");
$numrows = "SELECT COUNT( * ) FROM information_schema.tables WHERE table_schema =  'DB1'";
if($numrows > 1){
    if($numrows > 2){
        $table = rand(1, $numrows);
    }else{
        $table = rand(1, 2);
    }
}else{
    $table = rand(1, 1);
}
$checkForTable = mysql_query("SELECT 1 FROM $table LIMIT 1");
if($checkForTable){
    $query = "INSERT INTO $table (name,score) VALUES($name, $score)";
    $result = mysql_query($query);
    mysql_select_db("DB2");
    $query2 = "INSERT INTO Leaderboard (name,score) VALUES($name, $score)";
    $result2 = mysql_query($query2);
    mysql_select_db("DB1");
    if($result && $result2){
        echo "<h4 style='color:green;'>Your Score Has Been Inserted</h4><hr/>";
    }else{
        echo "<h4 style='color:red;'>We encountered an error while inserting your data </h4><hr/>";
    }
}else{
    $newtable = "CREATE TABLE $table (
        id bigint AUTO_INCREMENT NOT NULL,
        name varchar(255) NOT NULL,
        score bigint(20) NOT NULL,
        PRIMARY KEY('id')
    )";
    $result = mysql_query($newtable);
    if($result){

        $query = "INSERT INTO $newtable (name,score) VALUES($name,$score)";
        $result = mysql_query($query);
        mysql_select_db("DB2");
        $query2 = "INSERT INTO Leaderboard (name,score) VALUES($name, $score)";
        $result2 = mysql_query($query2);
        mysql_select_db("DB1");
        if($result && $result2){
            echo "<h4 style='color:green;'>Your Score Has Been Inserted</h4><hr/>";
        }else{
            echo "<h4 style='color:red;'>We encountered an error while inserting your data </h4><hr/>";
        }

    }else{
        echo "TableNotCreatedException: " . mysql_error();
    }
}

When I try out the code I get:

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 '1 ( id bigint AUTO_INCREMENT NOT NULL, name varcha' at line 1

I've been trying to figure this out for a while but I've had no luck. Please Help!

Rahul · Accepted Answer · 2016-01-23 20:10:08Z

1

That's because your $table variable contains a value 1 which you are using as table name and so your query becomes

CREATE TABLE 1(....

Per MySQL Documentation it says

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

Also, your quoting the column name as seen below

    PRIMARY KEY('id')

It should rather be

    PRIMARY KEY(`id`)

edited Jan 23, 2016 at 20:10

answered Jan 23, 2016 at 20:02

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

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

5 Comments

Rahul Over a year ago

@R.Harley, have you red the answer carefully. error is because you are using a digit as table name which not acceptable. Try using a different name. Read the documents.

User2001 Over a year ago

Ok @Rahul , I was getting some errors with the table existing so I changed it to CREATE TABLE IF NOT EXIST $table` (...)` and now I'm getting the 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 'EXIST 1` ( id bigint AUTO_INCREMENT NOT NULL, nam' at line 1`

User2001 Over a year ago

I've changed the CREATE TABLE $table to CREATE TABLE $table

User2001 Over a year ago

the backticks arent showing up but they're there ;)

User2001 Over a year ago

Ok, I've changed it to work now, thanks for the help!

Collectives™ on Stack Overflow

MySQL Not creating Table through PHP

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related