[SOLVED] Automatically add a column to a table if it's missing?

[tfburges]

Suppose the error message says...

 

"MYSQL error: Unknown column 'address1' in 'field list'"

 

...address1 should then be added to the table.  Is there a relatively simple way to do this?

[wildteen88]

To add a column to an existing table you'd ran an ALTER TABLE query.

[.josh]

er.. I'm kind of curious as to why you would try to send a query with an unknown column and then creating it based on an error message, versus checking whether it exists in the first place?

[JasonLewis]

You shouldn't really have to do this, should you? As Crayon Violent said, you should know what columns are in a table prior to inserting data into them.

[tfburges]

It's complicated... really complicated.  I have my reasons for wanting to do this.  I'm making a form creator and I'd like to use it as a quick fix for little bugs and glitches that keep unexpectedly popping up.  I know it would be better to just get all my logic aligned beforehand but for right now, adding another few clock cycles to the process isn't going to hurt much as long as it all still works.

 

I know how to use the alter table query.

 

I need to know how to retrieve the "unknown column" and alter the table automatically as soon as the error occurs.

 

I suppose I could do something like...

$query_column = "SELECT `$column_name` FROM `table`";
$result_column = mysql_query($query_column, $link) or die ('MYSQL error: ' . mysql_error());
if (mysql_num_rows($result_column) > 0) {
} else {
mysql_query("ALTER TABLE `table` ADD COLUMN `$column_name` varchar(50)");
}

[.josh]

okay but first off, if your query returns an error because of an unknown column, your die is going to stop the rest of your script, so you aren't going to get a chance to add it.  If you really want to go that route, use a trigger_error instead, so that your script will continue to execute. 

 

But if you can write that script there, why not just check if the column exists and make it if it's not? For instance, you can do this:

 

$sql = "SHOW COLUMNS FROM table";
$result = mysql_query($sql); 

$list = mysql_fetch_assoc($result);
if (in_array($column_name, $list)) {
  // it's there, do something
} else {
   // it's not there, add it
}

[tfburges]

Ahhh cool!  "in_array" is yet another function I was not aware of.  Thanks!

[tfburges]

Oh and I hope you'll forgive my ignorance.  I'm more of a C++ guy.  I'm relatively new to php but I do know that it's very powerful.

[.josh]

well if you know c++ then you'll feel right at home with php as it was written and (loosely) styled after c++.  You can even write your own php extensions in c++.