Check if table column exists inside XML

[CMX_CMGSCCC]

I did a quick check if anyone has posted a HOW TO for this, but I didnt find any so I will post how I managed to check if a table column exists inside an XML product file.

You will replace the fields to the ones you are looking for as well as the table names too. The example below will be used in the next version of my vBSupport v0.5 inside the <installcode>

// check for ticket.categoryid field
if (!($categoryid = $db->query_first("SHOW COLUMNS FROM " . TABLE_PREFIX . "ticket LIKE 'categoryid'")))
{
	// alter the table to add the column categoryid
	$db->query_write("ALTER TABLE " . TABLE_PREFIX . "ticket ADD COLUMN categoryid INT UNSIGNED NOT NULL DEFAULT '0' AFTER ticketid");
}

The way it works is quite simple, it uses the MySQL commands SHOW COLUMNS, you specify the table you want to search inside where I have put 'ticket' above. Then inside the LIKE part, replace 'categoryid' with the field you are searching for.

If the field is NOT found, it will run the commands inside the braces. If it already exists, it will not run the code inside the braces.

Not sure if there is an easier way to do this, I looked to see if ALTER TABLE ADD COLUMN IF NOT EXISTS was available, but it doesnt seem to be.

If someone has an easier way, by all means post it here.

The reason I need to verify them is because I dont want the people upgrading to lose all of their tickets/replies by dropping the table and recreating it every time I release an upgrade.

-CMX