Some handy PHP functions to create database table and add a column to it easily

I personally really like to do “make a table if not exists” and “add a column if not exists” to mysql database when working with PHP.

By doing that I don’t have to manually create table and add column before populating it with data from my website.

That’s why I’ve made this two functions to create a table and the other one to add a column to the table.

Let’s say we have a database connection variable which is $connection. Let’s say it’s set up already with correct database name, username, and password.

Then let’s make a variable for a table. I will add this variable:

$mynewtable = "newtable";

Then this is the first function to create a database table:

//Make table if not exists
function makeatable($connection, $table){
    mysqli_query($connection, "CREATE TABLE IF NOT EXISTS $table (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY
    )");
}

Then we can use that function to create a table with name “newtable” by doing this:

makeatable($connection, $mynewtable);

Or we can simply do this:

makeatable($connection, 'newtable');

If we execute this PHP program, it will make that table if it does not exist. If there is that table already, it will be skipped.

Then, we add a column to it if it not exists yet, by making this function below:

//Check column and make it if not exist
function makecolumn($connection, $columnname, $tablename, $ctype){
    if(!mysqli_query($connection, "SELECT $columnname FROM $tablename")){
        mysqli_query($connection, "ALTER TABLE $tablename ADD $columnname $ctype");
    }
}

This function takes 4 parameters, first one is the connection, second is column name string, third is the table that we did make earlier, and the last is a string to define the type of column we are making.

Let’s say we want to add “companyname” to our existing table “newtable”, so we call the function this way:

makecolumn($connection, "companyname", $mynewtable, "VARCHAR(500) NOT NULL");