Creation of tables with Database scripts

Asked

Viewed 456 times

5

In my application I have to create a table for each user, tables are created the first time when the user uploads a file and with the name table_$id_user. And the only way I see to create so 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’ve seen a lot of things over the years and I’ve chosen to do it this way, without having much idea if I’d be doing it the right way.

It’s right to do it this way?

What problems can arise when creating tables like this?

There’s another way to do this?

I was left with many questions about how to program in PHP when reading this answer.

php mysql
  • 1

    I do not want to go into the basis of the opinion, but rather the basis that the code thus is susceptible to problems?

  • 1

    It seems to me that this is a Code Review then. I don’t remember how the status of that is there in [meta]... And it seems to me also that there are 4 questions embedded in one, leaving the question Excessively broad... but I will abstain from voting... many doubts ;)

  • 1

    I think to answer that I need to ask: what advantages you Do you see the method you use today? Because at first glance it seems simpler to delete PHP and simply run SQL, either in Workbench or another tool. I personally use the command line for this: mysql --user=root --password=blabla nome_da_base < arquivo.sql.

2 answers

6


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

One obvious problem that occurs to me is having to escape all the double quotes (") within your SQL statements (for example, in a string being inserted in a table you just created).

Is there any standard for this?

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

It would be best practice to do otherwise?

This I do not know and seems opinionated to me. The term "best practice" 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 one table per user structure, and use a single table with a column usuario_id.

  • 1

    I’m looking at this question as "I do this; in what other ways can I do it, and how do they compare to what I use today?" This can be answered without opinions, even though the question has a side that can lead people to opine (for example, speak in "correct form" and "best practice").

  • I edited the question, if you want to change the answer.

  • @Jorgeb. I added an observation at the end.

1

It is not good practice that you are creating a table for each user! The right one 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 guess there’s no better way than the way you did it yourself.

  • 1

    You’re giving the answer based on your opinion and not facts. Workbench why? You didn’t answer the question.

  • You’re telling me the advantages of using this tool. That’s not what I want to know, because I already know. What I want to know is why not use it the way I use it, is there any more suitable way? Read this question and the answer, is more or less what I want to know.

  • I edited the question, if you want to change the answer.

  • 1

    @Jorgeb. The biggest problem with that question, in my humble opinion, is not that it’s based on opinions, but it’s unclear what you really want. I don’t even think I should have responded, actually. =)

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.