Database Queries - pnm1231/DynaPort-X GitHub Wiki

The DynaPort X has a core class called Database.php. This is in charge of handling all the database queries of the application.

It's extending the PDO library of PHP therefore giving much compatibility while making running queries much safer.

Query Builder

The query builder is a method of building SQL queries easily while making them safer to execute.

The Database object is assign to $db variable inside models. Following is a sample model with a sample query.

<?php

class BlogPostsModel extends Model {
    
    function get(){
        return $this->db->
                select()->
                from('posts')->
                order('date','DESC')->
                fetch();
        }
    }
    
    function get_by_id($id){
        return $this->db->
                select()->
                from('posts')->
                where('id',$id)->
                fetchOne();
        }
    }
    
    function create($title,$content,$author){
        return $this->db->
                insert('posts')->
                set('title',$title)->
                set('content',$content)->
                set('author',$author)->
                set('time',time())->
                run();
        }
    }
    
    function update($id,$title,$content){
        return $this->db->
                update('posts')->
                set('title',$title)->
                set('content',$content)->
                set('time',time())->
                where('id',$id)->
                run();
        }
    }
    
    function delete($id){
        return $this->db->
                delete('posts')->
                where('id',$id)->
                run();
        }
    }

}

?>

Start building a query

Following is a list of operators you should use to start building a query.

  • select()
  • insert()
  • update()
  • delete()

Always begin building queries using these operators because they reset any previously built queries.

Sample Queries

Click on any part of these queries to jump to its explanation.

SELECT

The SELECT statement begins as the following:

$this->db->select()

It produces:

SELECT *

If you want to select only a list of specific fields, do as follows:

$this->db->select('field1,field2,field3')

You can also use functions like SUM as well as assigns such as AS.

$this->db->select('field1 AS f1,SUM(field2) AS sum,field3');

A SELECT statement must end with one of the following two methods.

  • fetch() - Returns an array of all results
  • fetchOne() - Return an array with a single result

FROM

This is where you specify the table name. from(table)

$this->db->
           select()->
           from('table_name');

This is a mandatory operator

For multiple tables use: from('table1,table2')

It will produce:

SELECT * FROM `table_name`

WHERE

This is where you specify simple matches you want. where(field,match,operator='=')

$this->db->
           select()->
           from('table_name')->
           where('id',123);

You can simply repeat the where operator for more where statements.

$this->db->
           select()->
           from('table_name')->
           where('id',123)->
           where('type','test','!=')->
           where('points',150,'>');

It will produce:

SELECT * FROM `table_name` WHERE `id`=123 AND `type`!='test' AND `points`>150

WHERE OR

As you see usual use of where() joins them with the AND statement. You can join them with an OR using the where_or() operator.

$this->db->
           select()->
           from('table_name')->
           where('id',123)->
           where_or()->
           where('points',150,'>');

It will produce:

SELECT * FROM `table_name` WHERE `id`=123 OR `points`>150

WHERE groups

You can groups WHERE statements using brackets as following:

$this->db->
           select()->
           from('table_name')->
           where('id',123)->
           where_or()->
           where_open()->
           where('type','test','!=')->
           where('points',150,'>')->
           where_close();

It will produce:

SELECT * FROM `table_name` WHERE `id`=123 OR (`type`!='test' AND `points`>150)

WHERE IN

You can use the following method to write WHERE IN queries.

$this->db->
           select()->
           from('table_name')->
           where('id',array(1,5,10),'in');

It will produce:

SELECT * FROM `table_name` WHERE `id` IN (1,5,10)

WHERE BETWEEN

You can use the following method similar to above to write WHERE BETWEEN queries.

$this->db->
           select()->
           from('table_name')->
           where('id',array(1,10),'between');

It will produce:

SELECT * FROM `table_name` WHERE `id` BETWEEN 1 AND 10

JOIN columns

You can join columns using the join() operator.

$this->db->
           select()->
           from('table1,table2')->
           join('table1.id','table2.pid');

It will produce:

SELECT * FROM `table1`,`table2` WHERE `table1`.`id`=`table2`.`pid`

LEFT & RIGHT JOIN

For this you can use either left_join() or right_join() methods.

$this->db->
           select()->
           from('table1')->
           left_join('table2','table1.id','table2.pid');

It will produce:

SELECT * FROM `table1` LEFT JOIN `table2` ON `table1`.`id`=`table2`.`pid`

GROUP BY

You can use the following method to GROUP the results.

$this->db->
           select()->
           from('table_name')->
           group('time');

It will produce:

SELECT * FROM `table_name` GROUP BY `time`

ORDER BY

You can use the following method to ORDER the results.

$this->db->
           select()->
           from('table_name')->
           order('time','desc');

It will produce:

SELECT * FROM `table_name` ORDER BY `time` DESC

OFFSET

You can use the following method to specify the OFFSET point.

$this->db->
           select()->
           from('table_name')->
           offset(5);

It will produce:

SELECT * FROM `table_name` OFFSET 5

LIMIT

You can use the following method to LIMIT the number of results.

$this->db->
           select()->
           from('table_name')->
           limit(25);

It will produce:

SELECT * FROM `table_name` LIMIT 25

To order results randomly use: order('rand')

INSERT

You can start writing an INSERT statement as the following:

$this->db->insert('table_name');

An INSERT statement must end with the run() method.

It will produce:

INSERT INTO `table_name`

UPDATE

You can start writing an UPDATE statement as the following:

$this->db->update('table_name');

It will produce:

UPDATE `table_name`

An UPDATE statement must end with the run() method.

DELETE

You can start writing a DELETE statement as the following:

$this->db->delete('table_name');

It will produce:

DELETE FROM `table_name`

A DELETE statement must end with the run() method.

SET

You can use the set() method to specify columns and values for INSERT and UPDATE queries.

$this->db->
           insert('table_name')->
           set('name','My Name')->
           set('points',125)->
           set('ip','123.123.123.123')->
           run();

RAW Queries

Sometimes you just want to write MySQL queries the way you want. In those cases, you can use the following methods.

$this->db->raw("SELECT * FROM `table_name` WHERE `id` IN (1,5,10) ORDER BY `name` ASC LIMIT 10");

You can also prevent SQL injections by passing parameters separately as below.

$parameters = array(
    'name' => '%Prasad',
    'age'  => 25
);

$this->db->raw("SELECT * FROM `table_name` WHERE `first_name` LIKE :name AND `age`<=:age",$parameters);