Add MySQL column if not existing using PDO

Question

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?

You should avoid using reserved words (like WHERE) as column names. — user672739
– user672739, Commented Mar 30, 2011 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
– shanethehat, Commented Mar 30, 2011 at 19:48

Community · Accepted Answer · 2017-05-23 12:19:54Z

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

edited May 23, 2017 at 12:19

CommunityBot

11 silver badge

answered Mar 30, 2011 at 18:34

sreimer

5,0082 gold badges36 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

shanethehat Over a year ago

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!

Collectives™ on Stack Overflow

Add MySQL column if not existing using PDO

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related