Query Builder

Seme Framework gives you access to a Query Builder class. This pattern allows information to be retrieved, inserted, and updated in your database with minimal scripting. In some cases only one or two lines of code are necessary to perform a database action. Seme Framework does not require that each database table be its own class file. It instead provides a more simplified interface.

Beyond simplicity, a major benefit to using the Query Builder features is that it allows you to create database independent applications, since the query syntax is generated by each database adapter. It also allows for safer queries, since the values are escaped automatically by the system.

Selecting Data

The following functions allow you to build SQL SELECT statements. Selecting all column (*)

$this->db->select(); //means select *

Selecting Certain Columns

For selecting some columns, you can use select method from db class as many as columns you want selected.

$this->db->select('column_name');
$this->db->select('column_name2');

Selecting aliased Data

For selecting aliased data, simply use select_as method.

$this->db->select('column_name','cn');

Selecting function aliased Data

For selecting function aliased data, simply use select_as method with add false (0) to third parameter.

$this->db->select('CONCAT(column_name," ",column_name2)','cn',0);

Third parameter means we do not strip any function that exist in select.

Choose Table

After selecting columns, we have to select the table using method from.

$this->db->from('table_name','tbl_alias');

Table alias is mandatory if we want to use JOIN method.

Join Table

After selecting columns, we have to select the table using method from.

$this->db->join('table_name2','tbl_alias2',
'table_name2_column','table_name',
'table_name_column',[left|right]);

Table join consist of 6 parameters:

1. Table to be joined
2. Alias of table to be joined
3. Column of table to be joined
4. Main table
5. Main table column
6. Join method left, right, or none (''). Default: none.

Order By

Sorting the result data.

$this->db->order_by('column_name',[asc | desc]);

Order By consist of 2 parameters:

1. The column name
2. Sort Direction, asc and desc. default asc.

Group By

Grouping data.

$this->db->group_by('column_name');

This method only take one parameter, the coloumn name you want to grouped.

Limit

Limiting the result.

$this->db->limit([number of result]);

This method only take one parameter, the number of result you desired.

Paging

Create ranged limit or paging.

$this->db->page([page_number],[page size]);

Order By consist of 2 parameters:

1. Page Number, starting from 1.
2. Page Size, starting from 1.

Get Result

By Default method get from seme framework return the array of object result.

$this->db->get([array | object],[0 | 1]);

Method get By consist of 2 parameters:

1. The type of result, array or object. Default object.
2. Debug mode, if true (1) it will break the process and show generated query.

Get Single Result

Get only one row result, simply use get_first method.

$this->db->get_first([array | object],[0 | 1]);

Order By consist of 2 parameters:

1. The type of result, array or object. Default object.
2. Debug mode, if true (1) it will break the process and show generated query.

Condition: Where

Filtered query result using where syntax SQL.

$this->db->where($params,$params2="",$operand="AND",$comp="=",$bracket=0,$bracket2=0);

Method where consist of 6 parameters:

1. Parameter-1 contain table field (key) string or name value pair array.
2. Parameter-2 contain about string table value. If parameter-1 is array, this parameter will be ignored.
3. Parameter-3 contain about operand, like AND or OR. Default value is AND.
4. Parameter-4 contain about comparison operand, default is =. Can be fill by:
   • =
   • !=
   • <>
   • >=
   • <=
   • like
   • like%
   • like%%
   • %like%
   • %like
   • notlike
   • notlike%
   • %notlike%
5. Parameter-5 add open bracket for start priority processing.
6. Parameter-6 add close bracket for end priority processing.

Condition: Where_As

Filtered query result using where syntax SQL. But this query not automatically escaped query. So please be avoiding security breach by using db Escape method.

$this->db->where_as($params,$params2="",$operand="AND",$comp="=",$bracket=0,$bracket2=0);

Method where_as consist of 6 parameters:

1. Parameter-1 contain table field (key) string or name value pair array.
2. Parameter-2 contain about string table value. If parameter-1 is array, this parameter will be ignored.
3. Parameter-3 contain about operand, like AND or OR. Default value is AND.
4. Parameter-4 contain about comparison operand, default is =. Can be fill by :
   • =
   • !=
   • <>
   • >=
   • <=
   • like
   • like%
   • like%%
   • %like%
   • %like
   • notlike
   • notlike%
   • %notlike%
5. Parameter-5 add open bracket for start priority processing.
6. Parameter-6 add close bracket for end priority processing.

Condition: Where_In

Filtered query result using Where In SQL syntax.

$this->db->where_in($tbl_key,$arr_values=array(),$is_not="0",$comp="AND");

Method where_in consist of 4 parameters:

1. Parameter-1 contain table field (key) string to be filtered in.
2. Parameter-2 contain about array of values.
3. Parameter-3 contain about negation, if true (1) apply negation (WHERE NOT IN) otherwise WHERE IN. Default 0.
4. Parameter-4 contain about comparison operand, default is AND. Can be fill by AND or OR.

Escape

Escapes special characters in a string for use in an SQL statement. Especially used in where_as parameter-2. Make your input charset friendly.

$this->db->esc($str);

Consist of 1 parameters contain about unescaped string. Returned escaped string.

Database Character Set

Seme Framework has supported for advanced character set like UTF8MB4 which is can be applied for MySQL 5.6 or higher. Default charset connection is UTF8.

Set Character Set

Setup or change character set can be applied using setCharSet method.

$this->db->setCharSet($charset);

Consist of 1 parameters contain about unescaped string. Returned true if success otherwise will triggered error.

Get Character Set

Get current character set.

$this->db->getCharSet();

Returned character set name if success otherwise will triggered error.