Php Check If Column Exists Mysql

Worksheet will open in a new window. You can & download or print using the browser document reader options. Credit Basics Note Taking GuideShowing top 8 worksheets in the category - Credit Basics Note Taking Guide.Some of the worksheets displayed are Credit basics, Understanding credit cards, The basics of taxes, Personal financial workbook, Major expenditures, Work answer key, Introduction to credit, Introduction to investing answer key.Once you find your worksheet, click on pop-out icon or print icon to worksheet to print or download.

Is there a way to check if a column exists in a mySQL DB prior to (or as) the ALTER TABLE ADD coumn_name statement runs? Sort of an IF column DOES NOT EXIST ALTER TABLE thing.

This is a PHP function to check if a specific INDEX (or key) exists in a MySQL table. It takes 3 parameters, which are: $link This is the MySQL connection object which is made, for example, by using mysqliconnect or mysqliinit. $table The name of the table that you want to check.

I’ve tried ALTER IGNORE TABLE my_table ADD my_column but this still throws the error if the column I’m adding already exists.

EDIT: use case is to upgrade a table in an already installed web app– so to keep things simple, I want to make sure the columns I need exist, and if they don’t, add them using ALTER TABLE

Do you think you can try this?:

It’s no one-liner, but can you at least see if it will work for you? At least while waiting for a better solution.

Since mysql control statements (e.g. “IF”) only work in stored procedures, a temporary one can be created and executed:

Utility functions and procedures

First, I have a set of utility functions and procedures that I use to do things like drop foreign keys, normal keys and columns. I just leave them in the database so I can use them as needed.

Here they are.

Dropping constraints and columns using the utilities above

With those in place, it is pretty easy to use them to check columns and constraints for existence:

Make a count sentence with the example below by John Watson.

Save that result in an integer and then make it a condition to apply the ADD COLUMN sentence.

Although its quite an old post but still i feel good about sharing my solution to this issue. If column doesn’t exist then an exception would occur definitely and then i am creating the column in table.

I just used the code below:

You can create a procedure with a CONTINUE handler in case the column exists (please note this code doesn’t work in PHPMyAdmin):

This code should not raise any error in case the column already exists. It will just do nothing and carry on executing the rest of the SQL.

You can test if a column exists with:

Just fill in your column name, table name, and database name.

As per MYSQL Community:

IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only one row is used of rows with duplicates on a unique key. The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.

So a working Code is:
ALTER IGNORE TABLE CLIENTS ADD CLIENT_NOTES TEXT DEFAULT NULL;

Data posted here:
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

Tags: mysql, sql

The COLUMNS table provides information about columns in tables. The related ST_GEOMETRY_COLUMNS table provides information about table columns that store spatial data. See Section 25.34, “The INFORMATION_SCHEMA ST_GEOMETRY_COLUMNS Table”.

The COLUMNS table has these columns:

• TABLE_CATALOG

  The name of the catalog to which the table containing the column belongs. This value is always def.

• TABLE_SCHEMA

  The name of the schema (database) to which the table containing the column belongs.

• TABLE_NAME

  The name of the table containing the column.

• COLUMN_NAME

  The name of the column.

• ORDINAL_POSITION

  The position of the column within the table. ORDINAL_POSITION is necessary because you might want to say ORDER BY ORDINAL_POSITION. Unlike SHOW COLUMNS, SELECT from the COLUMNS table does not have automatic ordering.

• COLUMN_DEFAULT

  The default value for the column. This is NULL if the column has an explicit default of NULL, or if the column definition includes no DEFAULT clause.

• IS_NULLABLE

  The column nullability. The value is YES if NULL values can be stored in the column, NO if not.

• DATA_TYPE

  The column data type.

  The DATA_TYPE value is the type name only with no other information. The COLUMN_TYPE value contains the type name and possibly other information such as the precision or length.

• CHARACTER_MAXIMUM_LENGTH

  For string columns, the maximum length in characters.

• CHARACTER_OCTET_LENGTH

  For string columns, the maximum length in bytes.

• NUMERIC_PRECISION

  For numeric columns, the numeric precision.

• NUMERIC_SCALE

  For numeric columns, the numeric scale.

• DATETIME_PRECISION

  For temporal columns, the fractional seconds precision.

• CHARACTER_SET_NAME

  For character string columns, the character set name.

• COLLATION_NAME

  For character string columns, the collation name.

• COLUMN_TYPE

  The column data type.

  The DATA_TYPE value is the type name only with no other information. The COLUMN_TYPE value contains the type name and possibly other information such as the precision or length.

• COLUMN_KEY

  Whether the column is indexed:

  • If COLUMN_KEY is empty, the column either is not indexed or is indexed only as a secondary column in a multiple-column, nonunique index.

  • If COLUMN_KEY is PRI, the column is a PRIMARY KEY or is one of the columns in a multiple-column PRIMARY KEY.

  • If COLUMN_KEY is UNI, the column is the first column of a UNIQUE index. (A UNIQUE index permits multiple NULL values, but you can tell whether the column permits NULL by checking the Null column.)

  • If COLUMN_KEY is MUL, the column is the first column of a nonunique index in which multiple occurrences of a given value are permitted within the column.

  If more than one of the COLUMN_KEY values applies to a given column of a table, COLUMN_KEY displays the one with the highest priority, in the order PRI, UNI, MUL.

  A UNIQUE index may be displayed as PRI if it cannot contain NULL values and there is no PRIMARY KEY in the table. A UNIQUE index may display as MUL if several columns form a composite UNIQUE index; although the combination of the columns is unique, each column can still hold multiple occurrences of a given value.

• EXTRA

  Any additional information that is available about a given column. The value is nonempty in these cases:

  • auto_increment for columns that have the AUTO_INCREMENT attribute.

  • on update CURRENT_TIMESTAMP for TIMESTAMP or DATETIME columns that have the ON UPDATE CURRENT_TIMESTAMP attribute.

  • STORED GENERATED or VIRTUAL GENERATED for generated columns.

  • DEFAULT_GENERATED for columns that have an expression default value.

• PRIVILEGES

  The privileges you have for the column.

• COLUMN_COMMENT

  Any comment included in the column definition.

• GENERATION_EXPRESSION

  For generated columns, displays the expression used to compute column values. Empty for nongenerated columns. For information about generated columns, see Section 13.1.20.8, “CREATE TABLE and Generated Columns”.

• SRS_ID

  This value applies to spatial columns. It contains the column SRID value that indicates the spatial reference system for values stored in the column. See Section 11.4.1, “Spatial Data Types”, and Section 11.4.5, “Spatial Reference System Support”. The value is NULL for nonspatial columns and spatial columns with no SRID attribute.

Notes

• In SHOW COLUMNS, the Type display includes values from several different COLUMNS columns.

• CHARACTER_OCTET_LENGTH should be the same as CHARACTER_MAXIMUM_LENGTH, except for multibyte character sets.

• CHARACTER_SET_NAME can be derived from COLLATION_NAME. For example, if you say SHOW FULL COLUMNS FROM t, and you see in the COLLATION_NAME column a value of utf8_swedish_ci, the character set is what is before the first underscore: utf8.

Column information is also available from the SHOW COLUMNS statement. See Section 13.7.7.5, “SHOW COLUMNS Statement”. The following statements are nearly equivalent: