Installation

composer require ezsql/ezsql

Usage

require'vendor/autoload.php';// **** is one of mysqli, pgsql, sqlsrv, sqlite3, or Pdo.useezsql\Database;$db = Database::initialize('****', [$dsn_path_user,$password,$database,$other_settings],$optional_tag);// Is same as:useezsql\Config;useezsql\Database\ez_****;$settings =newConfig('****', [$dsn_path_user,$password,$database,$other_settings]);$db =newez_****($settings);

to_string($arrays, $separation = ',');clean($string);create_cache(string $path = null);secureSetup(string $key = 'certificate.key',    string $cert = 'certificate.crt',    string $ca = 'cacert.pem',    string $path = '.'._DS);secureReset();create_certificate(string $privatekeyFile = certificate.key,    string $certificateFile = certificate.crt,    string $signingFile = certificate.csr,    string $ssl_path = null, array $details = [commonName => localhost]);

create(string $table = null, ...$schemas);// $schemas requires... column()column(string $column = null, string $type = null, ...$args);primary(string $primaryName, ...$primaryKeys);index(string $indexName, ...$indexKeys);drop(string $table);

// Creates an database tablecreate('profile',// and with database column name, datatype// data types are global CONSTANTS// SEQUENCE|AUTO is placeholder tag, to be replaced with the proper SQL drivers auto number sequencer word.column('id',INTR,11,AUTO,PRIMARY),// mysqlicolumn('name',VARCHAR,50, notNULL),column('email',CHAR,25,NULLS),column('phone',TINYINT));

innerJoin(string $leftTable = null, string $rightTable = null,    string $leftColumn = null, string $rightColumn = null, string $tableAs = null, $condition = EQ);leftJoin(string $leftTable = null, string $rightTable = null,    string $leftColumn = null, string $rightColumn = null, string $tableAs = null, $condition = EQ);rightJoin(string $leftTable = null, string $rightTable = null,    string $leftColumn = null, string $rightColumn = null, string $tableAs = null, $condition = EQ);fullJoin(string $leftTable = null, string $rightTable = null,    string $leftColumn = null, string $rightColumn = null, string $tableAs = null, $condition = EQ);

prepareOn();// When activated will use prepare statements for all shortcut SQL Methods calls.prepareOff();// When off shortcut SQL Methods calls will use vendors escape routine instead. This is the default behavior.

Shortcut SQL Methods

// The variadic ...$whereConditions, and ...$conditions parameters,//  represent the following global functions.// They are comparison expressions returning an array with the given arguments,//  the last arguments of _AND, _OR, _NOT, _andNOT will combine expressionseq('column',$value,_AND),// combine next expressionneq('column',$value,_OR),// will combine next expression againne('column',$value),// the default is _AND so will combine next expressionlt('column',$value)lte('column',$value)gt('column',$value)gte('column',$value)isNull('column')isNotNull('column')like('column','_%?')notLike('column','_%?')in('column', ...$value)notIn('column', ...$value)between('column',$value,$value2)notBetween('column',$value,$value2)// The above should be used within the where( ...$whereConditions) clause// $value will protected by either using escape or prepare statement

// To allow simple grouping of basic $whereConditions,// wrap the following around a group of the above comparison// expressions within the where( ...$whereConditions) clausegrouping(eq(key, value, combiner ),eq(key, value, combiner ) )// The above will wrap beginning and end grouping in a where statement// where required to break down your where clause.

// Note: The usage of this method will require the user/developer to check// if `query_string` or `param_array` is valid.//// This is really an `private` internal method for other shortcut methods,// it's made public for `class development` usage only.////// Supply the the whole `query` string, and placing '?' within, with the same number of arguments in an array.// It will then determine arguments type, execute, and return results.query_prepared(string$query_string, array$param_array);// You will need to call this method to get last successful query result.// It wll return an object array.queryResult();

Example for using prepare statements indirectly, with above shortcut SQL methods

// To get all shortcut SQL methods calls to use prepare statements$db->prepareOn();// This needs to be called at least once at instance creation$values = [];$values['name'] =$user;$values['email'] =$address;$values['phone'] =$number;$db->insert('profile',$values);$db->insert('profile', ['name' =>'john john','email' =>'john@email','phone' =>123456]);// returns result set given the table name, column fields, and ...conditions$result =$db->select('profile','phone',eq('email',$email),between('id',1,$values));foreach ($resultas$row) {echo$row->phone;}$result =$db->select('profile','name, email',// Conditionals can also be called, stacked with other functions like://  innerJoin(), leftJoin(), rightJoin(), fullJoin()//      as (leftTable, rightTable, leftColumn, rightColumn, tableAs, equal condition),//  where( eq( columns, values, _AND ), like( columns, _d ) ),//  groupBy( columns ),//  having( between( columns, values1, values2 ) ),//  orderBy( columns, desc ),//  limit( numberOfRecords, offset ),//  union(table, columnFields, conditions),//  unionAll(table, columnFields, conditions)$db->where(eq('phone',$number,_OR),neq('id',5) ),//  another way: where( array(key, operator, value, combine, combineShifted) );//  or as strings double spaced: where( "key  operator  value  combine  combineShifted" );$db->orderBy('name'),$db->limit(1));foreach ($resultas$row) {echo$row->name.''.$row->email;}// To get results in `JSON` format$json =get_results(JSON,$db);

Example for using prepare statements directly, no shortcut SQL methods used

$db->query_prepared('INSERT INTO profile( name, email, phone) VALUES( ?, ?, ? );', [$user,$address,$number]);$db->query_prepared('SELECT name, email FROM profile WHERE phone = ? OR id != ?', [$number,5]);$result =$db->queryResult();// the last query that has results are stored in `lastResult` protected property// Or for results in other formats use the global function, will use global database instance if no `$db` supplied$result =get_results(/* OBJECT|ARRAY_A|ARRAY_N|JSON */,$db);// Defaults to `OBJECT`foreach ($resultas$row) {echo$row->name.''.$row->email;}

usefunctionezsql\functions\functionBelow;// Or as here, a complete list.usefunctionezsql\functions\{database,mysqlInstance,pgsqlInstance,mssqlInstance,sqliteInstance,pdoInstance,tagInstance,setInstance,getInstance,clearInstance,get_vendor,///to_string,clean_string,is_traversal,sanitize_path,create_certificate,///column,primary,foreign,unique,index,addColumn,dropColumn,changingColumn,///eq,neq,ne,lt,lte,gt,gte,isNull,isNotNull,like,in,notLike,notIn,between,notBetween,///where,grouping,groupBy,having,orderBy,limit,innerJoin,leftJoin,rightJoin,fullJoin,union,unionAll,///creating,deleting,dropping,replacing,selecting,inserting,altering,get_results,table_setup,set_table,set_prefix,select_into,insert_select,create_select,};

For Authors andContributors

Contributing

License