adding a column if it does not exists otherwise skip adding the column

[bkeep]

I have been looking and have found some ideas but nothing I tried has worked as of yet.

The situation is I parse a text file and explode it for each query at the ; this works great. The issue I am having is once I run the update sql file I get an error if I run it again.

I want to be able to use something like IF NOT EXISTS when I try and alter the table column so I don't get the sql error

ALTER TABLE `listings` ADD `pending_removal` SMALLINT( 1 ) NOT NULL DEFAULT '0' AFTER `featured` ;

Best Regards,
Brandon

[eeek]

You can't just ignore the error?

[bkeep]

No this is for an install/upgrade script.

I have created an open source script that sometimes I release an add-on before it gets rolled into the script as part of the core release. If the add-on was added before the version upgrade is released the event of the error stops the rest of the file from being parsed.

I want to be able to continue adding other columns even if one of the columns exists.

[coopster]

You should set a version number and check it first. If the current installed version is greater than or equal to your upgrade version you know the column exists.

Another way to check for the existence is to run a table description query to view column definitions. If the database you are using does not have this feature available (which I cannot imagine as it is quite common) you can always do a SELECT * and LIMIT your result set to a single row, loop over the returned columns and check for your column name.

[bkeep]

the version number won't help as there could be any number of add-ons with versions and columns also some of the add-ons aren't exactly modular they may be some additional codes that needs additional data stored in the db.

I am also wanting to leave the php out of this if possible I can do this with out any issue using a scripting language I am wanting a sql only solution. think of performing this action from a mysql> prompt

So a new question, if I get a result from a query?

SHOW COLUMNS FROM listings WHERE Field = 'test';

if the column is there I get a result if it is not there I get an empty set how do I check for an empty set and then do somthing otherwise do something else

[bkeep]

Thanks for all of your help but I just did it through php
I searched and search thinking this had to be something so easy but alas nope I found out this is a feature request not due until v6 mysql