PHP Classes

QdbQuery: Query MySQL databases using parameters

Recommend this page to a friend!
     
  Info   View files Files   Install with Composer Install with Composer   Download Download   Reputation   Support forum   Blog    
Ratings Unique User Downloads Download Rankings
Not enough user ratingsTotal: 496 All time: 5,850 This week: 455Up
Version License PHP version Categories
qdbquery 1.0.0Artistic License5.3Databases
Description 

Author

This class can query MySQL databases using parameters.

It can add the definition of queries of several types to be executed later.

The class can execute the added queries using parameters passed in arrays.

Currently it can execute queries of type SELECT, INSERT, UPDATE and DELETE.

Picture of Richard Lee
Name: Richard Lee <contact>
Classes: 2 packages by
Country: United States United States
Age: ???
All time rank: 2885399 in United States United States
Week rank: 180 Up22 in United States United States Up

Details

QryBldr ======================== is a robust php mysqli query builder class used for creating, modifying, and re-using mysql queries, with unlimited arbitrary param. Class allows you to switch between prepared stmts and regular queries without writing new queries. <h2> Get Started </h2> <h3> Set DB consts + Include Lib </h3> define('DB_PREFIX', ''); define('DB_HOST', 'localhost'); define('DB_USER', 'Your-Username'); define('DB_PASS', 'Your-Password'); define('DB', 'Your-DB'); require 'lib/qrybldr.php'; $db = new \Plug\Database\QryBldr; <h3> Turn on Prepared Statements (optional)</h3> $db->return_prepared = true; <h3> INSERT </h3> <strong> Set the Table </strong> $db->table = 'Users'; <strong> create insert values (with datatype prefixes) </strong> $values = array( 's:id' => 1, 's:firstname' => 'Richard', 's:lastname' => 'Lee', 's:username' => 'R_username', 'sql:dateJoined' => 'now()', 's:email' => 'mail@richardlee.com'); <strong> perform insert query </strong> $stmt = $db->insert($values); <strong> execute insert again with different values </strong> $stmt->id->value(2); $stmt->email->value('email@johndoe.com'); $stmt->username->value('Mr_John_Doe'); $stmt->firstname->value('John'); $stmt->lastname->value('Doe'); $stmt->__execute(); <strong> execute again with different number of args </strong> $stmt->id->value(3); $stmt->email->value('email@dplug.com'); $stmt->username->value('Mr_Plug'); //delete query param firstname, lastname $stmt->firstname->unset(); $stmt->lastname->unset(); //create new arg $stmt->add_column("dob", "now()", "sql"); $stmt->__execute(); //this will close the current prepared statement and create a new one! <strong> turn off prepared statements, but run the same query </strong> $stmt->is_prepared = false; //when __execute is called stmt will be closed //note: in the case where __execute is not called //make sure you close or unset the stmt manually using //the ->close() method or unset function $stmt->id->value(4); $stmt->email->value('userj@dplug.com'); $stmt->username->value('userj'); //add new columns again $stmt->add_column("firstname", "james", "s"); $stmt->add_column("lastname", "tony", "s"); $stmt->__execute(); //performs non prepared query <h3> UPDATE + THE CLAUSE OBJECT </h3> //update values (with datatype prefixes) $values = array( 's:firstname' => 'New_Fname', 's:lastname' => 'New_Lname' ); <strong> create where clause </strong> $clauseObj = $db->getClauseObject(false); create condition //using add condition allows us to reference clauses using keywords $clauseObj->addCondition( array( "s:id" => "1", "OR:s:username:LIKE%" => "R_username"), "is_current_user"); now perform update $stmt = $db->update($values, array(" WHERE ( {?is_current_user} ) ", $clauseObj)); //stmt works in the same way as shown above //try multiple conditions //$db->update($values "WHERE ( {?condition1} ) OR ( {?condition2} ) " ) //use Conditinal statemtents in query //$db->update($values " IF( {?condtion1}, {?condition2}, {?condition3} ) " ) //access the clause object in a stmt via the __clause property $clause = $stmt->__clause->is_current_user; //$clause is a reference to the is_current_user condition <strong> change the details of the clause </strong> //the first arg is instance, second is value $clause->id->value(1, "2"); $clause->username->value(1, "Mr_John_Doe"); //give a new column value $stmt->firstname->value("another_New_FName"); $stmt->lastname->value("another_New_LName"); //perform another query $stmt->__execute(); //close stmt (ensure prepared stmt is not left open) $stmt->close(); <h3> DELETE </h3> //delete from table where username = Mr_John_Doe and id = 2 $db->delete(array(" WHERE {?is_current_user} ", $clauseObj)); $clauseObj->is_current_user->id->value(1, "1"); <h3> SELECT </h3> //create condition that matches users who logged in before now (all users) $clauseObj->addCondition(array("sql:dateJoined:<" => "now()"), "all_past_users"); //kill the username requirement $clauseObj->is_current_user->username->unset(); add select options the option "select" can be an assoc array where the key is the column name, and the value of the key is the alias it can also be a numeric array of column names, or just a simple string $options = array( "select" => array( "firstname" => "fname", "lastname" => "lname" ) , "limit" => 4 , "orderBy" => " id ASC "); lets set the clause param, so that it gets all past users, except for the current one $clause_param = array(" WHERE ( {?all_past_users} ) AND NOT ( {?is_current_user} ) ", $clauseObj); <strong>perform select query </strong> $stmt = $db->select($options, $clause_param); print the results print_r($stmt->fetch_array()); //close prepared stmt unset($stmt); <h3> QRY METHOD - for raw query with param </h3> sometimes it's harder to build a query string using db wrappers. especially when the query is a complex one using JOINs and the works the ::qry method allows you to write custom queries but with vars and conditions Create query string $q_string = "SELECT * FROM {table1} t1, {table2} t2 LEFT JOIN ON t1.{column1} = t2.{column2} WHERE {?condition} AND t1.column3 = {column3_value: 'default value'} "; initialize vars $q_vars = array("sql:table1" => "a", "sql:table2" => "b", "sql:column1" => "id", "sql:column2" => "a_id", "s:column3_value" => "given value"); note, if no value is given for column3_value "default value" is used instead perform a raw query //the third argument is the clause object, if none given the global clause object is used $db->qry($q_string, $q_vars, $clauseObj); change args without using datatype prefixes $db->setColumn($q_vars, array("table1"=> "b", "table2" => "c", "column2" => "b_id")); unset column without using datatype prefix $db->unsetColumn($q_vars, "table1"); get column data "datatype", "value", "conjunction", "operator" $col_3 = $db->getColumn($q_vars, "column3_value"); // echo $col_3->datatype; // echo $col_3->value; note: currently ::qry method does not support prepared queries <h3> Prefixes </h3> <strong> conjunction </strong> - AND | OR - use at the beginning column array keys when creating clause conditions <strong> datatype </strong> - s | d | i | b | sql - use after conjunction if conjunction exists, and before column name <strong> operator </strong> - LIKE% | NOT_LIKE% | = | > | := - used after column key name <strong>example: </strong> array("s:column_name:!=" => "value", "OR:i:column_name2:>" => 5, "AND:sql:dateTime:<" => "now()");

  Files folder image Files (8)  
File Role Description
Files folder imagelib (7 files)
Accessible without login Plain text file README.md Doc. Documentation

  Files folder image Files (8)  /  lib  
File Role Description
  Plain text file clauseobject.php Class Class source
  Plain text file columnobject.php Class Class source
  Plain text file dbrowobject.php Class Class source
  Plain text file helper.php Class Class source
  Plain text file obj.php Class Class source
  Plain text file qrybldr.php Class Class source
  Plain text file stmtobject.php Class Class source

The PHP Classes site has supported package installation using the Composer tool since 2013, as you may verify by reading this instructions page.
Install with Composer Install with Composer
 Version Control Unique User Downloads Download Rankings  
 100%
Total:496
This week:0
All time:5,850
This week:455Up