Database Library (Part 2)



( ZN >= 1.0.0 )

Database library, chapter 2 .

 

 

# Methods


this innerJoin ( String $ table , String $ otherColumn , String $ operator = '=' )
this outerJoin ( String $ table , String $ otherColumn , String $ operator = '=' )
this leftJoin ( String $ table , String $ otherColumn , String $ operator = '=' )
this rightJoin ( String $ table , String $ otherColumn , String $ operator = '=' )
this union ( String $ table )
this unionAll ( String $ table )
this transStart ( Void )
this transQuery ( Void )
Bool transEnd ( Void )
this caching ( String $ time , String $ driver = 'file' )
this cleanCaching ( Void )
this distinct ( Void )
this distinctRow ( Void )
String cset ( String $ set )
String collate ( String $ set )
String encoding ( String $ charset = 'utf8' , String $ collate = 'utf8_general_ci' )
String escapeString ( String $ data )
String realEscapeString ( String $ data )
Mixed characterSet ( String $ set , Bool $ return = false )
String alias ( String $ string , String $ alias , Bool $ brackets = false )
String brackets ( String $ string )
this all ( Void )
this maxStatementTime ( String $ time )
this straightJoin ( Void )
this highPriority ( Void )
this lowPriority ( Void )
this quick ( Void )
this delayed ( Void )
this ignore ( Void )
this partition ( String ... $ args )
this procedure ( String ... $ args )
this outFile ( String $ file )
this dumpFile ( String $ file )
this into ( String $ varname1 , String $ varname2 )
this forUpdate ( Void )
this lockInShareMode ( Void )
this smallResult ( Void )
this bigResult ( Void )
this bufferResult ( Void )
this cache ( Void )
this noCache ( Void )
this calcFoundRows ( Void )

 

 

# InnerJoin ( ZN >= 3.0.0 )


Used to join tables with SQL INNER JOIN statement. 1. The parameter must contain other table name and column information to be combined.

Parameters
String $ jointablecolumn Table and column information to merge.
String $ tablecolumn There get()should be table and column information for the single use method. Multiple uses may have different table and column information.
return this
uses

The following table shows which transactions are made with tables that have been subject to joining process.

Select
DB::innerJoin('comments.user_id', 'profiles.user_id')
  ->innerJoin('profiles.user_id', 'users.id')
  ->get('users');
SELECT *
FROM users
INNER JOIN comments ON comments.user_id = profiles.user_id
INNER JOIN profiles ON profiles.user_id = users.id
Update

updateOperation with Inner Join

DB::where('table1.id', 20)->innerJoin('table2.user_id', 'table1.id')->update('table1',
[
    'table1.name'  => 'Example Name',
    'table2.phone' => '0000000000'
]);
UPDATE table1
INNER JOIN table2 ON table2.user_id = table1.id
SET table1.name = 'Example Name' , table2.phone = '0000000000'
WHERE table1.id = 20
Delete

deleteOperation with Inner Join

DB::where('table1.id', 20)->innerJoin('table2.user_id', 'table1.id')->delete('table1');
DELETE table1, table2 FROM table1
INNER JOIN table2 ON table2.user_id = table1.id
WHERE table1.id = 20 

 

 

# OuterJoin ( ZN >= 3.0.0 )


Used to join SQL FULL OUTER JOIN statements between tables . 1. The parameter must contain other table name and column information to be combined.

Parameters
String $ jointablecolumn Table and column information to merge.
String $ tablecolumn There get()should be table and column information for the single use method. Multiple uses may have different table and column information.
return this
uses
DB::outerJoin('comments.user_id', 'profiles.user_id')
  ->outerJoin('profiles.user_id', 'users.id')
  ->get('users');
SQL Code:
SELECT *
FROM users
FULL OUTER JOIN comments ON comments.user_id = profiles.user_id
FULL OUTER JOIN profiles ON profiles.user_id = users.id

 

 

# LeftJoin ( ZN >= 3.0.0 )


Used to join tables with SQL LEFT JOIN statement. 1. The parameter must contain other table name and column information to be combined.

Parameters
String $ jointablecolumn Table and column information to merge.
String $ tablecolumn There get()should be table and column information for the single use method. Multiple uses may have different table and column information.
return this
uses
DB::leftJoin('comments.user_id', 'profiles.user_id')
  ->leftJoin('profiles.user_id', 'users.id')
  ->get('users');
SQL Code:
SELECT *
FROM users
LEFT JOIN comments ON comments.user_id = profiles.user_id
LEFT JOIN profiles ON profiles.user_id = users.id

 

 

# RightJoin ( ZN >= 3.0.0 )


Used to join tables between SQL RIGHT JOIN statement. 1. The parameter must contain other table name and column information to be combined.

Parameters
String $ jointablecolumn Table and column information to merge.
String $ tablecolumn There get()should be table and column information for the single use method. Multiple uses may have different table and column information.
return this
uses
DB::rightJoin('comments.user_id', 'profiles.user_id')
  ->rightJoin('profiles.user_id', 'users.id')
  ->get('users');
SQL Code:
SELECT *
FROM users
RIGHT JOIN comments ON comments.user_id = profiles.user_id
RIGHT JOIN profiles ON profiles.user_id = users.id

 

 

# Union ( ZN >= 4.3.3 )


Used between tables to join with SQL UNION statement.

Parameters
String $ jointablecolumn Table and column information to merge.
return this
uses
$get = DB::select('id', 'name')->union('example')->select('id', 'phone')->get('users');

output($get->stringQuery());
output($get->result());
SELECT id, name FROM example UNION was SELECT phone FROM users
0 => object
(
        id => string "2" (length = 3 )
        name => string 'example' (length = 9 )
)
1 => object
(
        id = > string '3' (length = 3 )
        name => string 'Test' (length = 5 )
)
2 => object
(
        id => string '1' (length = 3 )
        name => string '55332587488' (length = 7 )
)
3 => object
(
        id => string "2" (length = 3 )
        name => string '88996544878' (length = 4 )

 

 

# UnionAll ( ZN >= 4.3.3 )


Tables are used to join SQL UNION ALL statements.

Parameters
String $ jointablecolumn Table and column information to merge.
return this
uses
$get = DB::select('id', 'name')->unionAll('example')->select('id', 'phone')->get('users');

output($get->stringQuery());
output($get->result());
SELECT id, name FROM example UNION ALL SELECT id, phone FROM users
0 => object
(
        id => string "2" (length = 3 )
        name => string 'example' (length = 9 )
)
1 => object
(
        id => string '3' (length = 3 )
        name => string 'Test' (length = 5)
)
2 => object
(
        id => string '1' (length = 3 )
        name => string '55332587488' (length = 7 )
)
3 => object
(
        id => string "2" (length = 3 )
        name => string '88996544878' (length = 4 )

 

 

# TransStart ( ZN >= 2.0.0 )


Transaction queries are those that execute more than one query in sequence and cancel previous queries when there is a problem in one of the queries. In other words, all queries that are subject to this type of query need to work successfully. This removes any problems with the proper functioning of all queries. The transStart () method is used when starting the query . Transaction queries run in MYISAM , InnoDB, or BDB table types rather than MYSQL . Most database platforms support transaction query processing.

Parameters
void
return this
uses
DB::transStart()
  ->transQuery('...queries...')
  ->transQuery('...queries...')
  ->transEnd();

You can use it.

 

 

# TransQuery ( ZN >= 4.3.3 )


Transaction is the method by which queries are executed.

Parameters
String $ query The SQL query to run .
return this
uses
DB::transStart()
  ->transQuery('...queries...')
  ->transQuery('...queries...')
  ->transEnd();

You can use it.

 

 

# TransEnd ( ZN >= 2.0.0 )


transactionFinish the launched query.

void
return Bool
DB::transStart()
  ->transQuery('...queries...')
  ->transQuery('...queries...')
  ->transEnd();

 

 

# Caching ( ZN >= 4.3.6 )


Used to cache the SELECT query. This isdone with the Cache :: library of theZN Framework, so the drivers of this library can be used. The cache: file driver is not very efficient and is not recommended for use with this driver. It can be tried for many queries.

Parameters
scaler $ Time How long the cached query will be stored.
String $ cacheDriver = 'file' One of the caching drivers.
return this
uses
$result = DB::caching('1 day')->get('photos');

output($result->row());
$result = DB::caching('10 second', 'redis')->get('photos');

output($result->result());

 

 

# CleanCaching ( ZN> = 4.3.6 )


Used to delete the cached query. Which query will be used with that query if the prefetch is deleted.

Parameters
void
return this
uses
$result = DB::get('photos');

$result->cleanCaching();

output($result->row());

 

 

# Distinct / DistinctRow ( ZN >= 1.0.4 )


It is the DISTINCT counterpart of the SQL statement. It is used to isolate recurrent data.

Parameters
void
return this
uses
DB::distinct()->select('id', 'name', 'phone')->get('users');
SQL Code: SELECT DISTINCT id, name, phone FROM users

 

 

# Cset ( ZN >= 3.0.0 )


SQL CHARACTER is the equivalent of the SET statement. This statement is used between queries. It is used in conjunction with CREATE commands.

Parameters
String $ set Character set.
return String
uses
echo DB::cset('utf8');
CHARACTER SET utf8

 

 

# Collate ( ZN >= 3.0.0 )


The counterpart of the SQL COLLATE statement. This statement is used between queries. It is used in conjunction with CREATE commands.

Parameters
String $ set The character set.
return String
uses
echo DB::collate('utf8_general_ci');
COLLATE utf8_general_ci

 

 

# Encoding ( ZN >= 3.0.0 )


Allows the use of the above cset () and collate () methods combined. The parameters are predefined with utf8 , utf8_general_ci , respectively . This method is especiallyCREATE DATABASE and CREATE TABLE Used in conjunction with

Parameters
String $ charset = 'utf8' Character set.
String $ collate = 'utf8_general_ci' The character set.
return String
uses
echo DB::encoding(); 
CHARACTER SET utf8 COLLATE utf8_general_ci
echo DB::encoding('latin5', 'latin5_turkish_ci'); 
CHARACTER SET latin5 COLLATE latin5_turkish_ci

Such flexible uses are incompatible with different database platforms.

 

 

# EscapeString / RealEscapeString ( ZN> = 3.0.0 )


These two methods have the same function. It inserts the terstart symbol at the beginning of the quotes in the string.

Parameters
String $ data String to precede.
return String
uses
DB::escapeString("Example'Data");     
DB::realEscapeString("Example'Data"); 

 

 

# CharacterSet ( ZN >= 3.0.0 )


It SELECTcan only get()be used with queries or method.

Parameters
String $ set Character set.
Bool $ return = false If this value is set to true , the method produces output from the string .
return Mixed
uses
echo DB::characterSet('utf8')->getString('users');
SELECT * FROM users CHARACTER SET utf8

You only need to use the function SELECTthat places it in the place where it should be in the syntax.