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

[Andreas]

Hmm, I just fire off ALTER TABLE Queries without bothering if it does exist or not.
If it does mySQL will return an Error, but who cares ^.^

You could also use Class vB_Database_Alter_MySQL (probably the preferred and best way).
(The Reason I didn't so far is that it wasn't wroking in the Beta Versions, but seems like the Code has been fixed since RC1 )

[CMX_CMGSCCC]

Ok, thanx.

I was wondering if it found an MySQL error if it would finish the rest of the queries or not?

-CMX