DEV Community

Ryan P
Ryan P

Posted on

PHP MySQL Library Pt3

#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
Enter fullscreen mode Exit fullscreen mode

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')
Enter fullscreen mode Exit fullscreen mode

DROP TABLE users

$this->drop('user_view', 'view')
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

DROP TEMPORARY TABLE tmp_user

truncate 

returns mysqli_result
Enter fullscreen mode Exit fullscreen mode

The truncate function allows you to create a truncate table statement to delete all records from a table

$this->truncate('users')
Enter fullscreen mode Exit fullscreen mode

TRUNCATE TABLE users

createTable 

returns mysqli_result
Enter fullscreen mode Exit fullscreen mode

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 ] ]);
Enter fullscreen mode Exit fullscreen mode

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");
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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 ]);
Enter fullscreen mode Exit fullscreen mode

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 ]);
Enter fullscreen mode Exit fullscreen mode

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 ]);
Enter fullscreen mode Exit fullscreen mode

ALTER TABLE users MODIFY COLUMN email user_email varchar(255) DEFAULT NULL

ADD_CONSTRAINT:

setVar 

NULL return
Enter fullscreen mode Exit fullscreen mode

Allows you to set a SQL variable

$this->setVar('foo', 'bar');
Enter fullscreen mode Exit fullscreen mode

tableExists 

returns boolean
Enter fullscreen mode Exit fullscreen mode

Check to see if a table exists

$this->tableExists('schema', 'table');
Enter fullscreen mode Exit fullscreen mode




fieldExists

Top comments (0)