Add field if does not exist (mysql, php)
Hello,
The problem with my code is that some databases do not contain fields for "extra1Width" and "extra2Width".
This causes a database error.
How can I automatically ADD these fields if they do not already exist to avoid the error?
The value should be a smallint and should be 0 by default.
The problem with my code is that some databases do not contain fields for "extra1Width" and "extra2Width".
This causes a database error.
How can I automatically ADD these fields if they do not already exist to avoid the error?
The value should be a smallint and should be 0 by default.
$adminSetting .= '
extra1Width = ' . quote_smart($_POST['extra1Width']) . ',
extra2Width = ' . quote_smart($_POST['extra2Width']) . ',
$postupdate = 'UPDATE
cms_vars
SET
Item1 = ' . quote_smart($_POST['Item1']) . ','
.$adminSetting.'
Item2 = ' . quote_smart($_POST['Item2']) . ','
WHERE
id = 1';
$postupdateer = mysql_query($postupdate);
AielloJ
How many databases would you possibly need to make this upgrade to? You might want to consider the risks of a web / PHP application being able to change your database structure.
ASKER
I have about 70 databases. Alternatively I could just check to see if the fields exists before doing the query to avoid the error. How could I do that?
My first reccomendation would be to do the updates to all the databases if possible. If not, the easiest way to check if the field exists would be to do a SELECT * FROM the table with a rowlimit of 1. Then check for the existence of the column name in the recordset using the isset function. I consider this a definite second best solution to doing the database column updates if at all practical.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Should have left a comment...
You could turn the above into a function and call it to check for the columns that are needed before trying to use those columns.
As long as you specify the exact columns and do not depend on user input for the column names you should be safe. Never alter a table based on user input unless you are sure you know what that user input it. Only alter a database based on specific requirements.
You could turn the above into a function and call it to check for the columns that are needed before trying to use those columns.
As long as you specify the exact columns and do not depend on user input for the column names you should be safe. Never alter a table based on user input unless you are sure you know what that user input it. Only alter a database based on specific requirements.
just as an aside...
Altering a database in this way is a more common thing that one would expect. Many upgrades of applications perform these types of checks, especially those that do not require you to run an installation script to make the update.
If an installation/update script is provided then in many cases the database changes are done in the update. However, if an installation/update script is not used then in all likelihood, if database changes are included in the update, there could be many places where tables are altered to meet the new requirements of the application.
Security should always be considered when performing these changes to ensure that random alterations cannot occur due to user input.
Altering a database in this way is a more common thing that one would expect. Many upgrades of applications perform these types of checks, especially those that do not require you to run an installation script to make the update.
If an installation/update script is provided then in many cases the database changes are done in the update. However, if an installation/update script is not used then in all likelihood, if database changes are included in the update, there could be many places where tables are altered to meet the new requirements of the application.
Security should always be considered when performing these changes to ensure that random alterations cannot occur due to user input.