PHP MySQLi Library Pt1

I really love the way that the WordPress WPDB class works.

$wpdb->insert('table', ['field' => 'value']);

Done!

It does the statement creation and automatic escaping 'problematic' characters. But there's not an easy way to add it to your project. There are other variables like "posts", that contains the table name for the posts table, that are unnecessary for most. So I decided to create Php_Db that does most all the same things. I thought about using the library ezSQL that WPDB is based on, but it didn't include some very fundamental things that I wanted an extended insert, replace, and update functionality, along with some DDL capabilities. There were other things, but I saw a big need for those.
I'm going to take a couple days to talk about how Php_Db works because I put a lot of functionality in it. For full documentation you can look at my Docs or Source

So installation...
composer require godsgood33/php_db

Now you need to run the setup to generate some defined variables. Navigate to {project_dir}/vendor/godsgood33/php_db. Now if you would like to encrypt your DB password that Php_Db uses, run php bin/setup.php --algorithms to display the list of all your systems supported OpenSSL algorithms. Once you decide on one, rerun php bin/setup.php -a={algorithm} If you don't want to encrypt the password you can just run php bin/setup.php for the same questions.

It will then ask you a couple questions, MySQL server name/IP, user name, password, and schema. Then it prints out a list of constants you need to add to your project. Once you include those you can start using Php_Db. The best place to put them is in your bootstrap or index file. Just as long as it is done prior to instantiating your db class.

If you ever change your username or password, or you want to change your encryption algorithm, you can just rerun setup.php to get the new values, update your constants, and you're good. WARNING If you use Php_Db to encrypt any data you insert into your database you are going to have to decrypt it first, then change your algorithm, then you can re-encrypt it. Which all you have to do is call the encrypt / decrypt methods at the appropriate times.

I recommend using Php_Db to extended your database class
class db extends \Godsgood33\Php_Db\Database {}

So now, some basic functionality.

The field list, WHERE clauses, and "flags" all operate the same independent of which query you are running (select, insert, delete...)

Just like WPDB the first parameter in a query is the table name. This is just a string that you can put single or multiple tables and/or add aliases if you like. It just needs to look like it would if you were writing the SQL query itself.
"table" OR "table1 t1, table2 t2"

The next parameter in is the field list. This is simply an array of fields you want to ingest in your results. Again, just like the SQL query.
['id', 'name'] OR ['t1.id', 't2.name'] NOTE: There is no field list parameter in the selectCount method.

The third parameter is the WHERE clause list. This is an array of \Godsgood33\Php_Db\DBWhere objects.

DEFINITION:

$where = new DBWhere($field = null, $value = null, $operator = '=')

So let's say you wanted to find a user with a specific ID

$where = new DBWhere('id', 1234);
$this->select('users', null, $where);

This query would look like SELECT * FROM users WHERE id = '1234'. So what do you do when you want to provide multiple where clauses?

$where1 = new DBWhere('fname', 'Fred');
$where2 = new DBWhere('lname', 'Flintstone');
$this->select('users', null, [$where1, $where2]);

SELECT * FROM users WHERE fname = 'Fred' AND lname = 'Flintstone'

By default, DBWhere will use a 'AND' operator between the clauses. You can use any other SQL supported operator by updating the DBWhere::$sqlOperator property. So that could turn into...

$where1 = new DBWhere('lname', 'Flintstone');
$where2 = new DBWhere('lname', 'Jetson');
$where2->sqlOperator = 'OR';
$this->select('users', null, [$where1, $where2]);

SELECT * FROM users WHERE lname = 'Flintstone' OR lname = 'Jetson'

If you want to look for multiple values and use an IN operator you can call

$where = new DBWhere('id', [1,2,3], DBWhere::IN)
$this->select('users', null, $where);

SELECT * FROM users WHERE id IN ('1','2','3')

You can also use NOT_IN, BETWEEN, LIKE, NOT_LIKE, IS, or IS_NOT.
You can also use normal operators =, !=, >, <, <=, >=. If 'operator' parameter is not specified it uses '=' as the default operator.

DBWhere also does automatic conversion and escaping for the following:

• null = NULL
• DateTime = 'Y-m-d H:i:s'
• boolean = '0' or '1'
• array = each element is escaped
• objects = if the object has a callable '_escape' method, it will call that and return the result

Lastly, there are flags, join, having, order, group, and limit.

join = The join key is an array of join strings
having = The having key is an array of DBWhere object just like the WHERE clauses
order = Can either be a string or an array list of ordering clauses
group = Can be a string or array just like order
limit = A string with the number you want to limit (can include the starting row)

So a potential flags array could look like:

[
    'joins' => [
        'JOIN table2 t2 ON t2.id = t1.id',
        'JOIN table3 t3 ON t3.id = t2.id'
    ],
    'having' => [
        new DBWhere('age', 18, '>=')
    ],
    'order' => 't1.name DESC',
    'group' => 't1.id',
    'limit' => '10'
]

Again, these are the main parameters that are allowed for most of the query types. I am going to go in more detail about the different queries themselves in the next post. And as always there is more detail in the full docs