Database

Please note: This page is under construction and has not been finished yet.
Introduction
Use the following to retrieve the database class object
$sql = e107::getDb();
Basic database methods 
select()
Selecting data from a database table
$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 
$sql->select('tablename', 'field1, field2', 'field_id = 1');
Example #2: Using arguments 
$sql->select("comments", "*", "comment_item_id = '$id' AND comment_type = '1' ORDER BY comment_datestamp");
Example #3: Using arguments with noWhere option
$sql->select("chatbox", "*", "ORDER BY cb_datestamp DESC LIMIT $from, ".$view, true);
Example #4: BIND support
$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:
$sql->select('tablename', 'field1, field2', 'field_id = 1');
​
while($row = $sql->fetch())
{
    echo $row['field1'];
}
insert()
Inserting data into a database table:
$insert = array(
   'data'  => array('field1' => 'value1', 'field2' => 'value2'),
   'WHERE' => 'field_id = 1'
);
​
$sql->insert('tablename', $insert);
update()
Updating information in a database:
$update = array(
   'data'  => array('field1' => 'value1', 'field2' => 'value2'),
   'WHERE' => 'id = 1'
);
​
$sql->update('tablename', $update);
retrieve()
Combined select() and fetch() method.
$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
$string = $sql->retrieve('user', 'user_email', 'user_id = 1');
Example #2: Get multiple table-row values
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)
$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 
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' 
$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 
$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.
$sql->delete("user", "user_id = 2");
gen()
Generic query function to use various SQL commands. 
Example: perform a JOIN with gen():
$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()
$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()
$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. 
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()
​

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

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

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

Parameter	Type	Description
database	string	Database name
prefix	string	Prefix of the database tables (e.g. "e107_"). Defaults to MPREFIX.
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

Last modified 1yr ago