[Solved] Extending db. table "adress" with new columns - check existence of columns prior execution ALTER TABLE upon module installation

[otakarw]

I am creating module that will get required ID numbers for tax athority according to tax laws. Presta can save only 2 nubers Tax ID adn VAT ID. We need company register ID nr. Basically, I need to add column, but I want to add check if this column exist before executing ALTER TABLE:


This column should be created upon module instalation in backoffice with this code:

public function install()
   { 
   ...
/* here are all functions to show module in administration and to allow to install it */
   ...

/* Set database Db::getInstance()->Execute('… SQL QUERY …')  but missing check if coulmn EXISTS*/
   if (!Db::getInstance()->Execute('ALTER TABLE `'._DB_PREFIX_.'address` ADD `company_id` varchar(16) NULL AFTER `dni`')
           return false;

   }

How to modify following to work with prestashop classes

$sql=mysql_query("SELECT company_id FROM ps_adress");

if (!$sql){

mysql_query("ALTER TABLE ps_adress ADD company_id VARCHAR(16) NULL AFTER dni");

return false;

}

[otakarw]

After some thinking and searching I came to this

/* set database */

if (!Db::getInstance()->Execute('SELECT company_id from `'._DB_PREFIX_.'address`'))
       { 
           if (!Db::getInstance()->Execute('ALTER TABLE `'._DB_PREFIX_.'address` ADD `company_id` varchar(16) NULL AFTER `dni`'))
           return false;
       }

[vblanch]

I know this answer comes very, very late but the previous response is not correct. If you have MySQL warnings and errors enabled, this will disrupt the page from rendering in case the column doesn't exist, and in fact won't update the table.

 

I found a solution for this:

 

In your installation function you could do something like this:

public function install(){
    ...
    if(!$this->maybeUpdateDatabase()) return false;
}

Then the function to update your database is:

//function used to upgrade the module table
private function maybeUpdateDatabase(){
    $sql = 'DESCRIBE '._DB_PREFIX_.$this->table_name;        
    $columns = Db::getInstance()->executeS($sql);
    $found = false;
    foreach($columns as $col){
        if($col['Field']=='your_column'){
            $found = true;
            break;
        }
    }
    if(!$found){
        if (!Db::getInstance()->execute('
            ALTER TABLE `'._DB_PREFIX_.$this->table_name.'` ADD `your_column` text DEFAULT NULL'));
            return false;
    }
    return true;
}

The trick here is the MySQL command "Describe" which generates an array of arrays with the columns of the table. Then the key "Field" in every individual array indicates the name of the column. If not found, update the table and add the new column.

 

Hope this helps somebody, and sorry moderators to resurrect this

Cheers

[Rain_xx]

Itri

 

I know this answer comes very, very late but the previous response is not correct. If you have MySQL warnings and errors enabled, this will disrupt the page from rendering in case the column doesn't exist, and in fact won't update the table.

 

I found a solution for this:

 

In your installation function you could do something like this:

public function install(){
    ...
    if(!$this->maybeUpdateDatabase()) return false;
}

Then the function to update your database is:

//function used to upgrade the module table
private function maybeUpdateDatabase(){
    $sql = 'DESCRIBE '._DB_PREFIX_.$this->table_name;        
    $columns = Db::getInstance()->executeS($sql);
    $found = false;
    foreach($columns as $col){
        if($col['Field']=='your_column'){
            $found = true;
            break;
        }
    }
    if(!$found){
        if (!Db::getInstance()->execute('
            ALTER TABLE `'._DB_PREFIX_.$this->table_name.'` ADD `your_column` text DEFAULT NULL'));
            return false;
    }
    return true;
}

The trick here is the MySQL command "Describe" which generates an array of arrays with the columns of the table. Then the key "Field" in every individual array indicates the name of the column. If not found, update the table and add the new column.

 

Hope this helps somebody, and sorry moderators to resurrect this

Cheers

 

I tried that and still get error during the module installation.
In particular, if I use or not this code I get the same thing: if the field doesn't exist it gets to be created, if the field exist I get an error message.

 

 

I don't know if it's the case of who posted this thread but, I solved simply in this way:

$sql = 'SELECT MY_FIELD from '._DB_PREFIX_.'TABLE'; 
if ($results = Db::getInstance()->ExecuteS($sql))
   {
            return true;
    }
 else  {
        if (!Db::getInstance()->execute(
            'ALTER TABLE '._DB_PREFIX_.'TABLE ADD `MY_FIELD` varchar(16) NULL')) ;  
            return true;
    }

So, if the field already exists, the funcion do nothing and if the field doesn't exist it will be created.

Thank you.

Edited October 23, 2016 by Rain_xx (see edit history)

[tijojoel]

On 10/23/2016 at 7:36 PM, Rain_xx said:

Itri

 

 

I tried that and still get error during the module installation.
In particular, if I use or not this code I get the same thing: if the field doesn't exist it gets to be created, if the field exist I get an error message.

 

 

I don't know if it's the case of who posted this thread but, I solved simply in this way:

$sql = 'SELECT MY_FIELD from '._DB_PREFIX_.'TABLE'; 
if ($results = Db::getInstance()->ExecuteS($sql))
   {
            return true;
    }
 else  {
        if (!Db::getInstance()->execute(
            'ALTER TABLE '._DB_PREFIX_.'TABLE ADD `MY_FIELD` varchar(16) NULL')) ;  
            return true;
    }

So, if the field already exists, the funciondo nothing and if the field doesn't exist it will be created.

Thank you.

Here, the problem is when table has no column in it thenmysql query error will come.

Edited June 21, 2018 by tijopromantia (see edit history)

[Pedro Lima]

For someone else in the future that might need this, it worked for me simplifying like this:

 

if (!Db::getInstance()->execute('SELECT column_name from '._DB_PREFIX_.'table_name')) {
	Db::getInstance()->execute('ALTER TABLE '._DB_PREFIX_.'table_name ADD `column_name` char(32) NOT NULL DEFAULT ""');
}