Creating tables with database scripts

Question

Creating tables with database scripts

Navigation

#1 by (6 votes)
#2 by (1 votes)

5

In my application I have to create a table for each user, the tables are created the first time when the user uploads a file and with the name table_ $ id_user. And the only way I can see to create it dynamically is this:

$sql_create_table1 =
        "CREATE TABLE IF NOT EXISTS 'db'.'table1' (
        'id' INT NOT NULL AUTO_INCREMENT,
        'name' VARCHAR(50) NULL ,
                ...
        PRIMARY KEY ('id') )
        ENGINE = InnoDB
        DEFAULT CHARACTER SET = utf8
        COLLATE = utf8_unicode_ci;";

$mysqli->query( $sql_create_table1 );
...

I have seen a lot of things over the years and I chose to do so without having much sense if I was doing the right thing.

Is it okay to do this this way?

What problems can arise when creating tables?

Is there another way to do this?

I had a lot of questions about how to program in PHP by reading this answer .

php mysql

asked by anonymous 21.05.2014 / 17:19

2 answers

1

It is not good practice what you are doing to create a table for each user! The sure would be you create a single table with a foreign key pointing to the user table.

But if you insist on this way of doing it, I believe there is no better way than the way you did yourself.

21.05.2014 / 18:02

How to convert negative numbers from decimal to hexadecimal Problem with group by

score 6 · Accepted Answer

What kind of problems can arise with this method that I use?

An obvious problem that occurs to me is to have to escape all the double quotation marks ( " ) within your SQL statements (for example, in a string being inserted into a table that you just to create).

Is there a standard for this?

Not exactly, but you are packaging SQL as PHP strings, when you could simply write pure SQL, and run it on any client (Workbench, command line or other).

Would it be better practice to do otherwise?

This I do not know and seems to me opinionated. The term "best practice" by itself already displeases me. If it is to give an opinion, I would simply use pure SQL, without PHP, for simplicity.

Considering the last issue of the question: I would run away from this table structure per user, and would use a single table with a usuario_id column.