Alter Table

Add Column:

Purpose:

The purpose to give you the ability to add a field to a table programatically. NOTE: One field at a time!!!!

Definition:

$this->addColumn($strTableName, $param)

  • strTableName - (string) the table name you want to add the field to
  • param - (stdClass) the definition of the field you want to add
    • name - (string) the name of the field
    • dataType - (string) the datatype of the field (any accepted SQL datatype)
    • nn - (boolean) to set not null (optional)
    • default - (string|null) the default value you want the field to have
      Can be NULL (optional)

Returns:

String|mysqli_result - returns the SQL string or the msqli_result object

Examples:

$field = json_decode(
‘{
“name”:”field1”,
“dataType”:”varchar(64)”,
“nn”:false,
“default”:null
}’
);
$this->addColumn(‘test’, $field);

// ALTER TABLE test ADD COLUMN `field1` varchar(64) DEFAULT NULL

Drop Column:

Purpose:

To give you the ability to drop a column (or multiple) programatically.

Definition:

$this->dropColumn($strTableName, $params)

  • strTableName - (string) the table to drop the fields from
  • params - (array:stdClass|string)
    • String - can be a single field name to drop
    • Array:stdClass - can be an array of stdClass objects that have a name
      property to retrieve

Returns:

String|mysqli_result

Examples:

Single item using string

$field = “col1”;
$this->dropColumn(‘test’, $field);

// ALTER TABLE test DROP COLUMN `col1`

Single item using stdClass

$field = new stdClass();
$field->name = ‘col1’;
$this->dropColumn(‘test’, $field);

// ALTER TABLE test DROP COLUMN `col1`

Multiple items

$fields = json_decode(
‘[
{
“name”:”col1”
},
{
“name”:”col2”
}
]’
);
$this->dropColumn(‘test’, $fields);

// ALTER TABLE test DROP COLUMN `col1`, `col2`

Modify Column:

Purpose:

To give you the ability to modify a field programatically

Definition:

$this->modifyColumn($strTableName, $params)

  • strTableName - (string) the table name that contains the fields you want to
    edit
  • param - (stdClass) the definition of the field you want to modify
    • name - (string) the name of the field
    • dataType - (string) the datatype of the field (any accepted SQL datatype)
    • nn - (boolean) to set not null (optional)
    • default - (string|null) the default value you want the field to have
      Can be NULL (optional)

Returns:

String|mysqli_result

Examples:

$field = json_decode(
‘{
“name”:”col1”,
“new_name”:”col1”,
“dataType”:”varchar(10)”,
“nn”:false,
“default”:null
}’
);
$this->modifyColumn(‘test’, $field);

// ALTER TABLE test MODIFY COLUMN `col1` `col` varchar(10) DEFAULT NULL

Add Constraint:

Purpose:

The purpose of this method to allow you to add a constraint to your tables after creation. This simplifies table creation.

Definition:

$this->addConstraint($strTableName, $param)

  • strTableName - (string) the table name to add the constraint to
  • param - (stdClass) the definition of the constraint
    • id - the unique id of the constraint
    • local - (array|string) the field in the local table (fk)
    • schema - the schema the table is in
    • table - the table the field is in
    • field - (array|string) the field in the linked table (key)
    • delete - the action to take when the key is deleted
      can be CASCADE, SET NULL, RESTRICT, or NO ACTION
    • update - the action to take when the key is updated
      can be CASCADE, SET NULL, RESTRICT, or NO ACTION

Returns:

String|mysqli_result

Examples:

$field = json_decode(
‘{
“id”:”unique_id”,
“local”:”col1”,
“schema”:”schema”,
“table”:”table”,
“field”:”field1”,
“delete”:”CASCADE”,
“update”:”CASCADE”
}’
);
$this->addConstraint(‘test’, $field);

ALTER TABLE test ADD CONSTRAINT `unique_id` FOREIGN KEY (`col1`) REFERENCES `schema`.`table` (`field1`) ON DELETE CASCADE ON UPDATE CASCADE