Database
Read data from database
Connecting to database
Local database
Connect to the application local database.
$Database = $this->get('database');
Database defined in account preferences
Define database connection in account preferences:
database:
- Name: db_connection_name
Type: mysql
Host: mysql.domain.com
Port: 3306
DbName: my_database
Username: testuser
Password: demo
Connect to database using the settings:
$Database = $this->get('database', 'db_connection_name');
Custom connection
Connect to database with given arguments:
$Database = $this->get('database', Array(
'type' => 'mysql',
'host' => sftp.myhost.fi,
'dbname' => 'my_database',
'username' => 'demouser',
'password' => '1234'
));
Fetch data from database
Get all rows that match the given criteria:
$data = $Database->fetchAll('SELECT * FROM tbl_name WHERE field=?', Array($value));
Shorthand for fetching rows with WHERE clause:
$data = $Database->fetchAllWhere('tbl_name', 'field=?', Array($value));
Shorthand for fetching column value with WHERE clause:
$value = $Database->fetchColumnWhere('tbl_name', 'field_name', 'field=?', Array($value));
SelectStatement
new SelectStatement by table name
Get Select statement for certain table:
$Statement = $Database->newSelectStatement('invoice');
$Statement->addSelect('price');
$Statement->addWhere('invoice.price < 100');
Will create a statement:
SELECT
invoice.price as price
FROM
kayttaja_X_invoice AS invoice
WHERE
invoice.price < 100
new SelectStatement by Table
Get Select statement for certain table:
$Invoice = $this->get('table', 'invoice');
$Statement = $Invoice->newSelectStatement();
Will create a statement:
SELECT
invoice.*
FROM
kayttaja_X_invoice AS invoice
WHERE
( invoice.poistettu = 0 )
ORDER BY
lasku.laskupaiva desc
- Note: Creating SelectStatement from Table -object, it will set
- ->setWhereDeleted(true) -> Sets WHERE table.poistettu = 0
- ->setTablePreferences(true) -> Sets ORDER BY as default, according to table preferences
new SelectStatement by Page
Get Select statement for certain page data:
$Invoice = $this->get('page', 'invoice', '1107');
$Statement = $Invoice->newSelectStatement();
Will create a statement:
SELECT
invoice.*
FROM
kayttaja_X_invoice AS invoice
WHERE
( invoice.nr = 1107 )
new SelectStatement by Page Join -field
Get Select statement from JOIN field: Fetch page Invoice rows:
$Invoice = $this->get('page', 'invoice', '1107');
$Statement = $Invoice->newSelectStatement('invoice_rows');
// OR
$Statement = $Invoice->getField('invoice_rows')->newSelectStatement();
Will create a statement:
SELECT
invoice_row.*
FROM
kayttaja_X_invoice_row AS invoice_row
LEFT JOIN kayttajatyyppi_X_liitos_XXXXX ON ( kayttajatyyppi_1_liitos_XXXXX.lapsi_nr = invoice_row.nr )
LEFT JOIN kayttaja_X_invoice AS invoice ON ( kayttajatyyppi_1_liitos_XXXXX.isa_nr = invoice.nr )
WHERE
( invoice_row.poistettu = 0 )
AND ( invoice.nr = '1107' )
- Note: Creating SelectStatement from Field -object, it will set
- ->setWhereDeleted(true) -> Sets WHERE table.poistettu = 0
- ->setTablePreferences(true) -> Sets ORDER BY as default, according to table preferences
new SelectStatement by Page Reference -field
Get Select statement from REFERENCE field: Customer Invoices
$Customer = $this->get('page', 'customer', '1107');
$Statement = $Customer->newSelectStatement('invoice__customer');
// OR
$Statement = $Customer->getField('invoice__customer')->newSelectStatement();
Will create a statement:
SELECT
invoice.*
FROM
kayttaja_X_invoice AS invoice
LEFT JOIN kayttaja_1_customer AS customer ON ( invoice.customer = customer.nr )
WHERE
( invoice.poistettu = 0 )
AND ( invoice.customer = '1183' )
GROUP BY
invoice.nr
addFrom() - Field data
Join and reference -fields: Reference pages
$Statement->addFrom('customer');
$Statement->setWhere('customer.name LIKE :search', Array('search'=> '%'.$search.'%'))
$Statement->setSelect('customer.name'));
Fields with additional data: Person, Company, Address, File, Money
$Statement->addFrom('customer.billing_address');
// IS SHORTHAND FOR:
$Statement->addFrom('customer_billing_address', 'customer.billing_address');
$Statement->setSelect('customer_billing_address.katusoite'));
- Person
- sukunimi
- etunimi
- Company
- nimi
- Address
- katuosoite
- katuosoite2
- postinuero
- pl
- toimipaikka
- longitude
- latitude
- File
- fileid
- filename
- filesize
- uploaded
- Money
- alviton
- alvillinen
- alv
- alvosuus
- kaanteinen_alv Boolean
- summa
- valuutta String
- kutssi
- kurssipaiva Date
addFrom() - Page Info
- Page info is table, that holds common page info:
- otsikko String Page title (as set in table preferences)
- kuvaus String Additional info (as set in table preferences)
- kuva String Thumbnail url
- tyyli String Page color
- hakusana String Query terms
- avainsana String Query key -terms (as set in table preferences)
Page info -table:
$Statement->addFrom('info'); // Join XXX_sivu as invoice_info
// IS SHORTHAND FOR:
$Statement->addFrom('invoice_info', 'info');
$Statement->setSelect('invoice_info.otsikko');
Field target page info -table:
$Statement->addFrom('customer.info');
// IS SHORTHAND FOR:
$Statement->addFrom('customer_info', 'customer.info');
$Statement->addSelect('customer_info.otsikko'));
Full method list
| Method | Return | Description |
|---|---|---|
| getPdo() | PDO | Get the PDO object |
| execute($query, $values) | PDOStatement | Execute query. |
| fetch($query, $values) | array | Execute query and return first row in associative array. |
| fetchAll($query, $values) | array | Execute query and return rows in associative array. |
| fetchUniqueValues($query[,$values]) | array | Execute query and return unique values of first column. |
| insert($tbl, $data) | bool | Insert data to table. |
| update($tbl, $data, $where, [$values]) | bool | Update data in table. |
| delete($tbl, $where, [$values]) | bool | Delete data in table. |
Where -methods
Where -methods take key parameters and create the base of the query, which is then finished by $where clause.
| Method | Return | Description |
|---|---|---|
| fetchAllWhere($tbl, $where, [$values]) | array | Select all -statement with where, return rows in associative array. |
| fetchWhere($tbl, $where, [$values]) | array | Select all -statement with where, return first row in associative array. |
| fetchColumnWhere($tbl, $column, $where, [$values]) | string | Return the value in single column. |
| countWhere($tbl, $where, [$values]) | int | Return number of found rows. |
Schema -methods
| Table | Return | Description |
|---|---|---|
| isTable($tbl) | bool | Return true, if table exists. |
| renameTable($tbl, $newName) | bool | Return true, if rename table success. |
| dropTable($tbl) | - | Remove table form database. |
| clearTable($tbl[,$param]) | - | Clear all data from table. param[AUTO_INCREMENT] set new increment value. |
| getTableStatus($tbl) | array | Get table status information. |
| Column | Return | Description |
|---|---|---|
| isColumn($tbl,$column) | bool | Return true, if column exists in table. |
| addColumn($tbl,$column,$statement) | - | Add column to table. |
| dropColumn($tbl,$column) | - | Delete column from table. |
| getColumn($tbl,$column) | bool | Get column information. |
| getColumns($tbl) | array | Get information of all columns. |
| Index | Return | Description |
|---|---|---|
| isIndex($tbl,$name) | bool | Return true, if index exists in table. |
| addIndex($tbl,$name,$statement) | - | Create new index. |
| dropIndex($tbl,$name) | - | Remove index. |
| getIndexes($tbl) | array | Get information of all indexes. |
| View | Return | Description |
|---|---|---|
| dropView($name) | bool | Remove view form database. |
Arguments
Arguments used in methods.
| Argument | Type | Description |
|---|---|---|
| $tbl | string | Database table name |
| $column | string | Column nme in database |
| $query | string | MySQL query |
| $where | string | MySQL query from WHERE statement. (Beginning of the query is added by function). |
| $data | array | Row data in associative array. |
| $values | array | MySQL query variables |
DatabaseSelectStatement
Creating SELECT -statement.
| Set statement | Return | Description |
|---|---|---|
| addFrom(alias[,field]) | this | Add LEFT JOIN |
| addFrom(Array(alias=>[field])) | this | Add multiple LEFT JOIN |
| addSelect(name[,clause]) | this | Add SELECT -statement. If clause not given, parses clause from name. |
| addSelect(Array(name=>[clause])) | this | Add multple SELECT -statement |
| addWhere(clause[,variables]) | this | Add AND WHERE -statement |
| addWhere(array(clause)[,variables]) | this | Add multiple AND WHERE -statements. |
| setWhereDeleted([bool]) | this | Add WHERE deleted/not deleted. |
| addGroup(string) | this | Add GROUP BY -definition- |
| setGroup(string) | this | Clear GROUP BY definitions. Add new definition. |
| setGroup(boolean) | this | Clear GROUP BY definitions. Sets flag if add default GROUP BY basetable.nr, if no group definitions. |
| addHaving(clause[,variables]) | this | Add AND HAVING -statement |
| addHaving(array(clause)[,variables]) | this | Add multiple AND HAVING -statements. |
addOrder($field, $direction='') | this | Add order definition. setLimit(int) | this | Max number of rows setStart(int) | this | Start from row x setOffset(int) | this | Skip first x rows setVariable(name,value) | this | Set variable value addVariables(Array(name=>value)) | this | Add multiple variables. Associative or sequential array. setVariables(Array(name=>value)) | this | Set statement variables. Associative or sequential array. Overrides all variables! setTablePreferences(bool) | this | If TRUE, uses table preferences. Set order, if order is not set. addParams(array) | this | Add parametres from array: Array(from,where,select,...)
| Execute statement | Return | Description |
|---|---|---|
| fetchAll() | array | All rows. |
| fetch() | array | First row. |
| countAll() | number | Number of rows (without limit, offset) |
| count() | number | Number of rows (with limit,offset) |
| Statement info | Return | Description |
|---|---|---|
| setLogSql([bool,string,array]) | this | Set info to save in LogSql |
| getSqlStatement() | string | Get sql statement |
| getVariables() | array | Get statement variabels |
| getTblList() | array | Get list of tables in statement |
| isError() | bool | Return TRUE if there was an error in statement |
| getErrorMessage() | string | Get error message of lst execute |