Jump to content
otakarw

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

Recommended Posts

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;

}

Share this post


Link to post
Share on other sites

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;
       }

Share this post


Link to post
Share on other sites

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

  • Like 2

Share this post


Link to post
Share on other sites

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 by Rain_xx (see edit history)

Share this post


Link to post
Share on other sites
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 by tijopromantia (see edit history)

Share this post


Link to post
Share on other sites

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 ""');
}

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More