# 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()](/classes-and-methods/database.md#select) and [fetch()](/classes-and-methods/database.md#fetch) method.

```php
$sql->retrieve($table = null, $fields = null, $where = null, $multi = false, $indexField = null, $debug = false)
```

| Parameter  | Type    | Description                                                                                                                                                                                                                              |
| ---------- | ------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| table      | string  | <p>Name of the database table to retrieve data from. </p><p>If empty, enters fetch only mode</p>                                                                                                                                         |
| fields     | string  | <p>Comma separated list of fields or "<code>\*</code>" or a single field name (get one); <br><br>If <code>$fields</code> is of type boolean and <code>$where</code> is not found, <code>$fields</code> overrides <code>$multi</code></p> |
| where      | string  | WHERE/ORDER/LIMIT etc. clause.                                                                                                                                                                                                           |
| multi      | string  | <p>If set to true, fetch all (multi mode)</p><p><em>Default: false</em></p>                                                                                                                                                              |
| indexField | boolean | <p>Field name to be used for indexing when in multi mode</p><p><em>Default:  null</em></p>                                                                                                                                               |
| debug      | boolean | <p>....</p><p><em>Default: false</em></p>                                                                                                                                                                                                |

#### 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"]."<br/>";  
	}
}
```

#### 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&#x20;

```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'&#x20;

```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&#x20;

```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.&#x20;

#### *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 ");
```

## &#x20;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 | <p>force a new link connection if set to true <br><em>Default: false</em></p> |

### 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](/getting-started/database-structure.md#database-prefix). |
| multiple  | boolean | Set to true to maintain connection to a secondary database                                                                    |
| newLink   | boolean | <p>force a new link connection if set to true <br><em>Default: false</em></p>                                                 |

### getLastErrorNumber()

### getLastErrorText()


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://devguide.e107.org/classes-and-methods/database.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.