ALTER Table ADD Column IF NOT EXISTS (Possible?)

[phpstuck]

I am trying to figure out if I can ALTER a table and add a COLUMN where the condition is IF NOT EXISTS... I can create a whole new table that way. But can I ALTER an already existing table and add the new column's if they don't already exist. If they already exist then just skip it and move on.

 

This is for an update I am providing to quite a few people who are already running this application on their own computers using a localhost address.

 

Here is what I use for creating the table IF NOT EXISTS

 

$query = "CREATE TABLE IF NOT EXISTS `book` (
  `id` int(100) NOT NULL AUTO_INCREMENT,
  `upc` varchar(100) NOT NULL,
  `title` varchar(200) NOT NULL,
  `author` varchar(100) NOT NULL,
  `publisher` varchar(100) NOT NULL,
  `pages` varchar(100) NOT NULL,
  `genre` varchar(200) NOT NULL,
  `rating` varchar(100) NOT NULL,
  `notes` longtext NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;";

 

Say I want to add a column named "grouping" (`grouping` varchar(200) NOT NULL,) to a table that already exists... Is this possible. I don't want to generate an error and have to handle it later with more code.

[straylight]

I don't think that's possible by purely using SQL, at least not in MySQL. You could however try doing a "SHOW COLUMNS" and then use the results and PHP to figure out which tables you want to do your ALTER TABLE on.

[fenway]

Why would you be adding columns DYNAMICALLY?!?!?

[phpstuck]

Because it keeps people from having to add them manually. This application runs on computers not on the Internet. People serve it from their own computers running a PWS (xampp -Apache, MySQL - PHP) When I create updates it is easy to create a new table IF NOT EXISTS for new sections, on rare occasions I might need to add a column to a table that already exists.

[fenway]

Wait -- you're adding tables dynamically too?

[phpstuck]

Actually I figured it out using a simple PHP / MySQL test!

 

<?PHP

include_once 'db.php';

$sql=mysql_query(
        "SELECT groccat FROM book");

if (!$sql){

mysql_query("ALTER TABLE book ADD groccat VARCHAR(60) NOT NULL AFTER notes");


echo 'groccat created';

}ELSE{

//from here just continue the page as usual!

echo 'groccat already exists!';

}

?>