Wallogit.com
2017 © Pedro Peláez
The written for fast, practical database active record actions
Available fields:, (*1)
- This library completly is written for composer package
- This will works with vendor autoload
- Codeigniter active record class interface used
- These library use simple and fast of
- Database Configuration, (*2)
- We first make the database connection, (*3)
- SELECT, (*4)
- FROM, (*5)
- WHERE, (*6)
- LIKE COMBINATION, (*7)
- ORDER BY, (*8)
- GROUP BY, (*9)
- HAVING, (*10)
- LIMIT, (*11)
- OFFSET (skip data), (*12)
- JOIN TABLES, (*13)
- INSERT, (*14)
- UPDATE, (*15)
- DELETE, (*16)
- COUNT, (*17)
- Native Query, (*18)
- GET, (*19)
- DB PREFIX, (*20)
- Num Rows, (*21)
- Row, (*22)
- Processing the query results, (*23)
- Affected Rows, (*24)
- SQL Dump, (*25)
First let's start with the database settings., (*26)
database configuration files in the Db folders -> config.php, (*27)
$current = 'mysql:connect1';
$db = array(
'mysql' => array(
'connect1' => array(
'hostname' => 'localhost',
'username' => 'root',
'password' => '',
'database' => '',
'dbprefix' => ''
)
)
);
The $current variable is the driver you want to use as the active and allows you to use the database connection., (*28)
Example:, (*29)
Up when I want to define a second database connection settings you need to do the following., (*30)
'connect2' => array(
'hostname' => 'localhost',
'username' => 'root',
'password' => '',
'database' => '',
'dbprefix' => ''
)
and my $current variable have been:, (*31)
$current = 'mysql:connect2';
We can define the connection as we want it that way., (*32)
Note: mysql needs to be defined for the identification of the considered prospective., (*33)
Add our workspace our library, (*34)
use Db\Query as DB;
We install the library and also have set a alias. I chose the DB alias., (*35)
A simple database query:, (*36)
DB::select('*');
DB::get('example_table');
We questioned directly above our table without specifying any criteria query. We can do the same query in the following way:, (*37)
DB::select('*')->get('example_table');
Use 1:, (*38)
DB::select('*')->get('example_table');
Use 2:, (*39)
DB::select('examle_type.*')->get('example_type');
Use 3:, (*40)
DB::select('example_type.id');
DB::select('example_type.name')->get('example_type');
select_max():, (*41)
$result = DB::select_max('id')->get('example_type');
echo $result->row()->id;
select_min():, (*42)
$result = DB::select_max('id')->get('example_type');
echo $result->row()->id;
select_avg():, (*43)
$result = DB::select_avg('age')->get('example_type');
echo $result->row()->age;
select_sum():, (*44)
$result = DB::select_sum('total')->get('example_type');
echo $result->row()->total;
distinct():, (*45)
$result = DB::distinct('city')->get('example_type');
echo $result->row()->city;
from():, (*46)
$result = DB::select('*')->from('example_table')->get();
echo $result->row()->total;
$result = DB::where('city','Istanbul')->get('users');
print_r $result->result_array();
Where you can pass parameters to the method in 3 ways., (*47)
Method 1:, (*48)
$result = DB::where('city !=','Istanbul')->get('users');
print_r $result->result_array();
$result = DB::where('age >',19)->get('users');
print_r $result->result_array();
$result = DB::where('age <',19)->get('users');
print_r $result->result_array();
```sh $result = DB::where('age <>',18)->get('users');, (*49)
print_r $result->result_array();, (*50)
```sh $result = DB::where('city','Istanbul')->get('users'); print_r $result->result_array();
Method 2:, (*51)
$result = DB::where(array('city' => 'Istanbul'))->get('users');
print_r $result->result_array();
$result = DB::where(array('age >' => 19))->get('users');
print_r $result->result_array();
$result = DB::where(array('age <' => 19))->get('users');
print_r $result->result_array();
```sh $result = DB::where(array('age <>' => 18))->get('users');, (*52)
print_r $result->result_array();, (*53)
**Method 3:** ```sh $result = DB::where("city => 'Istanbul'")->get('users'); print_r $result->result_array();
suc as., (*54)
If we want we can create a query like:, (*55)
$result = DB::where('id',1)
->where(array('city' => 'Istanbul'))
->where("age <> '18'")->get('users');
print_r $result->result_array();
or_where():, (*56)
$result = DB::where('id',1)->or_where('age',18)->get('users');
where_in():, (*57)
$result = DB::where_in('age',18)->get('users');
a different use:, (*58)
$result = DB::where_in('age',array(18,20,22,23))->get('users');
Note: This combination can be used on all where_in, (*59)
or_where_in():, (*60)
$result = DB::where('city','Istanbul')->or_where_in('age',18)->get('users');
where_not_in():, (*61)
$result = DB::where_not_in('age',18)->get('users');
or_where_not_in():, (*62)
$result = DB::where('city','Istanbul')->or_where_not_in('age',18)->get('users');
, (*63)
or_where_not_in():, (*64)
$result = DB::where('city','Istanbul')->or_where_not_in('age',18)->get('users');
, (*65)
or_where_not_in():, (*66)
$result = DB::where('city','Istanbul')->or_where_not_in('age',18)->get('users');
like():, (*67)
$result = DB::like('name','Ali')->get('users');
```sh $result = DB::like(array('name' => 'Ali', 'city' => Ist))->get('users');, (*68)
**You can also locate the reference point by sending a third parameter:** ```sh before: $result = DB::like('name', 'Ali','before')->get('users'); print out: //users.name LIKE '%Ali' after: $result = DB::like('name', 'Ali','after')->get('users'); print out: //users.name LIKE 'Ali%'
or_like():, (*69)
$result = DB::like('name','Ali')->or_like('city','Ist')->get('users');
not_like():, (*70)
$result = DB::not_like('name','Ali')->get('users');
or_not_like():, (*71)
$result = DB::not_like('name','Ali')->or_not_like('city','Ist')->get('users');
order_by():, (*72)
$result = DB::->order_by('name','DESC')->get('users');
order_by('random'):, (*73)
$result = DB::->order_by('name','random')->get('users');
group_by():, (*74)
$result = DB::group_by('name')->get('users');
having():, (*75)
$result = DB::group_by('name')->having("name = 'Ali'")->get('users');
or_having():, (*76)
$result = DB::group_by('name')
->having("name = 'Ali'")->or_having('age',18)->get('users');
limit():, (*77)
$result = DB::limit(1)->get('users');
instead of the offset method is also useful for:, (*78)
$result = DB::limit(2,1)->get('users');
offset():, (*79)
$result = DB::offset(5)->get('users');
As simple as possible to join tables., (*80)
First example:, (*81)
DB::select('t1.name, t2.city')
->from(DB::dbprefix('users') . ' t1')
->join(DB::dbprefix('cities') . ' t2',"t2.id = t1.city_id",'inner')
->where('t1.age >',18)
->get();
We combine the member table where the city table. And we have defined the coming of the age of 18 and where the., (*82)
Note: We have sent the left marked as the third parameter in the join method. Parameters that are available here:, (*83)
- inner (INNER JOIN)
- left (LEFT JOIN)
- right (RIGHT JOIN)
- left outer join (LEFT OUTER JOIN)
- right outer join (RIGHT OUTER JOIN)
- cross (CROSS JOIN)
inner parameters will work as default., (*84)
Let's make different example:, (*85)
DB::select('t1.name, t2.city')
->from(DB::dbprefix('users') . ' t1')
->join(DB::dbprefix('cities') . ' t2',"t2.id = t1.city_id",'inner')
->join(DB::dbprefix('countries') . ' t3','t3.id = t2.country_id','left')
->where('t1.age <',30)
->where('t1.age >',18)
->get();
There are several ways to add data to the table., (*86)
insert():, (*87)
First:, (*88)
DB::insert('users',array(
'name' => 'Ali',
'city' => 'Istanbul',
'age' => 21
)
)
Another use:, (*89)
DB::set('name','Ali');
DB::set('city','Istanbul');
DB::set('age','18');
DB::insert('users');
```sh DB::set( array( 'name' => 'Ali', 'city' => 'Istanbul/Turkey', 'age' => 18 ) );, (*90)
DB::insert('users');, (*91)
<br >
and another use than:
```sh
class User {
public $name = 'Ali';
public $city = 'Istanbul';
public $age = 18;
}
DB::insert('users', new User());
insert_batch():, (*92)
DB::insert_batch('users',array(
array(
'name' => 'Ali',
'city' => 'Istanbul',
'age' => 21
),
array(
'name' => 'Erkan',
'city' => 'Ankara',
'age' => 20
),
array(
'name' => 'Emre',
'city' => 'Izmir',
'age' => 19
)
)
)
insert_id():, (*93)
After adding to retrieve the last record id:, (*94)
DB::insert_id();
Relatively simple processing such as insert, update, (*95)
update():, (*96)
DB::where('id',1)
->update('users',array(
'name' => 'Ali',
'city' => 'Istanbul/Turkey',
'age' => 18
)
)
or, (*97)
DB::set('name','Ali');
DB::set('city','Istanbul/Turkey');
DB::set('age',18);
DB::update('users');
or, (*98)
DB::set(
array(
'name' => 'Ali',
'city' => 'Istanbul/Turkey',
'age' => 18
)
);
DB::update('users');
update_batch():, (*99)
Sometimes we want to do multiple updates., (*100)
$data = array(
array(
'id' => 1,
'name' => 'Ali',
'city' => 'Izmir',
'age' => 19
),
array(
'id' => 2,
'name' => 'Ahmet',
'city' => 'Bursa',
'age' => 21
),
array(
'id' => 3,
'name' => 'Adem',
'city' => 'Antalya',
'age' => 22
)
);
DB::update_batch('users',data, 'id');
delete():, (*101)
DB::where('id',1)->delete('users');
Get the number of records in the table are also able to do a fairly simple way., (*102)
count_all():, (*103)
DB::count_all('users');
This method will return us to the number of records in the specified table, (*104)
count_all_results():, (*105)
DB::from('users')
->where('age >',18)
->or_where('city','Istanbul')
->count_all_results();
Note: as much as possible when you want to use this method of total records, (*106)
if you say you want to run native SQL., (*107)
query():, (*108)
DB::query("SELECT * FROM users WHERE age > 18");
Is a method that will run our query. If you wish you can send your query table names get method. If you wish, you can choose the method from., (*109)
get():, (*110)
DB::get('users');
or, (*111)
DB::from('users')->get();
get_where():, (*112)
$limit = 1;
$offset = 2;
DB::get_where('users',array('id' => 1),$limit,$offset);
We use our unique method we want to use the prefix table., (*113)
DB::dbprefix('users');
We can use it to get the number of rows of query results., (*114)
num_rows():, (*115)
$result = DB::get('users');
echo $result->num_rows();
Allows access to a single row in the query results., (*116)
row():, (*117)
The result will be the object., (*118)
$result = DB::get('users');
print_r $result->row();
or it can be done in specifying the number of rows you want to access, (*119)
print_r $result->row(5);
row_array():, (*120)
The result will be the array., (*121)
$result = DB::get('users');
print_r $result->row_array();
or it can be done in specifying the number of rows you want to access, (*122)
print_r $result->row_array(5);
If we want to use queries in a loop we run it we can do in two ways., (*123)
result():, (*124)
$result = DB::get('users');
print_r $result->result();
Note: results will become an object, (*125)
result_array():, (*126)
$result = DB::get('users');
print_r $result->result_array();
Note: results will become an array, (*127)
, (*128)
affected_rows():, (*129)
echo DB::affected_rows();
When running under the URL of a page request is sent to all queries will return the string and working duration., (*130)
dump():, (*131)
one will give way listed in a table., (*132)
echo DB::dump();
If we want we can also take in a number of., (*133)
print_r DB::dump('array');
hopefully be helpful to you!, (*134)
Please errors and parts you do not understand that you can discuss open issues identified under the project., (*135)
The written for fast, practical database active record actions
MIT
codeigniter php-5.3 external-db