• 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?

Jaxel

Well-known member
#1
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'");
 

Jaxel

Well-known member
#2
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?
 

Jaxel

Well-known member
#3
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;
	}
 

Kier

XenForo Developer
Staff member
#4
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);
}
 

Lawrence

Well-known member
#6
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();

    }
 

Jeremy

Well-known member
#7
And Kier, I stole your code for BBCode Manager. Thanks for that. *runs and hides, waiting for ninja awesomeness attacks to come*