SQL database table creation with variable

Question

I am working on a project, and I have to use sql. The variable $file_name needs to be the table name, but when i try this:

$sqlTableCreate = "CREATE TABLE ". $file_name . "( 
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP
)";

The table does not create. I checked by using this:

if ($sqlConnection->query($sqlTableCreate) === TRUE) {  
    echo 'Created Sucessfully';
} else {
    echo 'Table does not create.';
}

I get 'Table does not create' when trying to use this. Help would be greatly appreciated. Thanks in advance!

can you show how `$sqlConnection` is created? are you using `PDO` or `mysqli` — Alex Andrei, Jan 01 '16 at 12:49
You need to get the *actual* error from MySQL. It isn't clear which API you're using, so you'll have to look at the docs for that API to determine how to return real error messages. — Jay Blanchard, Jan 01 '16 at 12:50
There is not actual error from MySQL. It just doesn't create. I am using MySQLi — Teddy Codes, Jan 01 '16 at 12:51
Consult the docs [here](http://php.net/manual/en/mysqli.error.php). — Jay Blanchard, Jan 01 '16 at 12:53
what is the output if you add `print $sqlConnection->error;` to the `else` clause ? — Alex Andrei, Jan 01 '16 at 12:56
Can you check anyway, whether the table is created already? (If created once, subsequent queries will fail with error.) — Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ, Jan 01 '16 at 13:03
I have checked the database and there is nothing there besides ones that i have just recently checked. I will post the output of the errors in a minute. — Teddy Codes, Jan 01 '16 at 13:21
@Robert, that means you have made mistake while selecting database when creating the connection. Showing your codes for creating connection will be more helpful, — Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ, Jan 01 '16 at 13:27
@Barmar $file_name = 'currentScan.csv'; but it does change when the file is uploaded to the server. — Teddy Codes, Jan 01 '16 at 13:28
@Robert, that's could be the problem. You are trying to create a table named `currentScan.csv` which your DB engine thinking that `currentscan` is the DB name and `csv` is the table name which obviously doesn't exits. — Rahul, Jan 01 '16 at 13:29
so, how would i drop the .csv? i need to have the table called 'currentScan'. When i posted the error, it said `Unknown database 'correctscan'`. i am not exactly sure why it says database. — Teddy Codes, Jan 01 '16 at 13:30
@Robert 'currentScan.csv' will try to create a table named `csv` in db `currentScan`, so it wont work if there is any db named `currentScan` and you are properly connected to it. — Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ, Jan 01 '16 at 13:31
There is no database called currentScan. The database is called PrimeSelect. — Teddy Codes, Jan 01 '16 at 13:33

score 2 · Accepted Answer · edited May 23 '17 at 12:31

2

Your filename contains a extension, but I suspect you just want to use the name without the extension as the name of the table. You can use the basename function to remove the extension.

$sqlTableCreate = "CREATE TABLE ". basename($file_name, ".csv") . "( 
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP
)";

If there can be different extensions, and you want to remove them more generally, see

How to remove extension from string (only real extension!)

edited May 23 '17 at 12:31

Community

1
1

answered Jan 01 '16 at 13:32

Barmar

741,623
53
500
612

Need to make sure, if there is any other file type expected? – Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ Jan 01 '16 at 13:33
I've added a link to another question that shows how to remove extensions in general. – Barmar Jan 01 '16 at 13:36
There is not. There is only a .csv extension allowed to be uploaded to the site. Thanks! – Teddy Codes Jan 01 '16 at 13:37

Rahul · Answer 2 · 2016-01-01T13:31:34.197

I don't see any issue with your posted query but couple things may be wrong

Make sure that there is no table exists with that same name. You can use IF NOT EXISTS marker to be sure like

  CREATE TABLE IF NOT EXISTS". $file_name . "(

make sure that the variable $file_name is not empty. Else, you are passing a null identifier in CREATE TABLE statement; which will not succeed.

Per your comment: you have $file_name = 'currentScan.csv';

That's the problem here. You are trying to create a table named currentScan.csv which your DB engine thinking that currentscan is the DB name and .csv is the table name which obviously doesn't exits and so the error.

score -1 · Answer 3 · edited Jan 01 '16 at 12:53

-1

first check your database connection and change your query with given below :

$sqlTableCreate = "CREATE TABLE ". $file_name . " ( 
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";

edited Jan 01 '16 at 12:53

Jay Blanchard

34,243
16
77
119

answered Jan 01 '16 at 12:51

jilesh

436
1
3
13

The OP's query is fine, and I see no change here? – Jay Blanchard Jan 01 '16 at 12:53
My database connection is fine, but i am just not able to create the table. – Teddy Codes Jan 01 '16 at 12:53
Does the space matter? – Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ Jan 01 '16 at 13:05

SQL database table creation with variable

3 Answers3