PHP MySQL Library Pt3

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');

fieldExists