i have a Table class that has a createNew function which automatically creates my own defined tables, it checks the existing tables in the database and keeps creating them until the desired number is reached. I need the function to check the total number of columns and change them automatically in the database or when i reload the page once i do anything to alter them
//this is the class, i have only include two tables for the purpose of clarity
include_once 'Database.php';
/**
* create all tables
*/
class Table
{
private $stmt;
private $default;
private $sql;
private $tables;
private $nw_table_names;
private $table_num_query;
private $table_num_result;
private $table_num;
//by default all prefixes are lowercase
private $DB_PREFIX = 'ht';
/**
* @param create table function
*/
public function createNew()
{
$database = new Database();
$conn = $database->connect();
//check table number
$this->table_num_query = 'SHOW TABLES in haron_taiko';
$this->stmt = $conn->prepare($this->table_num_query);
$this->stmt->execute();
$this->table_num_result = $this->stmt->get_result();
$this->table_num = $this->table_num_result->num_rows;
if (!$this->table_num) {
//drafts table
$drafts_table =
'
CREATE TABLE ' . $this->DB_PREFIX . '_drafts(
post_id int(11) AUTO_INCREMENT PRIMARY KEY NOT null,
post_title varchar(120) NOT NULL,
post_body LONGTEXT NOT NULL,
post_category TEXT NOT NULL,
post_image varchar(256) NOT NULL,
date_added varchar(64) NOT NULL
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
';
//review table
$review_table =
'
CREATE TABLE ' . $this->DB_PREFIX . '_review(
post_id int(11) AUTO_INCREMENT PRIMARY KEY NOT null,
post_title varchar(120) NOT NULL,
post_body LONGTEXT NOT NULL,
post_category TEXT NOT NULL,
post_image varchar(256) NOT NULL,
date_added varchar(64) NOT NULL
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
';
//CREATE TABLE STATEMENTS
$this->tables = [$drafts_table, $review_table];
$this->nw_table_names = ['' . $this->DB_PREFIX . '_drafts', '' . $this->DB_PREFIX . '_review'];
//default execution first
foreach ($this->nw_table_names as $nw_table) {
$this->default = 'DROP TABLE IF EXISTS ' . $nw_table . '';
$this->stmt = $conn->prepare($this->default);
$this->stmt->execute();
}
foreach ($this->tables as $k => $this->sql) {
$this->stmt = $conn->prepare($this->sql);
$this->stmt->execute();
}
} else {
//run null->optimizes loading for home page
}
}
}
so two tables drafts
and review
are created once i instanciate the class in the home page like so
$tables = new Table();
$tables->createNew();
but if i were to alter or add a new column in the drafts table and reload the home page, in my database in phpmyadmin
, the columns have not changed, i need a function that automatically changes the column names in the table if i manually alter any column of any table without altering the table manuallly in phpmyadmin or writing an ALTER TABLE script each time i need to change or add a column in a table