In part 1 of this series, I talked about this Php_Db library that I created. In part 2 I showed a good number of the function and queries that you can create using it. In this part, I'm going to talk about the DDL queries that you can create with Php_Db
drop
returns mysqli_result
The drop function will create a drop statement that allows you to drop a table or view from your schema. By default it will pull drop a table
$this->drop('users')
DROP TABLE users
$this->drop('user_view', 'view')
DROP VIEW user_view
There is also a third parameter that allows you to specify that the table is a temporary table.
$this->drop('tmp_user', 'table', true)
DROP TEMPORARY TABLE tmp_user
truncate
returns mysqli_result
The truncate function allows you to create a truncate table statement to delete all records from a table
$this->truncate('users')
TRUNCATE TABLE users
createTable
returns mysqli_result
The createTable function allows you to create a table or temporary table either using an array or select statement (this can also use an existing saved select statement). The second parameter is weather this table will be a temporary table. The third parameter defines what fields will be created in this table.
$this->createTable('new_users', false, [
[
'field' => 'id',
'datatype' => 'int(11)',
'option' => 'AUTO_INCREMENT PRIMARY KEY'
],
[
'field' => 'name',
'datatype' => 'varchar(255)',
'default' => null
],
[
'field' => 'email',
'datatype' => 'varchar(64)',
'default' => null
]
]);
CREATE TABLE IF NOT EXISTS new_users (
id int(11) AUTO_INCREMENT PRIMARY KEY,
name varchar(255),
email varchar(255)
);
As I said above, you can also use a select query.
$this->createTable('new_users', false, "SELECT * FROM users");
CREATE TABLE IF NOT EXISTS new_users AS (SELECT * FROM users);
createTableJson
createTableJson allows you to pass a pre-formatted JSON string which will make the create table string. There is an example of the json file format in /vendor/godsgood33/php-db/examples/
alterTable
returns mysqli_result
The alterTable function allows you to create a statement to alter a database table by adding, dropping, or modifying a column. alterTable does this by having 3 parameters, the table to edit, the action to take (ADD_COLUMN, DROP_COLUMN, MODIFY_COLUMN, & ADD_CONSTRAINT), and the parameters. The actions are constants in the Database class.
ADD_COLUMN:
$phoneColumn = new stdClass();
$phoneColumn->name = 'phone';
$phoneColumn->dataType = 'varchar(20)';
$phoneColumn->default = null;
$this->alterTable('users', \Godsgood33\Php_Db\Database::ADD_COLUMN, [
$phoneColumn
]);
ALTER TABLE users ADD COLUMN phone varchar(20) DEFAULT NULL
DROP_COLUMN:
$phoneColumn = new stdClass();
$phoneColumn->name = 'phone';
$this->alterTable('users', \Godsgood33\Php_Db\Database::DROP_COLUMN, [
$phoneColumn
]);
ALTER TABLE users DROP COLUMN phone
MODIFY_COLUMN:
$emailColumn = new stdClass();
$emailColumn->name = 'email';
$emailColumn->new_name = 'user_email';
$emailColumn->dataType = 'varchar(255)';
$emailColumn->default = null;
$this->alterTable('users', \Godsgood33\Php_Db\Database::MODIFY_COLUMN, [
$emailColumn
]);
ALTER TABLE users MODIFY COLUMN email user_email varchar(255) DEFAULT NULL
ADD_CONSTRAINT:
setVar
NULL return
Allows you to set a SQL variable
$this->setVar('foo', 'bar');
tableExists
returns boolean
Check to see if a table exists
$this->tableExists('schema', 'table');
Top comments (0)