Database Class

• Calling the DB Class
• Performing a Query
• Retrieving Results from SELECT query
• INSERTing Data
• UPDATEing Data
• Additional Functions

Calling the DB Class

ExpressionEngine has an abstract database layer that allows developers to easily access the MySQL database and also provide many features like automatic escaping of characters and query caching.

To use this abstract layer in your modules you must call the $DB global in every function that will be accessing the database or using the functions in the DB Class.

function database_example()
{
	global $DB;

	$query = $DB->query("SELECT blog_name FROM exp_weblogs");

	if ($query->num_rows > 0)
	{
		foreach($query->result as $row)
		{
			echo $row['blog_name']."<br />\n";
		}
	}
}

Performing a Query

$DB->query() sends a query to the database and will also return back the results, if it is a SELECT query. If you are doing an INSERT or UPDATE, then you do not need to set a variable since there are no results being returned.

// Simple select query
$query = $DB->query("SELECT * FROM exp_weblogs");

// Update, with no variable being set
$DB->query("UPDATE exp_weblogs SET blog_name = 'dog' WHERE blog_name = 'cat'");

Note: When doing any sort of query using user submitted data make sure to use the $DB->escape_str() function (details below) to prevent any problems between MySQL and the data.

Retrieving Results from SELECT query

Upon performing a SELECT query an object containing the results of query will be returned by the function. To check for the number of results returned by the query, use the num_rows value in the returned object.

$results = $DB->query("SELECT * FROM exp_weblogs");

if ($results->num_rows == 0)
{
	exit('No weblogs exist');
}

In many instances, you will know that only a single row of data will be returned from a query. Instead of looping through an array, you can simply use the row array in the object returned.

$results = $DB->query("SELECT * FROM exp_weblogs ORDER BY weblog_id LIMIT 0,1");

$first_weblog = $results->row['blog_name'];

When many rows of data are returned you will wish to loop through the returned array and use the values returned for the fields. ExpressionEngine uses MYSQL_ASSOC, which has MySQL using the field names in the array and not numbers. To access this array, then you will want to use the result array of the object with a foreach loop.

$results = $DB->query("SELECT blog_name, weblog_id FROM exp_weblogs");

if ($results->num_rows > 0)
{
    foreach($results->result as $row)
    {
        echo $row['weblog_id'].' - '.$row['blog_name']."<br />\n";
    }
}

INSERTing Data

Included in the DB Class is a function that makes the inserting of data into the database easier by correctly formatting the INSERT string and escaping the values being inserted. The insert_string() function accepts the name of the table for the insert and an array containing the field names as keys with the values containing the data for those fields.

$data = array('name' => $name, 'email' => $email, 'url' => $url);

$sql = $DB->insert_string('exp_weblog', $data);

// INSERT INTO exp_weblog (name, email, url) VALUES ('Joe', '[email protected]', 'www.joe.com')

$DB->query($sql);

Upon performing an insert you might wish to know the value of the primary key for the row that was added. The DB Class tracks the last insert and will return the primary key value using the insert_id variable of the class.

$DB->query($insert_sql);
$entry_id = $DB->insert_id;

UPDATEing Data

Included in the DB Class is a function that makes the updating of data in the database easier by correctly formatting the UPDATE string and escaping the values being inserted. The update_string() function accepts the name of the table for the update, an array containing the field names as keys with the values containing the updated data for those fields, and the WHERE clause for choosing which rows in the table to update.

$data = array('name' => $name, 'email' => $email, 'url' => $url);

$sql = $DB->update_string('exp_weblog', $data, "author_id = '1'");

// UPDATE exp_weblog SET name = 'Joe', email = '[email protected]', url = 'www.joe.com' WHERE author_id = '1'

$DB->query($sql);

Additional Functions

escape_str will accept a string variable and return that variable prepared for any sql statement to the database.

$query = $DB->query("SELECT FROM exp_comments WHERE url = '".$DB->escape_str($site_url)."'");

affected_rows will return how many rows in the database were affected during the most recent query. Every so often a useful variable to have when performing INSERT, UPDATE, or DELETE queries.

$query = $DB->query("DELETE FROM exp_comments WHERE url = '".$DB->escape_str($site_url)."'");
echo $DB->affected_rows." rows were deleted.";