UK.DB

The UniKado database DB library, _(*1)

Usage

use UK\DB\Connection;

try
{

   // Open the PGSQL connection (for example)
   $conn = Connection::CreatePgSQL(
      '127.0.0.1',
      'my_database',
      'db_user',
      'db_password',
      'UTF8',
      5432
   );

   // Fetch all records with an foo value > 0
   $records = $conn->fetchAll(
      'SELECT foo, bar, baz from my_table WHERE foo > ?',
      [ 0 ]
   );

   // Output the returned records
   print_r( $records );

}
catch ( \Exception $ex )
{

   echo $ex;
   exit;

}

Version history

0.1.2

Fixing first issue #1

This adds 2 new methods to \UK\DB\Connection, _(*2)

The methods let you set or get a flag that declares if the parsing of used SQL query string for defined Query-Vars should be executed always. Otherwise it will only be parsed if there are some Query-Vars defined., _(*3)

0.1.1

Introducing Query-Vars. (Pre prepared statements)

Query-Vars are key-value pairs, used to replace some placeholders inside an SQL query string with the associated string values., _(*4)

Its like the regular known prepared statements but usable for query parts where prepared statements will not work. e.g. for an dynamic table name part or something else…, _(*5)

Placeholder format restrictions

Placeholders inside the SQL query string must:, _(*6)

start with an open curly bracket, followed by the Dollar symbol {$ and end with the closing curly bracket }
be defined by the 2 parts placeholder-name and default value, separated by the equal sign =

The default value is already used if no Query-Var is defined by PHP code., _(*7)

If no default value should be used the equal sign is mandatory but it throws an exception if no replacement value is declared!, _(*8)

{$PlaceholderName=DefaultValue}

or 2 variants without an default value, _(*9)

{$PlaceholderName=}

{$PlaceholderName}

Value format restrictions

An Query-Vars value must be an string (not null or something else)
It should not contain two following dashes --
Accepted chars are: A-Za-z0-9 \t?_:.<=>-

For example:, _(*10)

If you want to use the following SQL, _(*11)

SELECT
      foo,
      bar
   FROM
      my_table
   WHERE
      foo > ?
   ORDER BY
      foo ASC

but the order direction part should be dynamic, you can use, _(*12)

SELECT
      foo,
      bar
   FROM
      my_table
   WHERE
      foo > ?
   ORDER BY
      foo {$ORDER_DIRECTION=ASC}

Example to call this SQL command, _(*13)

$records = $connectionInstance->fetchAll(
   // The SQL query string
   'SELECT foo, bar FROM my_table WHERE foo > ? ORDER BY foo {$ORDER_DIRECTION=ASC}',
   // Prepared statement parameters
   [ 0 ],
   \PDO::FETCH_ASSOC
   // The query vars
   [ 'ORDER_DIRECTION' => 'DESC' ]
);