PHP Classes

QdbQuery: Query MySQL databases using parameters

Recommend this page to a friend!
  Info   View files View files (8)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog    
Last Updated Ratings Unique User Downloads Download Rankings
2013-12-04 (3 years ago) RSS 2.0 feedNot yet rated by the usersTotal: 492 This week: 1All time: 5,660 This week: 610Up
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.

Name: Richard Lee <contact>
Classes: 2 packages by
Country: United States United States
Age: ???
All time rank: 2786352 in United States United States
Week rank: 507 Up49 in United States United States Up


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'        => '');
<strong> perform insert query </strong>
    $stmt                = $db->insert($values);
<strong> execute insert again with different values </strong>
<strong> execute again with different number of args </strong>

    //delete query param firstname, lastname
    //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
    //add new columns again
    $stmt->add_column("firstname", "james", "s");
    $stmt->add_column("lastname", "tony", "s");
    $stmt->__execute(); //performs non prepared query

    //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
    //perform another query 
    //close stmt (ensure prepared stmt is not left open)
 <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
    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
    //close prepared 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  
File Role Description
Files folder imagelib (7 files)
Accessible without login Plain text file Doc. Documentation

  Files folder image Files  /  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

 Version Control Unique User Downloads Download Rankings  
This week:1
All time:5,660
This week:610Up