PHP: How to check if there is a field in the table, and if it does not exist, add

Question

<?php

$servername = "servername ";
$database   = "database   ";
$username   = "username   ";
$password   = "password   ";

$conn       = new mysqli($servername, $username, $password, $database);

if ($conn->connect_error) 
{
  die("Falha na Conexão: " . $conn->connect_error);
}

//echo "Conexão feita com sucesso. <br />";

$sql = ("SHOW TABLES");
$query = $conn->query($sql);
while($row = $query->fetch_array(MYSQLI_NUM)){
    $rows[] = $row;
}

foreach($rows as $r){
    $query = ("SHOW COLUMNS FROM {$r[0]}");
    $search     = $conn->query($query);

    $created    = false;
    $updated    = false;
    $deleted    = false;
    
    while($row = $search->fetch_array(MYSQLI_NUM)){
        $ro[] = $row;
    }

    foreach($ro as $find) {
        if($find[0]  == 'created_at') {
            $created = true; 
        }
            
        if($find[0]  == 'updated_at' ) {
            $updated = true; 
        }
            
        if($find[0]  == 'deleted_at') {
            $deleted = true; 
        }
    }

    if($created == false) {
        //echo 'created </br>';
        $sql    = ("ALTER TABLE {$r[0]} ADD created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP");
        $conn->query($sql);
        $created = true; 
    }

    if($updated == false) {
         //echo 'updated </br>';
         $sql    = ("ALTER TABLE {$r[0]} ADD updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP");
         $conn->query($sql);
         $updated = true; 
    }

    if($deleted == false) {
        //echo 'deleted </br>';
        $sql    = ("ALTER TABLE {$r[0]} ADD deleted_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP");   
        $conn->query($sql);     
        $deleted = true; 
    }
}

$conn->close();

?>

Answer 1

You can do it this way:

$sql =  ("IF NOT EXISTS( SELECT NULL
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = 'NOME DA TABELA'
        AND table_schema = 'NOME DO BANCO'
        AND column_name = 'NOME DA COLUNA')  THEN

  ALTER TABLE tabela ADD created_at NOT NULL CURRENT_TIMESTAMP
  ADD updated_at NOT NULL CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
  ADD deleted_at NULL
END IF;");