godsgood33/php-db

PHP Database Library

1.5 2019-03-09 01:26 UTC

README

Build Status Code Coverage

This is a library that I have been building to act similar to the WPDB class used for database interactions on Wordpress. I have expanded that library and added things like extended insert, replace, and update syntax building.

Installation

  • `composer require godsgood33/php_db`

Setup

  • After installation, run php bin/setup.php to create the required constants to put a required config file:

    • PHP_DB_SERVER (IP or hostname of the server you want to connect to)
    • PHP_DB_USER (username for the connection)
    • PHP_DB_PWD (password for the user)
    • PHP_DB_SCHEMA (default schema to connect to, you can use {schema}.{table} syntax if you need to query other tables)
    • PHP_DB_LOG_LEVEL (using PSR/Logger levels, if absent will default to Logger::Error)
    • PHP_DB_CLI_LOG (boolean deciding if you want to show logs ont he command line, if absent, will not display)
    • PHP_DB_ENCRYPT (boolean to decide if you want to encrypt the password)
    • PHP_DB_ENCRYPT_ALGORITHM (OpenSSL algorithm used to encrypt and decrypt the password)
    • PHP_DB_ENCRYPT_SALT (salt used to encrypt the password)
  • After including autoload, you can create an object as follows

    $db = new Godsgood33\Php_Db\Database();

OR

$conn = new mysqli("server", "user", "pwd", "schema");
$db = new Godsgood33\Php_Db\Database($conn);

Using the second allows you to connect to ANY server that is not the default (however, if the connection DROPs out for any reason, it will be restored with the default server info)

We recommend using this class to extend your existing database connection class. Because instantiating will automatically call the parent class constructor and connect to the database using the default values (as long as you don't create your own class constructor, in which case call parent::__construct()). Then within your DB class you build the function calls that will perform the queries that you need.

class MyDB extends Godsgood33\Php_Db\Database
{
    public function getUsers()
    {
        $this->select("users");
		
        return $this->execute();
    }
}
$db = new MyDB();

Options

  • $autorun static variable - if set to true script will auto-commit query after building it and return the result (using example above)

    public function getUsers() { return $this->select("users"); }

Query Type List

  1. select
    • builds select query
  2. selectCount
    • builds select count(1) query
  3. insert
    • builds insert query for one (1) row
  4. extendedInsert
    • builds insert query with more than one row
  5. update
    • builds update query for one row
  6. extendedUpdate
    • builds update query for more than one row (requires table to pull from and to update)
  7. replace
    • builds replace into query for one row
  8. extendedReplace
    • builds replace query for more than one row
  9. delete
    • builds delete query (allows for joins and targeted deletion)
  10. drop
    • builds drop query (allows for dropping multiple tables)
  11. truncate
    • builds truncate query
  12. createTable
    • builds create table query (allows for temporary, DDL syntax only, or create from select statement)
  13. createTableJson
    • builds a DDL create table query from json (examples/create_table_json.json)
  14. alterTable
    • builds alter table query (allows for add, modify, and drop column syntax)
  15. setVar
    • set a server, system, or connection variable
  16. fieldExists
    • queries table to check for presents of a specific field
  17. fieldData
    • queries table to get field data
  18. tableExists
    • checks for presence of a table
  19. fields
    • helper method to build field list
  20. parseClause
    • helper method to build where and having clauses
  21. flags
    • helper method to parse option flag array
  22. groups
    • helper method to build group by syntax
  23. order
    • helper method to build order by syntax
  24. isConstraint
    • helper method to check for presence of a constraint
  25. encrypt / decrypt
    • helper methods to decrypt and encrypt content