0

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

Community
  • 1
  • 1

0 Answers0