1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Add Column If Not Exists?

Discussion in 'XenForo Development Discussions' started by Jaxel, Nov 22, 2010.

  1. Jaxel

    Jaxel Well-Known Member

    Is there a correct procedure to add columns in install scripts? Just running the code below will create errors after subsequent upgrades, since the column already exists:
    Code:
    $db->query("ALTER TABLE `EWRmedio_media` ADD `thread_id` int(10) unsigned NOT NULL DEFAULT '0'");
     
  2. Jaxel

    Jaxel Well-Known Member

    I got the idea to do this:
    Code:
    if (false === $db->query("SELECT `thread_id` FROM `EWRmedio_media` LIMIT 0"))
    {
    	$db->query("ALTER TABLE `EWRmedio_media` ADD `thread_id` int(10) unsigned NOT NULL DEFAULT '0'");
    }
    Will this work?
     
  3. Jaxel

    Jaxel Well-Known Member

    I came up with a solution...

    Code:
    	public function addColumnIfNotExist($db, $table, $field, $attr)
    	{
    		$exists = false;
    		$columns = $db->fetchAll("SHOW columns FROM `".$table."`");
    
    		foreach ($columns AS $column)
    		{
    			if ($column['Field'] == $field)
    			{
    				$exists = true;
    				break;
    			}
    		}
    
    		if (!$exists)
    		{
    			$db->query("ALTER TABLE `".$table."` ADD `".$field."` ".$attr);
    		}
    
    		return true;
    	}
     
  4. Kier

    Kier XenForo Developer Staff Member

    How about this?
    PHP:
    public function addColumnIfNotExist($db$table$field$attr)
    {
        if (
    $db->fetchRow('SHOW COLUMNS FROM ' $table ' WHERE Field = ?'$field))
        {
            return;
        }
        
        return 
    $db->query('ALTER TABLE ' $table ' ADD ' $field ' ' $attr);
    }
     
    Darkimmortal and AlexandrosD like this.
  5. Jaxel

    Jaxel Well-Known Member

    I guess that works... and would be faster... thanks Kier.
     
  6. Lawrence

    Lawrence Well-Known Member

    Jaxel, you can also use your addons version id to call the appropriate function within your install class, one example:

    PHP:
        public static function install($existingAddOn$addOnData)
        {

            
    $start_at 0;
            
    $finish_at $addOnData['version_id'];

            if (
    $existingAddOn)
            {
                       
    // if the addon already exists, it has a version id, retrieve it and increase it by one for our new starting point

                
    $start_at $existingAddOn['version_id'];
                
    $start_at++;
            }


            for (
    $x $start_at$x <= $finish_at$x++)
            {


                switch (
    $x)
                {
                    case 
    0:
                                    
    // initial DB query here (or function call) as this is the first install
                   
    break;

                    case 
    4:
                                    
    // version updates 2 and 3 needed no DB changes, but version 4 does

                   
    break;
                    default:


                   break;
                }

            }
            
    // rebuild caches and all done

            
    XenForo_Model::create('XenForo_Model_ContentType')->rebuildContentTypeCache();

        }
     
  7. Jeremy

    Jeremy XenForo Moderator Staff Member

    And Kier, I stole your code for BBCode Manager. Thanks for that. *runs and hides, waiting for ninja awesomeness attacks to come*
     
  8. Kier

    Kier XenForo Developer Staff Member

    That was the idea behind posting it :)
     
    Jeremy likes this.

Share This Page