Query Builder
Required Namespace
  • Ness
    • Autopulse
      • Factory
        • QueryBuilder
You can include this library to your code file by typing the below code;

use Ness\Autopulse\Factory\QueryBuilder


Query Builder Class #top

Query Builder class is used to generate queries and retrieve records from your database. Using a query builder class will give you a more understandable code, you will have a chance to access your records with few lines rather than creating and thinking on complex queries. You can run your 'select' queries using this library for 'update' and 'insert' commands you need to use 'Autopulse' library. You can take a look at Autopulse Library Here.

About Queries All your queries must end with get() function. This is required for returning your current query.


A Simple Query #top

Following code example show's you how to select all data from a table. 

                                
$qb = new QueryBuilder();
$mycommand = $qb->select()
            ->table("members")
            ->get();
            
//Generated: select  * from members





Select Multiple Columns #top

This example is about chained selects for multiple columns. You can do the same task by using arrays. The following second example will show you how to use arrays. 

                        
$qb = new QueryBuilder();
$mycommand = $qb->select("email")->select("password")
            ->table("members")
            ->get();
            
    //Generated: select  email, password from members


The same thing can be done with arrays. 
    
$qb = new QueryBuilder();
$mycommand = $qb->select(array("username", "email", "password"))
                ->table("members")
                ->get();
            
//Generated: select   username, email, password from members


//or you can use
$mycommand = $qb->select("username, email, password")->table("users")->get();

//This will generate: select   username, email, password from users





Get Specific Data #top

Following code example show's you how to select a specific data from a table. 

    
$mycommand = $qb->select("username")
    ->table("users")
    ->where("id=", "1955")
    ->get();
//Generated: select  username from users  where id ='1955'





Get Specific Data with Multiple Conditions #top

You can use chained where function or array for multiple conditions. These two code lines will show you an example about that. 


$qb = new QueryBuilder();
$mycommand = $qb->select("username")
    ->table("users")
    ->where("age >=", "18")->where("country_code=", "32")
    ->get();
//Generated: select  username from users  where age >='18'  AND country_code='32'

The following code lines will return same result. 
        
$qb = new QueryBuilder();
$mycommand = $qb->select("username")
        ->table("users")
        ->where(array("age >=" => "18", "country_code=" => "32"))
        ->get();
//Generated: select  username from users  where age >='18'  AND country_code='32'





Using where_or #top

This function is for where condition with OR 

    
$qb = new QueryBuilder();
$mycommand = $qb->select("username")
        ->table("users")
        ->where_or("age >=", "18")->where("country_code=", "32")
        ->get();
//Generated: select  username from users  where age >='18'  OR country_code='32'

The following code lines will return same result. 
    
$qb = new QueryBuilder();
$mycommand = $qb->select("username")
        ->table("users")
        ->where_or(array("age >=" => "18", "country_code=" => "32"))
        ->get();
//Generated: select  username from users  where age >='18'  OR country_code='32'

You can connect where with where_or: 

$qb = new QueryBuilder();
$mycommand = $qb->select("*")
    ->table("users")
    ->where("id >","10")->where_or(array("country_code="=>"10"))->where("gender=","m")
    ->get();
//Generated: select  * from users  where id >'10'  OR  country_code='10'   AND gender='m'





Searching Data With 'like' methods #top

You can generate queries with LIKE condition. 

    
$qb = new QueryBuilder();
$mycommand = $qb->select(array("product_name", "author", "version"))
        ->table("products pr")
        ->like("product_name", "nessphp")
        ->get();
//Generated: select   product_name, author, version from products pr  WHERE product_name LIKE  '%nessphp%'

You can also use this method with chained methods. 

$qb = new QueryBuilder();
$mycommand = $qb->select(array("pr.product_name", "pr.author", "pr.version"))
        ->table("products pr")
        ->like("pr.product_name", "nessphp")->like("author","Sinan")
        ->get();
//Generated: select   pr.product_name, pr.author, pr.version from products pr  
//WHERE pr.product_name LIKE  '%nessphp%' AND author LIKE  '%Sinan%'


Setting Wildcard Options As you have noticed, the wildcard in the like method is found both at the beginning and at the end. You can set this with some special functions. The following code should be followed for this.

Wildcard Position #top

    
$mycommand = $qb->select("product_name")
->table("products")
->like("author", "Sinan", $qb->wildcard->Both())
->get();
//Generated:  ..... author LIKE  '%Sinan%'
Front: $qb->wildcard->Front() 
                        
                            
$mycommand = $qb->select("product_name")
->table("products")
->like("author", "Sinan", $qb->wildcard->Front())
->get();
//Generated:  ..... author LIKE  '%Sinan'
End: $qb->wildcard->End() 
                        
                            
$mycommand = $qb->select("product_name")
->table("products")
->like("author", "Sinan", $qb->wildcard->End())
->get();
//Generated:  ..... author LIKE  'Sinan%'

You can also set a special wildcard charecter for LIKE clause. You can do that by;

Special Wildcard #top


$qb = new QueryBuilder();

$qb->wildcard->setWildcard("_");
$mycommand = $qb->select("product_name")
->table("products")
->like("author", "Sinan", $qb->wildcard->End())
->get();
Running command with $qb->wildcard->setWildcard("_"); will set your % to _ wildcard.

You can get your current wildcard by calling $qb->wildcard->getWildcard(); this will return as string your current wildcard.

Other types of LIKE

You can also call the LIKE method with some other customized methods. Here is a list of them;

like_not: The use of this method is identical to LIKE. The only difference between them is the function. like_not method creates queries with NOT restriction.

like_or: Standart 'like' method will connect conditions with 'AND' if you want to add a OR condition you need to call your 'like' method with this 'like_or' method.

like_or_not: Standart 'like_not' method will connect conditions with 'AND' if you want to add a OR condition you need to call your 'like_not' method with this 'like_or_not' method.

INFO All your wildcard options can be used in all types or like methods

Using JOIN's #top

by default your queries with JOIN should be; 


$qb = new QueryBuilder();
$mycommand = $qb->select("u.username")
        ->table("users u")
        ->join("roles r", "r.roleid = u.roleid")
        ->get();
Or you can connect multiple joins with each other 
 
$qb = new QueryBuilder();
$mycommand = $qb->select("u.username, m.message")
        ->table("users u")
        ->join("roles r", "r.roleid = u.roleid")
        ->join("mesaages m", "m.userid = u.userid")
        ->get();
JOIN TYPES You can set a join type by adding a third parameter to your join method in your query. For example you can set it "INNER", "OUTER","RIGHT" or "LEFT"

Example: 

$qb = new QueryBuilder();
$mycommand = $qb->select("u.username, m.message")
        ->table("users u")
        ->join("roles r", "r.roleid = u.roleid", "INNER")
        ->join("mesaages m", "m.userid = u.userid",  "INNER")
        ->get();

group_by #top

Add a group by condition to your query 


$qb = new QueryBuilder();
$mycommand = $qb->select("message_type, message_body")
        ->table("messages")
        ->group_by("message_type")
        ->get();
        



$qb = new QueryBuilder();
$mycommand = $qb->select("message_type, message_body")
    ->table("messages")
    ->group_by(array("message_type", "other_1", "other_2", ... , "other_n")
    ->get();


order_by #top

Set aorder by condition to your query 


$qb = new QueryBuilder();
$mycommand = $qb->select("message_type, message_body")
        ->table("messages")
        ->order_by("message_date", "ASC")
        ->get();


limit #top

Add LIMIT condition to your query 


$qb = new QueryBuilder();
$mycommand = $qb->select("message_type, message_body")
        ->table("messages")
        ->order_by("message_date", "ASC")
        ->limit(20)
        ->get();


Other Useful Commands #top

sum() #top


$qb = new QueryBuilder();
$mycommand = $qb->select(QueryBuilder::sum("salary"))
        ->table("employee")
        ->get();
//select   SUM(salary)  from employee

min() #top


$qb = new QueryBuilder();
$mycommand = $qb->select(QueryBuilder::min("salary"))
        ->table("employee")
        ->get();
//select   MIN(salary)  from employee

max() #top


$qb = new QueryBuilder();
$mycommand = $qb->select(QueryBuilder::max("salary"))
        ->table("employee")
        ->get();
//select   MAX(salary)  from employee

avg() #top


$qb = new QueryBuilder();
$mycommand = $qb->select(QueryBuilder::avg("salary"))
        ->table("employee")
        ->get();
//select   AVG(salary)  from employee

count() #top


$qb = new QueryBuilder();
$mycommand = $qb->select(QueryBuilder::count("*"))
        ->table("employee")
        ->get();
//select   count(*)  from employee

distinct() #top


$qb = new QueryBuilder();
$mycommand = $qb->select(QueryBuilder::distinct("user_name"))
        ->table("employee")
        ->get();
//select   distinct user_name  from employee

column_as()#top


$qb = new QueryBuilder();
$mycommand = $qb->select(QueryBuilder::column_as("u.username", "fullName"))
        ->table("employee")
        ->get();
//select u.username AS fullName from employee

free() #top

Returns the value in parameter. 

QueryBuilder:free("select * from users");

frontfree() #top


$qb = new QueryBuilder();

$qb->frontFree("-- DISABLED");
$mycommand = $qb->select(QueryBuilder::column_as("u.username", "fullName"))
        ->table("employee")
        ->get();
// -- DISABLED select u.username AS fullName from employee

endfree() #top


$qb = new QueryBuilder();

$qb->endFree("-- THIS QUERY SELECTS ALL RECORDS FROM employee TABLE");
$mycommand = $qb->select(QueryBuilder::column_as("u.username", "fullName"))
        ->table("employee")
        ->get();
//select u.username AS fullName from employee -- THIS QUERY SELECTS ALL RECORDS FROM employee TABLE

Running Generated Queries #top

All methods and returned values in QueryBuilder class is stored in a variable and send to any variable or parameter as string. For running a query generated with QueryBuilder class you need to define a connection to database. This example will guide you to create a simple query and run it with mysqlconnect and mysqlcommand class. 



//Create a query builder instance
$qb = new QueryBuilder();

//create a command
$mycommand = $qb->select(array("u.email", 
    QueryBuilder::column_as(QueryBuilder::free("concat_ws(' ', u.name, 'u.surname')"), "fullName")))
->table("employee")
->get();


//define connection
$con = new MySqlConnect("localhost", "root", "my_pwd_324234", "employee");

//fill command to MySqlCommand class
$cmd = new MySqlCommand($mycommand, $con->Source());

//returned data as array in var_dump
var_dump($cmd->FetchAll());