PHP MySQL Library Pt3
Tags: php, mysql, wordpress
Published: 2019-07-15 04:08:45

---
title: PHP MySQL Library Pt3
published: true
description: 
tags: #php #mysql #wordpress
---

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