Actions

Difference between revisions of "Inserting, Updating and Removing data using JDatabase"

From Joomla! Documentation

(Remove some try/catch stuff)
m (added Category:Database using HotCat)
 
(24 intermediate revisions by 5 users not shown)
Line 1: Line 1:
There are two parts to JDatabase:
+
{{version|2.5,3.x}}
# Building a query - this allows you to be multi-database compatible.
+
{{dablink|'''Version Note:''' While this document pertains to Joomla! 2.5 and 3.x, <code>$db->query()</code> throws a deprecated notice in Joomla 3.0+. In that case, change <code>$db->query()</code> to <code>$db->execute()</code>. However note <code>$db->execute()</code> does not work in Joomla 2.5.}}
# Retrieving the results of a query - allowing you to retrieve the contents of your database query in a number of ways.
+
  
This section of the documentation looks at building a query to the database with JDatabase.
+
This tutorial is split into two independent parts:
 +
* Inserting, updating and removing data from the database.
 +
* Selecting data from one or more tables and retrieving it in a variety of different forms
 +
 
 +
This section of the documentation looks at inserting, updating and removing data from a database table. To see the other part [[Selecting_data_using_JDatabase|click here]]
  
 
== Introduction==
 
== Introduction==
Line 29: Line 32:
  
 
Some of the more frequently used methods include; select, from, join, where and order. There are also methods such as insert, update and delete for modifying records in the data store. By chaining these and other method calls, you can create almost any query against your data store without compromising portability of your code.
 
Some of the more frequently used methods include; select, from, join, where and order. There are also methods such as insert, update and delete for modifying records in the data store. By chaining these and other method calls, you can create almost any query against your data store without compromising portability of your code.
 
==Selecting Records from a Single Table==
 
 
Below is an example of creating a database query using the JDatabaseQuery class. Using the select, from, where and order methods, we can create queries which are flexible, easily readable and portable:
 
 
<source lang="php">
 
// Get a db connection.
 
$db = JFactory::getDbo();
 
 
// Create a new query object.
 
$query = $db->getQuery(true);
 
 
// Select all records from the user profile table where key begins with "custom.".
 
// Order it by the ordering field.
 
$query->select(array('user_id', 'profile_key', 'profile_value', 'ordering'));
 
$query->from('#__user_profiles');
 
$query->where('profile_key LIKE \'custom.%\'');
 
$query->order('ordering ASC');
 
 
// Reset the query using our newly populated query object.
 
$db->setQuery($query);
 
 
// Load the results as a list of stdClass objects.
 
$results = $db->loadObjectList();
 
</source>
 
 
The query can also be chained to simplify further:
 
 
<source lang="php">
 
$query
 
    ->select(array('user_id', 'profile_key', 'profile_value', 'ordering'))
 
    ->from('#__user_profiles')
 
    ->where('profile_key LIKE \'custom.%\'')
 
    ->order('ordering ASC');
 
</source>
 
 
Chaining can become useful when queries become longer and more complex.
 
 
==Selecting Records from Multiple Tables==
 
 
Using the JDatabaseQuery's [http://api.joomla.org/Joomla-Platform/Database/JDatabaseQuery.html#join join] methods, we can select records from multiple related tables. The generic "join" method takes two arguments; the join "type" (inner, outer, left, right) and the join condition. In the following example you will notice that we can use all of the keywords we would normally use if we were writing a native SQL query, including the AS keyword for aliasing tables and the ON keyword for creating relationships between tables. Also note that the table alias is used in all methods which reference table columns (I.e. select, where, order).
 
 
<source lang="php">
 
// Get a db connection.
 
$db = JFactory::getDbo();
 
 
// Create a new query object.
 
$query = $db->getQuery(true);
 
 
// Select all articles for users who have a username which starts with 'a'.
 
// Order it by the created date.
 
$query
 
    ->select(array('a.*', 'b.username', 'b.name'))
 
    ->from('#__content AS a')
 
    ->join('INNER', '#__users AS b ON (a.created_by = b.id)')
 
    ->where('b.username LIKE \'a%\'')
 
    ->order('a.created DESC');
 
 
// Reset the query using our newly populated query object.
 
$db->setQuery($query);
 
 
// Load the results as a list of stdClass objects.
 
$results = $db->loadObjectList();
 
</source>
 
 
The join method above enables us to query both the content and user tables, retrieving articles with their author details. There are also convenience methods for [http://api.joomla.org/Joomla-Platform/Database/JDatabaseQuery.html#innerJoin inner], [http://api.joomla.org/Joomla-Platform/Database/JDatabaseQuery.html#leftJoin left], [http://api.joomla.org/Joomla-Platform/Database/JDatabaseQuery.html#rightJoin right] and [http://api.joomla.org/Joomla-Platform/Database/JDatabaseQuery.html#outerJoin outer] joins.
 
 
We can use multiple joins to query across more than two tables:
 
 
<source lang="php">
 
$query
 
    ->select(array('a.*', 'b.username', 'b.name', 'c.*', 'd.*'))
 
    ->from('#__content AS a')
 
    ->join('INNER', '#__users AS b ON (a.created_by = b.id)')
 
    ->join('LEFT', '#__user_profiles AS c ON (b.id = c.user_id)')
 
    ->join('RIGHT', '#__categories AS d ON (a.catid = d.id)')
 
    ->where('b.username LIKE \'a%\'')
 
    ->order('a.created DESC');
 
</source>
 
 
Notice how chaining makes the source code much more readable for these longer queries.
 
  
 
==Inserting a Record==
 
==Inserting a Record==
Line 136: Line 58:
 
     ->values(implode(',', $values));
 
     ->values(implode(',', $values));
  
// Reset the query using our newly populated query object.
+
// Set the query using our newly populated query object and execute it.
 
$db->setQuery($query);
 
$db->setQuery($query);
 +
$db->query();
 
</source>
 
</source>
  
 
===Using an Object===
 
===Using an Object===
  
The JDatabaseDriver also class provides us with a convenience method for saving an object directly to the database allowing us to add a record to a table without writing a single line of SQL.
+
The JDatabaseDriver class also provides a convenient method for saving an object directly to the database allowing us to add a record to a table without writing a single line of SQL.
  
 
<source lang="php">
 
<source lang="php">
Line 174: Line 97:
 
// Fields to update.
 
// Fields to update.
 
$fields = array(
 
$fields = array(
     'profile_value=\'Updating custom message for user 1001.\'',
+
     $db->quoteName('profile_value') . ' = ' . $db->quote('Updating custom message for user 1001.'),
     'ordering=2');
+
     $db->quoteName('ordering') . ' = 2'
 +
);
  
 
// Conditions for which records should be updated.
 
// Conditions for which records should be updated.
 
$conditions = array(
 
$conditions = array(
     'user_id=42',  
+
     $db->quoteName('user_id') . ' = 42',  
     'profile_key=\'custom.message\'');
+
     $db->quoteName('profile_key') . ' = ' . $db->quote('custom.message')
 +
);
  
 
$query->update($db->quoteName('#__user_profiles'))->set($fields)->where($conditions);
 
$query->update($db->quoteName('#__user_profiles'))->set($fields)->where($conditions);
Line 186: Line 111:
 
$db->setQuery($query);
 
$db->setQuery($query);
  
$result = $db->execute();
+
$result = $db->query();
 
</source>
 
</source>
  
Line 216: Line 141:
 
==Deleting a Record==
 
==Deleting a Record==
  
Just as there are select, insert and update method calls, there is also a delete method for remove records from the database.
+
Finally, there is also a delete method to remove records from the database.
  
 
<source lang="php">
 
<source lang="php">
Line 225: Line 150:
 
// delete all custom keys for user 1001.
 
// delete all custom keys for user 1001.
 
$conditions = array(
 
$conditions = array(
     'user_id=1001',  
+
     $db->quoteName('user_id') . ' = 1001',  
     'profile_key LIKE \'custom.%\'');
+
     $db->quoteName('profile_key') . ' = ' . $db->quote('custom.%')
 +
);
  
 
$query->delete($db->quoteName('#__user_profiles'));
 
$query->delete($db->quoteName('#__user_profiles'));
Line 234: Line 160:
  
  
$result = $db->execute();
+
$result = $db->query();
</source>
+
 
+
==Transactions==
+
 
+
The Joomla 12.1 Framework introduces SQL transactions (where supported) via the JDatabaseDriver's transactionStart, transactionCommit and transactionRollback. This supersedes the queryBatch method which was introduced in 11.1.
+
 
+
IMPORTANT NOTE: Transactions can only be used against transaction-aware storage engine such as InnoDB (many tables in Joomla still use the MyISAM storage engine which cannot support transactions).
+
 
+
<source lang="php">
+
$db = JFactory::getDbo();
+
 
+
try {
+
    $db->transactionStart();
+
+
    $query = $db->getQuery(true);
+
   
+
    $values = array($db->quote('TEST_CONSTANT'), $db->quote('Custom'), $db->quote('/path/to/translation.ini'));
+
   
+
    $query->insert($db->quoteName('#__overrider'));
+
    $query->columns($db->quoteName(array('constant', 'string', 'file')));
+
    $query->values(implode(',',$values));
+
 
+
    $db->setQuery($query);
+
    $result = $db->execute();
+
 
+
    $db->transactionCommit();
+
} catch (Exception $e) {
+
    $db->transactionRollback();
+
    // catch any database errors.
+
}
+
 
</source>
 
</source>
  
Anything between the transactionStart and transactionCommit methods are not executed until transactionCommit is called. If an exception occurs, we can roll back the changes using the transactionRollback method. This allows us to return the database to the original state if a problem occurs even though we may execute a number of changes to the database's tables.
+
[[Category:Database]]

Latest revision as of 03:13, 11 May 2014

This tutorial is split into two independent parts:

  • Inserting, updating and removing data from the database.
  • Selecting data from one or more tables and retrieving it in a variety of different forms

This section of the documentation looks at inserting, updating and removing data from a database table. To see the other part click here

Contents

Introduction

Joomla provides a sophisticated database abstraction layer to simplify the usage for third party developers. New versions of the Joomla Platform API provide additional functionality which extends the database layer further, and includes features such as connectors to a greater variety of database servers and the query chaining to improve readability of connection code and simplify SQL coding.

Joomla can use different kinds of SQL database systems and run in a variety of environments with different table-prefixes. In addition to these functions, the class automatically creates the database connection. Besides instantiating the object you need just two lines of code to get a result from the database in a variety of formats. Using the Joomla database layer ensures a maximum of compatibility and flexibility for your extension.

The Query

Joomla's database querying has changed since the new Joomla Framework was introduced "query chaining" is now the recommended method for building database queries (although string queries are still supported).

Query chaining refers to a method of connecting a number of methods, one after the other, with each method returning an object that can support the next method, improving readability and simplifying code.

To obtain a new instance of the JDatabaseQuery class we use the JDatabaseDriver getQuery method:

$db = JFactory::getDbo();
 
$query = $db->getQuery(true);

The JDatabaseDriver::getQuery takes an optional argument, $new, which can be true or false (the default being false).

To query our data source we can call a number of JDatabaseQuery methods; these methods encapsulate the data source's query language (in most cases SQL), hiding query-specific syntax from the developer and increasing the portability of the developer's source code.

Some of the more frequently used methods include; select, from, join, where and order. There are also methods such as insert, update and delete for modifying records in the data store. By chaining these and other method calls, you can create almost any query against your data store without compromising portability of your code.

Inserting a Record

Using SQL

The JDatabaseQuery class provides a number of methods for building insert queries, the most common being insert, columns and values.

// Get a db connection.
$db = JFactory::getDbo();
 
// Create a new query object.
$query = $db->getQuery(true);
 
// Insert columns.
$columns = array('user_id', 'profile_key', 'profile_value', 'ordering');
 
// Insert values.
$values = array(1001, $db->quote('custom.message'), $db->quote('Inserting a record using insert()'), 1);
 
// Prepare the insert query.
$query
    ->insert($db->quoteName('#__user_profiles'))
    ->columns($db->quoteName($columns))
    ->values(implode(',', $values));
 
// Set the query using our newly populated query object and execute it.
$db->setQuery($query);
$db->query();

Using an Object

The JDatabaseDriver class also provides a convenient method for saving an object directly to the database allowing us to add a record to a table without writing a single line of SQL.

// Create and populate an object.
$profile = new stdClass();
$profile->user_id = 1001;
$profile->profile_key='custom.message';
$profile->profile_value='Inserting a record using insertObject()';
$profile->ordering=1;
 
// Insert the object into the user profile table.
$result = JFactory::getDbo()->insertObject('#__user_profiles', $profile);

Notice here that we do not need to escape the table name; the insertObject method does this for us.

The insertObject method will throw a error if there is a problem inserting the record into the database table.

We need to ensure that the record does not exist before attempting to insert it, so adding some kind of record check before executing the insertObject method would be recommended.

Updating a Record

Using SQL

The JDatabaseQuery class also provides methods for building update queries, in particular update and set. We also reuse another method which we used when creating select statements, the where method.

$db = JFactory::getDbo();
 
$query = $db->getQuery(true);
 
// Fields to update.
$fields = array(
    $db->quoteName('profile_value') . ' = ' . $db->quote('Updating custom message for user 1001.'),
    $db->quoteName('ordering') . ' = 2'
);
 
// Conditions for which records should be updated.
$conditions = array(
    $db->quoteName('user_id') . ' = 42', 
    $db->quoteName('profile_key') . ' = ' . $db->quote('custom.message')
);
 
$query->update($db->quoteName('#__user_profiles'))->set($fields)->where($conditions);
 
$db->setQuery($query);
 
$result = $db->query();

Using an Object

Like insertObject, the JDatabaseDriver class provides a convenience method for updating an object.

Below we will update our custom table with new values using an existing id primary key:

// Create an object for the record we are going to update.
$object = new stdClass();
 
// Must be a valid primary key value.
$object->id = 1;
$object->title = 'My Custom Record';
$object->description = 'A custom record being updated in the database.';
 
// Update their details in the users table using id as the primary key.
$result = JFactory::getDbo()->updateObject('#__custom_table', $object, 'id');

Just like insertObject, updateObject takes care of escaping table names for us.

The updateObject method will throw a error if there is a problem inserting the record into the database table.

We need to ensure that the record already exists before attempting to update it, so we would probably add some kind of record check before executing the updateObject method.

Deleting a Record

Finally, there is also a delete method to remove records from the database.

$db = JFactory::getDbo();
 
$query = $db->getQuery(true);
 
// delete all custom keys for user 1001.
$conditions = array(
    $db->quoteName('user_id') . ' = 1001', 
    $db->quoteName('profile_key') . ' = ' . $db->quote('custom.%')
);
 
$query->delete($db->quoteName('#__user_profiles'));
$query->where($conditions);
 
$db->setQuery($query);
 
 
$result = $db->query();