# Database
{% hint style="danger" %}
**Please note:** This page is under construction and has not been finished yet.
{% endhint %}
## Introduction
Use the following to retrieve the database class object
```php
$sql = e107::getDb();
```
## Basic database methods
### select()
Selecting data from a database table
```php
$sql->select($table, $fields = '*', $arg = '', $noWhere = false, $debug = false, $log_type = '', $log_remark = '')
```
| Parameter | Type | Description | Mandatory? |
| ----------- | ------------- | ------------------------------------------------------------------------- | ---------- |
| **table** | string | Name of the database table | **Yes** |
| fields | string | Comma separated list of fields or "`*`" or a single field name (get one); | |
| arg | string\|array | .... | |
| noWhere | boolean | | |
| debug | boolean | | |
| log type | | | |
| log\_remark | | | |
#### Example #1: Simple select
```php
$sql->select('tablename', 'field1, field2', 'field_id = 1');
```
#### Example #2: Using arguments
```php
$sql->select("comments", "*", "comment_item_id = '$id' AND comment_type = '1' ORDER BY comment_datestamp");
```
#### Example #3: Using arguments with noWhere option
```php
$sql->select("chatbox", "*", "ORDER BY cb_datestamp DESC LIMIT $from, ".$view, true);
```
#### Example #4: BIND support
```php
$sql->select('user', 'user_id, user_name', 'user_id=:id OR user_name=:name ORDER BY user_name', array('id' => 999, 'name'=>'e107'))
```
### fetch()
Selecting, looping through and displaying selected data with the fetch() method:
```php
$sql->select('tablename', 'field1, field2', 'field_id = 1');
while($row = $sql->fetch())
{
echo $row['field1'];
}
```
### insert()
Inserting data into a database table:
```php
$insert = array(
'data' => array('field1' => 'value1', 'field2' => 'value2'),
'WHERE' => 'field_id = 1'
);
$sql->insert('tablename', $insert);
```
### update()
Updating information in a database:
```php
$update = array(
'data' => array('field1' => 'value1', 'field2' => 'value2'),
'WHERE' => 'id = 1'
);
$sql->update('tablename', $update);
```
### retrieve()
Combined [select()](#select) and [fetch()](#fetch) method.
```php
$sql->retrieve($table = null, $fields = null, $where = null, $multi = false, $indexField = null, $debug = false)
```
| Parameter | Type | Description |
| ---------- | ------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| table | string | Name of the database table to retrieve data from.
If empty, enters fetch only mode
|
| fields | string | Comma separated list of fields or "\*" or a single field name (get one);
If $fields is of type boolean and $where is not found, $fields overrides $multi
|
| where | string | WHERE/ORDER/LIMIT etc. clause. |
| multi | string | If set to true, fetch all (multi mode)
Default: false
|
| indexField | boolean | Field name to be used for indexing when in multi mode
Default: null
|
| debug | boolean | ....
Default: false
|
#### Example #1: Get a single value
```php
$string = $sql->retrieve('user', 'user_email', 'user_id = 1');
```
#### Example #2: Get multiple table-row values
```php
if($allRows = $sql->retrieve('user', 'user_name, user_email', '', true))
{
foreach($allRows as $row)
{
echo $row["user_name"]." - ".$row["user_email"]."
";
}
}
```
#### Example #3: Fetch all, don't append WHERE to the query, index by user\_id, noWhere auto detected (string starts with upper case ORDER)
```php
$array = $sql->retrieve('user', 'user_id, user_email, user_name', 'ORDER BY user_email LIMIT 0,20', true, 'user_id');
```
#### Example #4: Same as above but retrieve() is only used to fetch, not useable for single return value
```php
if($sql->select('user', 'user_id, user_email, user_name', 'ORDER BY user_email LIMIT 0,20', true))
{
$array = $sql->retrieve(null, null, null, true, 'user_id');
}
```
#### Example #5: Using whole query example, in this case default mode is 'one'
```php
$array = $sql->retrieve('
SELECT p., u.user_email, u.user_name
FROM `#user` AS u
LEFT JOIN `#myplug_table` AS p
ON p.myplug_table = u.user_id
ORDER BY u.user_email LIMIT 0,20
');
```
#### Example #6: Using whole query example, multi mode - $fields argument mapped to $multi
```php
$array = $sql->retrieve('SELECT u.user_email, u.user_name FROM #user AS U ORDER BY user_email LIMIT 0,20', true);
```
### delete()
Delete a record from a database table.
```php
$sql->delete("user", "user_id = 2");
```
### gen()
Generic query function to use various SQL commands.
#### *Example: perform a JOIN with gen():*
```php
$sql->gen("SELECT f.*,u.user_name FROM #faqs AS f LEFT JOIN #users as u ON f.faq_author = u.user_id ");
```
## Advanced database methods
### connect()
```php
$sql->connect($mySQLserver, $mySQLuser, $mySQLpassword, $newLink = false)
```
| Parameter | Type | Description |
| ------------- | ------- | ----------------------------------------------------------------------------- |
| mySQLserver | string | IP or hostname of the SQL server |
| mySQLuser | string | SQL username |
| mySQLpassword | string | SQL password |
| newLink | boolean | force a new link connection if set to true
Default: false
|
### count()
### database()
```php
$sql->database($database, $prefix = MPREFIX, $multiple=false)
```
| Parameter | Type | Description |
| --------- | ------- | --------------------------------------------------------------------------------------------------------------------------------------------------- |
| database | string | Database name |
| prefix | string | Prefix of the database tables (e.g. "e107\_"). Defaults to [MPREFIX](https://devguide.e107.org/getting-started/database-structure#database-prefix). |
| multiple | boolean | Set to true to maintain connection to a secondary database |
| newLink | boolean | force a new link connection if set to true
Default: false
|
### getLastErrorNumber()
### getLastErrorText()