QdbQuery: Query MySQL databases using parameters

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()");