#NOT production ready!

Pribi (forked from Dibi - smart database layer for PHP)

Original Dibi is great.

Not the unified interface for any database vendor, but the fluent interface.

And short notations of standard operations.

And for type-check of parameters, inserted similarly to printf() (just for integer the %i notation is used in difference).

And should be greater, but the price for universality does not allow it. Especially lack of prepared statements hurts these developers, who recalculate CPU time to money.

Forbidden changes, because of final methods or whole classes, confound chance to extend Dibi itself, so fork has been enforced. All hail the "Fluent, MySQL, prepared statements". And wish me good luck with development.

Pribi requires PHP 5.5.0 or later because of namespaces 5.3, __callStatic() 5.3, __DIR__ 5.3, traits 5.4 and Foo::class magic (auto-existing) absolute class name constant 5.5.

Tips and Tricks

Identifier quote

• Pribi is for MySQL. That's the trick. That means, the quotation is done by ` (tick).

Not SQL-99 standard by " (double quote), not MS SQL style by [ ] (brackets) or anything else. Only `ticks`.

Protection against empty array of values to search by

• Empty IN() condition will cause syntax error in MySQL, Pribi will replace it for you by FALSE /* IN(empty) */ (because IN (NULL) is NULL, not false).

• Similarly NOT IN() will cause syntax error, Pribi will replace it for you by NOT FALSE /* IN(empty) */ (because NOT IN (NULL) is NULL, not true).

Aliasing

• For alias, use select('table_with_some_domain_name.columnWithNameReferencingToTableDomain')->as('prettyAliasWithBothTableAndColumnMeaning').

• If you want try to use shorthand as select('columnName prettyAlias'), that means two strings split by white space, you will have to wrap both identifiers like this select('`columnName` `prettyAlias`')

• Otherwise Pribi will wrap your string as single identifier: 'columnName prettyAlias' = `columnName prettyAlias` (yes, name with space is valid).

• So again, use fluent. Every time. That's what Pribi has been designed for and what is native then.

Attractions

• SELECT 1 is complete and valid query, it will return, surprisingly, 1.

• SELECT * FROM `tableFoo` INNER JOIN `tableBar` is valid too, but without ON (or WHERE) condition, specifying bindings between tableFoo and tableBar, nothing will be as result. MySQL allows to run query like this (with empty result), but Pribi does not offer you (by fluent) execution of that query immediately after INNER JOIN because of useless result.

• SELECT IF(1, 'Hit!', 'Missed!') AS integerAsBoolean will give string Hit!, SELECT IF('1', 'Hit!', 'Missed!') AS stringIntegerAsBoolean will give string Hit!, SELECT IF('true', 'Hit!', 'Missed!') AS stringAsBoolean will give string Miss!. How is this possible? Maybe because u think in PHP or similar language. MySQL tries cast everything in condition to boolean. Integer or float is not a problem, string with integer of float value is again not a problem, but everything else, as any text, is converted to false. Finally, SELECT IF('0.1false', 'Hit!', 'Missed!') AS stringStartingByIntegerAsBoolean will give string Hit!, because MySQL reads it from left to right and casting the value to number, so at the end MySQL uses float 0.1 as condition value and that is considered as true.

• SELECT * FROM `foo` LEFT JOIN `bar` on the other hand is not valid for MySQL and you will get a syntax error. And again, Pribi does not offer you (by fluent) the execution of that query.

• SELECT * FROM `foo` INNER JOIN `bar` ON TRUE seems almost same like previous situation, but notice the trailing ON TRUE condition. It says join everything with everything and will give you every combination of all rows of both tables, which will result into number of rows of first table multiply number of rows of second table of result rows.

• SELECT * FROM `foo` LEFT JOIN `bar` ON TRUE seems again almost same like previous situation, which will give you again combinations of every row of every table, but notice the LEFT keyword, which covers situation when the second table is empty - then at least rows from the first table will be in result (extended by columns of the second table, but with NULL values everywhere, as any LEFT JOIN without condition match).

• This is valid identifier `9[;,-/`, and this too `SELECT (_) AS REALY?!`, if you do not forgot `ticks`. Like this you can name your columns, tables, databases, aliases (all called identifier). But seriously, do not do it, if you do not like pain badly.

• NULL is not a standard value, it does not mean nothing but much more something like does not know. If you want to search for NULL value, you will have to use IS NULL or IS NOT NULL construction. Comparing any value with NULL results into NULL, because MySQL really does not know, what NULL means against compared value. For example, 15 = NULL is NULL, "" = NULL (empty string) is NULL, TRUE = NULL is NULL and finally NULL = NULL is again NULL. Because, remember this, MySQL does not know what is meaning of NULL, so comparing unknown value with unknown value has unknown result. For any details, try MySQL official pages.

• When we are talking about NULL, remember than NULL can occurs during calculation and then "NULL-out" whole result, as for example could do divination by zero (SELECT (5 + 64 / 0 - 12) IS NULL results into 1).

• IN () is not a valid condition and causes a syntax error because of missing parameters of IN condition. Using `columnFoo` IN (NULL) matches nothing, even if some of `columnFoo` value is NULL (which is very special, respective very unknown, as mentioned before).

• NOT IN (NULL) is always false, although IN (NULL) is false also. The negation doesn't work. Again, NULL is very special, respective very unknown, and the result is unknown also, which means false.

• `columnFoo` LIKE `columnBar` condition brings same results as `columnFoo` = `columnBar`, but is performed much slower because of lexical value comparison and also ignores keys. Try it in your sandbox and never after. I checked this for MyISAM and InnoDB engines.

• How to multiply values of a column? To sum numbers from one row, you can just simply sum them by plus sign SELECT (`firstColumn` + `secondColumn` + `anotherColumn`) AS `sumResult` and to multiply them by star sign SELECT (`firstColumn` * `secondColumn` * `anotherColumn`) AS `multiplicationResult`. To sum values from all rows, but single column, you can use built-in function SELECT SUM(`columnWithValuesToSum`), but what if you need to multiply values from all rows, but single column? Now math come to help by logarithm SELECT EXP(SUM(LOG(`columnWithValuesToMultiply`))) AS `multiplied`.

• IN command becomes much faster than repeated WHERE for a large amount of values. For details try Danil Zburivsky research and then Baron Schwartz simple answer about sorted values in the IN list.

• Duplicate key on update when creating a table? Yes, that is quite confusing message. Its correct meaning is The name of a key (index) you want to use is already in use for an existing table.

• Queries too slow? Use the index, Luke!. And use the EXPLAIN to find out if an index is used at all.

• To use or not to use PRIMARY KEY for InnoDB table? As say Jeremy Cole Every table has a primary key, even if you do not want to (if the PRIMARY KEY is not explicitly specified, the first non-NULL unique key is used, and failing that, a 48-bit hidden Row ID field is automatically added), so create it by yourself to take control over it.

• What operation comes first to play? SQL operations order are as follows. Check the Ben Nadel blog for details.

  1. FROM clause
  2. WHERE clause
  3. GROUP BY clause
  4. HAVING clause
  5. SELECT clause
  6. ORDER BY clause