1

I know very little SQL, but found a command for SQL Server to add a column to the database if it doesn't already exist. Sadly it doesn't work when executed against my MySQL database, returning a syntax error.

$query = $dbh->prepare("if not exists (select * from syscolumns where id=object_id(':table_name') and name='where') alter table :table_name add where int(2)");

if($query->execute(array(':table_name'=>'registrations'))) {
    //twist and shout
} else {
    print_r($query->errorInfo());   
}

So what should I change to create the column 'where int(2)' if it doesn't exist?

shanethehat
  • 15,460
  • 11
  • 57
  • 87
  • why do you need dynamically create a column? – Your Common Sense Mar 30 '11 at 18:37
  • 1
    You should avoid using reserved words (like WHERE) as column names. –  Mar 30 '11 at 18:41
  • Excellent point, I need to change that to something safer and more descriptive. I have do this via a PHP script because it's the only way I know how with the level of access I have, I can't access the database server directly. – shanethehat Mar 30 '11 at 19:48

1 Answers1

1

You would have to create a stored procedure to handle this within one statement. This was discussed here: add column to mysql table if it does not exist

Community
  • 1
  • 1
sreimer
  • 4,913
  • 2
  • 33
  • 43
  • Just do it and catch the resulting error. It's not how I like to work, but it's late and this needs a fix, so thank you! – shanethehat Mar 30 '11 at 19:51