Best way to code creating a table

PHP
1

In my script I want to create table which makes sense on the first run of the script but on any sequential run table should not be created since the first time run created it. what os the best way to do this. as of now I have it like this

$sql = "CREATE TABLE IF NOT EXISTS My_Table (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR (10) NOT NULL,
    ...........
    )";

but I also see in some tutorials showing

$query = "SHOW TABLES LIKE '$table_name'";
$result = mysqli_query($conn, $query);
if (mysqli_num_rows($result) == 0) {
.......

How is this commonly done?

2

You should separate table creation and usage. Table creation should normally not be done by any script at all but as a direct sql file containing the commands to create your database structure.

3

you mean by directly executing sql statements on the server or have the command in separate file?

4

Same file you get when you do a dump with mysqldump. Only other way around

1 Like
5

Honestly I see no reason you can’t have the code to create tables like you show there. What is more important is that you keep such code together and abstracted away from the rest of the codebase. In other words, try to put such code into its own module or class that other code can use it without knowing it is creating SQL statements for the tables. (Look at the database repository pattern)

I say there should be no reason you can’t do something like this because there are many systems (aka things like WordPress for instance) where you need to create custom tables for a plugin and you would normally create those tables through the WPDB class. You would also do such code in things like migrations in Laravel etc.

Now what you don’t want to be doing is building those SQL statements all over your code base because it will be hard to maintain and track down.

What I find also useful is that near where you create tables, you could put your SQL to tear them back down (drop tables). In fact Laravel has this concept in place with their migrations. An up() method to execute building of tables and a down() method for dropping them. :slight_smile:

6

this table is going to be there and I don’t have need to drop it

7

Famous last words! :wink:

8

Is there a need for the table to be created separately from the database? Normally all tables for a database would be created at the same time. For those situations, if the database does not exist then it is created with all tables and potentially some seed data.

If this was for an application that runs in multiple locations (companies perhaps) then it would make sense for there to be something that upgrades the database for the instance of the application. If this application is a website that exists in just one server then it seems that creation of the table could be done as part of the deployment of the new version.

1 Like