Joomla! Documentation - User contributions [en]

Inserting, Updating and Removing data using JDatabase

2023-01-29T02:34:09Z

Blueline0: Added $this->getDatabse();

<noinclude><languages /></noinclude>
<noinclude>{{Joomla version|version=3.x}}{{Joomla version|version=2.5|status=eos}}</noinclude>
{{-}}
{{tip|<translate>
Note many examples online use <code>$db->query()</code> instead of <code>$db->execute()</code>. This was the old method in Joomla 1.5 and 2.5 and will throw a deprecated notice in Joomla 3.0+.</translate>|title=<translate>
Version Note</translate>}}

<translate>
This tutorial is split into two independent parts:</translate>
<translate>
* Inserting, updating and removing data from the database.</translate>
<translate>
* Selecting data from one or more tables and retrieving it in a variety of forms.</translate>

<translate>
This section examines inserting, updating and removing data from a database table. See also the [[S:MyLanguage/Selecting_data_using_JDatabase|other part]].</translate>

<translate>
== Introduction== 
</translate>
<translate>
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.</translate>

<translate>
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.</translate>

<translate>
==The Query== 
</translate>
<translate>
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).</translate>

<translate>
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.</translate>

<translate>
To obtain a new instance of the JDatabaseQuery class we use the JDatabaseDriver getQuery method:</translate>

<source lang="PHP">
// Use one of the following depending on the file and version of Joomla
$db = $this->_db; // use in Table class, also works in Model classes, J4 onwards
$db = $this->getDatabase(); // only works in the Field, Model or Table classes, J4 onwards

$db = JFactory::getDbo(); //deprecated J3, removed in J4
$db = Factory::getDbo(); //deprecated J4 - requires use Joomla\CMS\Factory;
$db = $this->getDbo(); // only works in the model or table classes, deprecated J5, use $this->getDatabse();
$db = Factory::getContainer()->get('DatabaseDriver'); // Not well documented

$query = $db->getQuery(true);
</source>

<translate>
The <tt>JDatabaseDriver::getQuery</tt> takes an optional argument, ''$new'', which can be true or false (the default being false).</translate>

<translate>
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.</translate>

<translate>
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.</translate>

<translate>

==Inserting a Record== 
</translate>
<translate>
===Using SQL=== 
</translate>

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

<source lang="php">
// Get a db connection.
$db = JFactory::getDbo(); //deprecated J3, removed in J4
$db = Factory::getDbo(); //deprecated J4 - requires use Joomla\CMS\Factory;
$db = $this->getDbo(); // only works in the model or table classes, deprecated J4
$db = Factory::getContainer()->get('DatabaseDriver'); //J4 onwards, outside of the Model or Table
$db = $this->_db; //only works in the model or table classes, J4 onwards

// 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->execute();
</source>
<translate>

(Here the <tt>quoteName()</tt> function adds appropriate quotes around the table and column names to avoid conflicts with any database reserved word, now or in the future.)</translate>
<translate>

To get the ID of the row that you just inserted, you can use the ''insertid'' method, for example.</translate>
<source lang="php">
// Get the row that was just inserted
$new_row_id = $db->insertid();
</source>

<translate>
====How to Store Empty Value as NULL==== 
</translate>
<translate>

If your default value of a column is NULL, you should not add that column name in the array. Let the database system store NULL as the default value.
If the default value of a column is not NULL and it is allowed to store NULL values, you should specify that in the code. See how to do it in the following example.
</translate>

<source lang="php">
// Get a db connection.
$db = JFactory::getDbo();

// Create a new query object.
$query = $db->getQuery(true);

/** First Case [NULL as default value] **/
// The column 'profile_value' has NULL as default value. So, we will not add it to the array. The database engine will store NULL as value for column 'profile_value'.
// $columns = array('user_id', 'profile_key', 'profile_value', 'ordering');
$columns = array('user_id', 'profile_key', 'ordering');

// Insert values.
$values = array(1001, $db->quote('custom.message'), 1);

/** Second Case [string as default value and you can also store NULL value] **/
// The column 'profile_value' has empty string '' as default value but we can also store NULL value. So, we have to add the column name and NULL value to $columns and $values.
$columns = array('user_id', 'profile_key', 'profile_value', 'ordering');

// Insert values.
$values = array(1001, $db->quote('custom.message'), $db->quote('NULL'), 1);
</source>

<translate>

===Using an Object=== 
</translate>
<translate>
The <tt>JDatabaseDriver</tt> 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.</translate>

<source lang="php">
// 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);
</source>

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

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

<translate>
If you are providing a unique primary key value (as in the example above), it is highly recommended that you select from the table by that column value before attempting an insert.</translate>

<translate>

If you are simply inserting the next row in your table (i.e. the database generates a primary key value), you can specify the primary key column-name as the third parameter of the insertObject() method and the method will update the object with the newly generated primary key value.</translate>

<translate>

For example, given the following statement:</translate>

<source lang=php>

$result = $dbconnect->insertObject('#__my_table', $object, 'primary_key');

</source>

<translate>

after execution, $object->primary_key will be updated with the newly inserted row's primary key value.</translate>

<translate>

'''HINT''' Set $object->primary_key to null or 0 (zero) before inserting.</translate>

<translate>
====How to Store Empty Value as NULL When Inserting an Object==== 
</translate>
<translate>

If your default value of a column is NULL, you should not add that column name to the object. Let the database system store NULL as the default value.
If the default value of a column is not NULL and it is allowed to store NULL values, you should specify that in the code. See how to do it in the following example.
</translate>

<source lang="php">
// Create and populate an object.
$profile = new stdClass();
$profile->user_id = 1001;
$profile->profile_key='custom.message';
$profile->ordering=1;

/** First Case [NULL as default value] **/
// The column 'profile_value' has NULL as default value. So, we will not add it to the object. The database engine will store NULL as value for column 'profile_value'.
// $profile->profile_value='Inserting a record using insertObject()';

/** Second Case [string as default value and you can also store NULL value] **/
// The column 'profile_value' has empty string '' as default value but we can also store NULL value. So, we have to add the column name and NULL value as its value.
$profile->profile_value = $db->quote('NULL');

// Insert the object into the user profile table.
$result = JFactory::getDbo()->insertObject('#__user_profiles', $profile);
</source>

<translate>
==Updating a Record== 
</translate>
<translate>
===Using SQL=== 
</translate>
<translate>
The [https://api.joomla.org/cms-3/classes/JDatabaseQuery.html JDatabaseQuery] class also provides methods for building update queries, in particular [https://api.joomla.org/cms-3/classes/JDatabaseQuery.html#method_update update] and [https://api.joomla.org/cms-3/classes/JDatabaseQuery.html#method_set set]. We also reuse another method which we used when creating select statements, the ''where'' method.</translate>

<source lang="php">
$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',

// If you would like to store NULL value, you should specify that.
$db->quoteName('avatar') . ' = NULL',
);

// 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->execute();
</source>

<translate>
=== Using an Object === 
</translate>
<translate>
Like <tt>insertObject</tt>, the JDatabaseDriver class provides a convenience method for updating an object.</translate>

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

<source lang="php">
$updateNulls = true;

// 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.';

// If you would like to store NULL value, you should specify that.
$object->short_description = null;

// Update their details in the users table using id as the primary key.
// You should provide forth parameter with value TRUE, if you would like to store the NULL values.
$result = JFactory::getDbo()->updateObject('#__custom_table', $object, 'id', $updateNulls);
</source>

<translate>
Just like insertObject, updateObject takes care of escaping table names for us.</translate>

<translate>
The updateObject method will throw a error if there is a problem updating the record into the database table.</translate>

<translate>
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.</translate>

<translate>
==Deleting a Record== 
</translate>
<translate>
Finally, there is also a delete method to remove records from the database.</translate>

<source lang="php">
$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->execute();
</source>

<translate>
== Sample Module Code == </translate>
<translate>

Below is the code for a simple Joomla module which you can install and run to demonstrate use of the JDatabase functionality for updating records in the database and which you can adapt to experiment with some of the concepts described above. If you are unsure about development and installing a Joomla module then following the tutorial at [[S:MyLanguage/J3.x:Creating a simple module/Introduction| Creating a simple module]] will help.</translate>

<translate>

'''Important note: In any Joomla extensions which you develop that you should avoid accessing the core Joomla tables directly like this and should instead use the Joomla APIs if at all possible. The database structures may change without warning.'''</translate>

<translate>

In a folder mod_db_update create the following 2 files:</translate>

<tt>mod_db_update.xml</tt>
<source lang="xml">
<?xml version="1.0" encoding="utf-8"?>
<extension type="module" version="3.1" client="site" method="upgrade">
<name>Database update demo</name>
<version>1.0.1</version>
<description>Code demonstrating use of Joomla Database class to perform SQL UPDATE statements</description>
<files>
<filename module="mod_db_update">mod_db_update.php</filename>
</files>
</extension>
</source>

<tt>mod_db_update.php</tt>
<source lang="php">
<?php
defined('_JEXEC') or die('Restricted Access');

use Joomla\CMS\Factory;

$db = Factory::getDbo();

$me = Factory::getUser();

if ($me->id == 0)
{
echo "Not logged on!";
}
else
{
$email = $me->email;
// change the case of the email address
$email_uppercase = strtoupper($email);
if ($email == $email_uppercase)
{
$new_email = strtolower($email);
}
else
{
$new_email = $email_uppercase;
}

$query = $db->getQuery(true);

$fields = array($db->quoteName('email') . " = '{$new_email}'");

$conditions = array($db->quoteName('id') . ' = ' . $me->id);

$query->update($db->quoteName('#__users'))->set($fields)->where($conditions);

echo $db->replacePrefix((string) $query);

$db->setQuery($query);

if ($result = $db->execute())
{
echo "Email case successfully changed!";
}
}
</source>
<translate>

The code above updates the email address field in the <tt>users</tt> record of the currently logged-on user, toggling between upper case and lower case in successive reloads of the web page. </translate>
<translate>

The method <tt>Factory::getUser()</tt> returns the <tt>user</tt> object of the currently logged-on user, or if not logged on, then a blank <tt>user</tt> object, whose <tt>id</tt> field is set to zero. </translate>
<translate>

The <tt>$db->replacePrefix((string) $query)</tt> expression returns the actual SQL statement and outputting this can be useful in debugging. </translate>

<translate>

Zip up the ''mod_db_update'' directory to create <tt>mod_db_update.zip</tt>.</translate>

<translate>

Within your Joomla administrator go to Install Extensions and via the Upload Package File tab. Upload this zip file to install this sample log module.</translate>

<translate>

Make this module visible by editing it (click on it within the Modules page) then:
# Make its status ''Published''
# Select a position on the page for it to be shown
# Specify the pages it should appear on in the Menu Assignment tab</translate>

<translate>

When you visit a site web page then you should see the module in your selected position and it should output the SQL UPDATE statement and affirm that it has updated the record successfully. To confirm that it has updated the record correctly, go into PHPMyAdmin and view the <tt>users</tt> table within the Joomla database. The updates aren't visible via the admin Users functionality on the backend, as Joomla displays in lower case all the email addresses in the records. </translate>

<noinclude>
<translate>

[[Category:Database]]
</translate>
</noinclude>

Inserting, Updating and Removing data using JDatabase

2022-11-20T12:37:35Z

Blueline0:

<noinclude><languages /></noinclude>
<noinclude>{{Joomla version|version=3.x}}{{Joomla version|version=2.5|status=eos}}</noinclude>
{{-}}
{{tip|<translate>
Note many examples online use <code>$db->query()</code> instead of <code>$db->execute()</code>. This was the old method in Joomla 1.5 and 2.5 and will throw a deprecated notice in Joomla 3.0+.</translate>|title=<translate>
Version Note</translate>}}

<translate>
This tutorial is split into two independent parts:</translate>
<translate>
* Inserting, updating and removing data from the database.</translate>
<translate>
* Selecting data from one or more tables and retrieving it in a variety of forms.</translate>

<translate>
This section examines inserting, updating and removing data from a database table. See also the [[S:MyLanguage/Selecting_data_using_JDatabase|other part]].</translate>

<translate>
== Introduction== 
</translate>
<translate>
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.</translate>

<translate>
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.</translate>

<translate>
==The Query== 
</translate>
<translate>
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).</translate>

<translate>
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.</translate>

<translate>
To obtain a new instance of the JDatabaseQuery class we use the JDatabaseDriver getQuery method:</translate>

<source lang="PHP">
// Use one of the following depending on the file and version of Joomla
$db = JFactory::getDbo(); //deprecated J3, removed in J4
$db = Factory::getDbo(); //deprecated J4 - requires use Joomla\CMS\Factory;
$db = $this->getDbo(); // only works in the model or table classes, deprecated J4
$db = Factory::getContainer()->get('DatabaseDriver'); //J4 onwards, outside of the Model or Table
$db = $this->_db; //only works in the model or table classes, J4 onwards

$query = $db->getQuery(true);
</source>

<translate>
The <tt>JDatabaseDriver::getQuery</tt> takes an optional argument, ''$new'', which can be true or false (the default being false).</translate>

<translate>
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.</translate>

<translate>
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.</translate>

<translate>

==Inserting a Record== 
</translate>
<translate>
===Using SQL=== 
</translate>

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

<source lang="php">
// Get a db connection.
$db = JFactory::getDbo(); //deprecated J3, removed in J4
$db = Factory::getDbo(); //deprecated J4 - requires use Joomla\CMS\Factory;
$db = $this->getDbo(); // only works in the model or table classes, deprecated J4
$db = Factory::getContainer()->get('DatabaseDriver'); //J4 onwards, outside of the Model or Table
$db = $this->_db; //only works in the model or table classes, J4 onwards

// 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->execute();
</source>
<translate>

(Here the <tt>quoteName()</tt> function adds appropriate quotes around the table and column names to avoid conflicts with any database reserved word, now or in the future.)</translate>
<translate>

To get the ID of the row that you just inserted, you can use the ''insertid'' method, for example.</translate>
<source lang="php">
// Get the row that was just inserted
$new_row_id = $db->insertid();
</source>

<translate>
====How to Store Empty Value as NULL==== 
</translate>
<translate>

If your default value of a column is NULL, you should not add that column name in the array. Let the database system store NULL as the default value.
If the default value of a column is not NULL and it is allowed to store NULL values, you should specify that in the code. See how to do it in the following example.
</translate>

<source lang="php">
// Get a db connection.
$db = JFactory::getDbo();

// Create a new query object.
$query = $db->getQuery(true);

/** First Case [NULL as default value] **/
// The column 'profile_value' has NULL as default value. So, we will not add it to the array. The database engine will store NULL as value for column 'profile_value'.
// $columns = array('user_id', 'profile_key', 'profile_value', 'ordering');
$columns = array('user_id', 'profile_key', 'ordering');

// Insert values.
$values = array(1001, $db->quote('custom.message'), 1);

/** Second Case [string as default value and you can also store NULL value] **/
// The column 'profile_value' has empty string '' as default value but we can also store NULL value. So, we have to add the column name and NULL value to $columns and $values.
$columns = array('user_id', 'profile_key', 'profile_value', 'ordering');

// Insert values.
$values = array(1001, $db->quote('custom.message'), $db->quote('NULL'), 1);
</source>

<translate>

===Using an Object=== 
</translate>
<translate>
The <tt>JDatabaseDriver</tt> 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.</translate>

<source lang="php">
// 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);
</source>

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

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

<translate>
If you are providing a unique primary key value (as in the example above), it is highly recommended that you select from the table by that column value before attempting an insert.</translate>

<translate>

If you are simply inserting the next row in your table (i.e. the database generates a primary key value), you can specify the primary key column-name as the third parameter of the insertObject() method and the method will update the object with the newly generated primary key value.</translate>

<translate>

For example, given the following statement:</translate>

<source lang=php>

$result = $dbconnect->insertObject('#__my_table', $object, 'primary_key');

</source>

<translate>

after execution, $object->primary_key will be updated with the newly inserted row's primary key value.</translate>

<translate>

'''HINT''' Set $object->primary_key to null or 0 (zero) before inserting.</translate>

<translate>
====How to Store Empty Value as NULL When Inserting an Object==== 
</translate>
<translate>

If your default value of a column is NULL, you should not add that column name to the object. Let the database system store NULL as the default value.
If the default value of a column is not NULL and it is allowed to store NULL values, you should specify that in the code. See how to do it in the following example.
</translate>

<source lang="php">
// Create and populate an object.
$profile = new stdClass();
$profile->user_id = 1001;
$profile->profile_key='custom.message';
$profile->ordering=1;

/** First Case [NULL as default value] **/
// The column 'profile_value' has NULL as default value. So, we will not add it to the object. The database engine will store NULL as value for column 'profile_value'.
// $profile->profile_value='Inserting a record using insertObject()';

/** Second Case [string as default value and you can also store NULL value] **/
// The column 'profile_value' has empty string '' as default value but we can also store NULL value. So, we have to add the column name and NULL value as its value.
$profile->profile_value = $db->quote('NULL');

// Insert the object into the user profile table.
$result = JFactory::getDbo()->insertObject('#__user_profiles', $profile);
</source>

<translate>
==Updating a Record== 
</translate>
<translate>
===Using SQL=== 
</translate>
<translate>
The [https://api.joomla.org/cms-3/classes/JDatabaseQuery.html JDatabaseQuery] class also provides methods for building update queries, in particular [https://api.joomla.org/cms-3/classes/JDatabaseQuery.html#method_update update] and [https://api.joomla.org/cms-3/classes/JDatabaseQuery.html#method_set set]. We also reuse another method which we used when creating select statements, the ''where'' method.</translate>

<source lang="php">
$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',

// If you would like to store NULL value, you should specify that.
$db->quoteName('avatar') . ' = NULL',
);

// 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->execute();
</source>

<translate>
=== Using an Object === 
</translate>
<translate>
Like <tt>insertObject</tt>, the JDatabaseDriver class provides a convenience method for updating an object.</translate>

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

<source lang="php">
$updateNulls = true;

// 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.';

// If you would like to store NULL value, you should specify that.
$object->short_description = null;

// Update their details in the users table using id as the primary key.
// You should provide forth parameter with value TRUE, if you would like to store the NULL values.
$result = JFactory::getDbo()->updateObject('#__custom_table', $object, 'id', $updateNulls);
</source>

<translate>
Just like insertObject, updateObject takes care of escaping table names for us.</translate>

<translate>
The updateObject method will throw a error if there is a problem updating the record into the database table.</translate>

<translate>
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.</translate>

<translate>
==Deleting a Record== 
</translate>
<translate>
Finally, there is also a delete method to remove records from the database.</translate>

<source lang="php">
$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->execute();
</source>

<translate>
== Sample Module Code == </translate>
<translate>

Below is the code for a simple Joomla module which you can install and run to demonstrate use of the JDatabase functionality for updating records in the database and which you can adapt to experiment with some of the concepts described above. If you are unsure about development and installing a Joomla module then following the tutorial at [[S:MyLanguage/J3.x:Creating a simple module/Introduction| Creating a simple module]] will help.</translate>

<translate>

'''Important note: In any Joomla extensions which you develop that you should avoid accessing the core Joomla tables directly like this and should instead use the Joomla APIs if at all possible. The database structures may change without warning.'''</translate>

<translate>

In a folder mod_db_update create the following 2 files:</translate>

<tt>mod_db_update.xml</tt>
<source lang="xml">
<?xml version="1.0" encoding="utf-8"?>
<extension type="module" version="3.1" client="site" method="upgrade">
<name>Database update demo</name>
<version>1.0.1</version>
<description>Code demonstrating use of Joomla Database class to perform SQL UPDATE statements</description>
<files>
<filename module="mod_db_update">mod_db_update.php</filename>
</files>
</extension>
</source>

<tt>mod_db_update.php</tt>
<source lang="php">
<?php
defined('_JEXEC') or die('Restricted Access');

use Joomla\CMS\Factory;

$db = Factory::getDbo();

$me = Factory::getUser();

if ($me->id == 0)
{
echo "Not logged on!";
}
else
{
$email = $me->email;
// change the case of the email address
$email_uppercase = strtoupper($email);
if ($email == $email_uppercase)
{
$new_email = strtolower($email);
}
else
{
$new_email = $email_uppercase;
}

$query = $db->getQuery(true);

$fields = array($db->quoteName('email') . " = '{$new_email}'");

$conditions = array($db->quoteName('id') . ' = ' . $me->id);

$query->update($db->quoteName('#__users'))->set($fields)->where($conditions);

echo $db->replacePrefix((string) $query);

$db->setQuery($query);

if ($result = $db->execute())
{
echo "Email case successfully changed!";
}
}
</source>
<translate>

The code above updates the email address field in the <tt>users</tt> record of the currently logged-on user, toggling between upper case and lower case in successive reloads of the web page. </translate>
<translate>

The method <tt>Factory::getUser()</tt> returns the <tt>user</tt> object of the currently logged-on user, or if not logged on, then a blank <tt>user</tt> object, whose <tt>id</tt> field is set to zero. </translate>
<translate>

The <tt>$db->replacePrefix((string) $query)</tt> expression returns the actual SQL statement and outputting this can be useful in debugging. </translate>

<translate>

Zip up the ''mod_db_update'' directory to create <tt>mod_db_update.zip</tt>.</translate>

<translate>

Within your Joomla administrator go to Install Extensions and via the Upload Package File tab. Upload this zip file to install this sample log module.</translate>

<translate>

Make this module visible by editing it (click on it within the Modules page) then:
# Make its status ''Published''
# Select a position on the page for it to be shown
# Specify the pages it should appear on in the Menu Assignment tab</translate>

<translate>

When you visit a site web page then you should see the module in your selected position and it should output the SQL UPDATE statement and affirm that it has updated the record successfully. To confirm that it has updated the record correctly, go into PHPMyAdmin and view the <tt>users</tt> table within the Joomla database. The updates aren't visible via the admin Users functionality on the backend, as Joomla displays in lower case all the email addresses in the records. </translate>

<noinclude>
<translate>

[[Category:Database]]
</translate>
</noinclude>

Inserting, Updating and Removing data using JDatabase

2022-11-20T12:37:04Z

Blueline0:

<noinclude><languages /></noinclude>
<noinclude>{{Joomla version|version=3.x}}{{Joomla version|version=2.5|status=eos}}</noinclude>
{{-}}
{{tip|<translate>
Note many examples online use <code>$db->query()</code> instead of <code>$db->execute()</code>. This was the old method in Joomla 1.5 and 2.5 and will throw a deprecated notice in Joomla 3.0+.</translate>|title=<translate>
Version Note</translate>}}

<translate>
This tutorial is split into two independent parts:</translate>
<translate>
* Inserting, updating and removing data from the database.</translate>
<translate>
* Selecting data from one or more tables and retrieving it in a variety of forms.</translate>

<translate>
This section examines inserting, updating and removing data from a database table. See also the [[S:MyLanguage/Selecting_data_using_JDatabase|other part]].</translate>

<translate>
== Introduction== 
</translate>
<translate>
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.</translate>

<translate>
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.</translate>

<translate>
==The Query== 
</translate>
<translate>
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).</translate>

<translate>
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.</translate>

<translate>
To obtain a new instance of the JDatabaseQuery class we use the JDatabaseDriver getQuery method:</translate>

<source lang="php">
$db = JFactory::getDbo(); //deprecated J3, removed in J4
$db = Factory::getDbo(); //deprecated J4 - requires use Joomla\CMS\Factory;
$db = $this->getDbo(); // only works in the model or table classes, deprecated J4
$db = Factory::getContainer()->get('DatabaseDriver'); //J4 onwards, outside of the Model or Table
$db = $this->_db; //only works in the model or table classes, J4 onwards

$query = $db->getQuery(true);
</source>

<translate>
The <tt>JDatabaseDriver::getQuery</tt> takes an optional argument, ''$new'', which can be true or false (the default being false).</translate>

<translate>
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.</translate>

<translate>
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.</translate>

<translate>

==Inserting a Record== 
</translate>
<translate>
===Using SQL=== 
</translate>

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

<source lang="php">
// Get a db connection.
$db = JFactory::getDbo(); //deprecated J3, removed in J4
$db = Factory::getDbo(); //deprecated J4 - requires use Joomla\CMS\Factory;
$db = $this->getDbo(); // only works in the model or table classes, deprecated J4
$db = Factory::getContainer()->get('DatabaseDriver'); //J4 onwards, outside of the Model or Table
$db = $this->_db; //only works in the model or table classes, J4 onwards

// 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->execute();
</source>
<translate>

(Here the <tt>quoteName()</tt> function adds appropriate quotes around the table and column names to avoid conflicts with any database reserved word, now or in the future.)</translate>
<translate>

To get the ID of the row that you just inserted, you can use the ''insertid'' method, for example.</translate>
<source lang="php">
// Get the row that was just inserted
$new_row_id = $db->insertid();
</source>

<translate>
====How to Store Empty Value as NULL==== 
</translate>
<translate>

If your default value of a column is NULL, you should not add that column name in the array. Let the database system store NULL as the default value.
If the default value of a column is not NULL and it is allowed to store NULL values, you should specify that in the code. See how to do it in the following example.
</translate>

<source lang="php">
// Get a db connection.
$db = JFactory::getDbo();

// Create a new query object.
$query = $db->getQuery(true);

/** First Case [NULL as default value] **/
// The column 'profile_value' has NULL as default value. So, we will not add it to the array. The database engine will store NULL as value for column 'profile_value'.
// $columns = array('user_id', 'profile_key', 'profile_value', 'ordering');
$columns = array('user_id', 'profile_key', 'ordering');

// Insert values.
$values = array(1001, $db->quote('custom.message'), 1);

/** Second Case [string as default value and you can also store NULL value] **/
// The column 'profile_value' has empty string '' as default value but we can also store NULL value. So, we have to add the column name and NULL value to $columns and $values.
$columns = array('user_id', 'profile_key', 'profile_value', 'ordering');

// Insert values.
$values = array(1001, $db->quote('custom.message'), $db->quote('NULL'), 1);
</source>

<translate>

===Using an Object=== 
</translate>
<translate>
The <tt>JDatabaseDriver</tt> 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.</translate>

<source lang="php">
// 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);
</source>

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

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

<translate>
If you are providing a unique primary key value (as in the example above), it is highly recommended that you select from the table by that column value before attempting an insert.</translate>

<translate>

If you are simply inserting the next row in your table (i.e. the database generates a primary key value), you can specify the primary key column-name as the third parameter of the insertObject() method and the method will update the object with the newly generated primary key value.</translate>

<translate>

For example, given the following statement:</translate>

<source lang=php>

$result = $dbconnect->insertObject('#__my_table', $object, 'primary_key');

</source>

<translate>

after execution, $object->primary_key will be updated with the newly inserted row's primary key value.</translate>

<translate>

'''HINT''' Set $object->primary_key to null or 0 (zero) before inserting.</translate>

<translate>
====How to Store Empty Value as NULL When Inserting an Object==== 
</translate>
<translate>

If your default value of a column is NULL, you should not add that column name to the object. Let the database system store NULL as the default value.
If the default value of a column is not NULL and it is allowed to store NULL values, you should specify that in the code. See how to do it in the following example.
</translate>

<source lang="php">
// Create and populate an object.
$profile = new stdClass();
$profile->user_id = 1001;
$profile->profile_key='custom.message';
$profile->ordering=1;

/** First Case [NULL as default value] **/
// The column 'profile_value' has NULL as default value. So, we will not add it to the object. The database engine will store NULL as value for column 'profile_value'.
// $profile->profile_value='Inserting a record using insertObject()';

/** Second Case [string as default value and you can also store NULL value] **/
// The column 'profile_value' has empty string '' as default value but we can also store NULL value. So, we have to add the column name and NULL value as its value.
$profile->profile_value = $db->quote('NULL');

// Insert the object into the user profile table.
$result = JFactory::getDbo()->insertObject('#__user_profiles', $profile);
</source>

<translate>
==Updating a Record== 
</translate>
<translate>
===Using SQL=== 
</translate>
<translate>
The [https://api.joomla.org/cms-3/classes/JDatabaseQuery.html JDatabaseQuery] class also provides methods for building update queries, in particular [https://api.joomla.org/cms-3/classes/JDatabaseQuery.html#method_update update] and [https://api.joomla.org/cms-3/classes/JDatabaseQuery.html#method_set set]. We also reuse another method which we used when creating select statements, the ''where'' method.</translate>

<source lang="php">
$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',

// If you would like to store NULL value, you should specify that.
$db->quoteName('avatar') . ' = NULL',
);

// 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->execute();
</source>

<translate>
=== Using an Object === 
</translate>
<translate>
Like <tt>insertObject</tt>, the JDatabaseDriver class provides a convenience method for updating an object.</translate>

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

<source lang="php">
$updateNulls = true;

// 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.';

// If you would like to store NULL value, you should specify that.
$object->short_description = null;

// Update their details in the users table using id as the primary key.
// You should provide forth parameter with value TRUE, if you would like to store the NULL values.
$result = JFactory::getDbo()->updateObject('#__custom_table', $object, 'id', $updateNulls);
</source>

<translate>
Just like insertObject, updateObject takes care of escaping table names for us.</translate>

<translate>
The updateObject method will throw a error if there is a problem updating the record into the database table.</translate>

<translate>
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.</translate>

<translate>
==Deleting a Record== 
</translate>
<translate>
Finally, there is also a delete method to remove records from the database.</translate>

<source lang="php">
$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->execute();
</source>

<translate>
== Sample Module Code == </translate>
<translate>

Below is the code for a simple Joomla module which you can install and run to demonstrate use of the JDatabase functionality for updating records in the database and which you can adapt to experiment with some of the concepts described above. If you are unsure about development and installing a Joomla module then following the tutorial at [[S:MyLanguage/J3.x:Creating a simple module/Introduction| Creating a simple module]] will help.</translate>

<translate>

'''Important note: In any Joomla extensions which you develop that you should avoid accessing the core Joomla tables directly like this and should instead use the Joomla APIs if at all possible. The database structures may change without warning.'''</translate>

<translate>

In a folder mod_db_update create the following 2 files:</translate>

<tt>mod_db_update.xml</tt>
<source lang="xml">
<?xml version="1.0" encoding="utf-8"?>
<extension type="module" version="3.1" client="site" method="upgrade">
<name>Database update demo</name>
<version>1.0.1</version>
<description>Code demonstrating use of Joomla Database class to perform SQL UPDATE statements</description>
<files>
<filename module="mod_db_update">mod_db_update.php</filename>
</files>
</extension>
</source>

<tt>mod_db_update.php</tt>
<source lang="php">
<?php
defined('_JEXEC') or die('Restricted Access');

use Joomla\CMS\Factory;

$db = Factory::getDbo();

$me = Factory::getUser();

if ($me->id == 0)
{
echo "Not logged on!";
}
else
{
$email = $me->email;
// change the case of the email address
$email_uppercase = strtoupper($email);
if ($email == $email_uppercase)
{
$new_email = strtolower($email);
}
else
{
$new_email = $email_uppercase;
}

$query = $db->getQuery(true);

$fields = array($db->quoteName('email') . " = '{$new_email}'");

$conditions = array($db->quoteName('id') . ' = ' . $me->id);

$query->update($db->quoteName('#__users'))->set($fields)->where($conditions);

echo $db->replacePrefix((string) $query);

$db->setQuery($query);

if ($result = $db->execute())
{
echo "Email case successfully changed!";
}
}
</source>
<translate>

The code above updates the email address field in the <tt>users</tt> record of the currently logged-on user, toggling between upper case and lower case in successive reloads of the web page. </translate>
<translate>

The method <tt>Factory::getUser()</tt> returns the <tt>user</tt> object of the currently logged-on user, or if not logged on, then a blank <tt>user</tt> object, whose <tt>id</tt> field is set to zero. </translate>
<translate>

The <tt>$db->replacePrefix((string) $query)</tt> expression returns the actual SQL statement and outputting this can be useful in debugging. </translate>

<translate>

Zip up the ''mod_db_update'' directory to create <tt>mod_db_update.zip</tt>.</translate>

<translate>

Within your Joomla administrator go to Install Extensions and via the Upload Package File tab. Upload this zip file to install this sample log module.</translate>

<translate>

Make this module visible by editing it (click on it within the Modules page) then:
# Make its status ''Published''
# Select a position on the page for it to be shown
# Specify the pages it should appear on in the Menu Assignment tab</translate>

<translate>

When you visit a site web page then you should see the module in your selected position and it should output the SQL UPDATE statement and affirm that it has updated the record successfully. To confirm that it has updated the record correctly, go into PHPMyAdmin and view the <tt>users</tt> table within the Joomla database. The updates aren't visible via the admin Users functionality on the backend, as Joomla displays in lower case all the email addresses in the records. </translate>

<noinclude>
<translate>

[[Category:Database]]
</translate>
</noinclude>

Inserting, Updating and Removing data using JDatabase

2022-11-20T12:36:25Z

Blueline0: Added some notes on the J4 $db

<noinclude><languages /></noinclude>
<noinclude>{{Joomla version|version=3.x}}{{Joomla version|version=2.5|status=eos}}</noinclude>
{{-}}
{{tip|<translate>
Note many examples online use <code>$db->query()</code> instead of <code>$db->execute()</code>. This was the old method in Joomla 1.5 and 2.5 and will throw a deprecated notice in Joomla 3.0+.</translate>|title=<translate>
Version Note</translate>}}

<translate>
This tutorial is split into two independent parts:</translate>
<translate>
* Inserting, updating and removing data from the database.</translate>
<translate>
* Selecting data from one or more tables and retrieving it in a variety of forms.</translate>

<translate>
This section examines inserting, updating and removing data from a database table. See also the [[S:MyLanguage/Selecting_data_using_JDatabase|other part]].</translate>

<translate>
== Introduction== 
</translate>
<translate>
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.</translate>

<translate>
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.</translate>

<translate>
==The Query== 
</translate>
<translate>
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).</translate>

<translate>
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.</translate>

<translate>
To obtain a new instance of the JDatabaseQuery class we use the JDatabaseDriver getQuery method:</translate>

<source lang="php">
$db = JFactory::getDbo();

$query = $db->getQuery(true);
</source>

<translate>
The <tt>JDatabaseDriver::getQuery</tt> takes an optional argument, ''$new'', which can be true or false (the default being false).</translate>

<translate>
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.</translate>

<translate>
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.</translate>

<translate>
==Inserting a Record== 
</translate>
<translate>
===Using SQL=== 
</translate>

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

<source lang="php">
// Get a db connection.
$db = JFactory::getDbo(); //deprecated J3, removed in J4
$db = Factory::getDbo(); //deprecated J4 - requires use Joomla\CMS\Factory;
$db = $this->getDbo(); // only works in the model or table classes, deprecated J4
$db = Factory::getContainer()->get('DatabaseDriver'); //J4 onwards, outside of the Model or Table
$db = $this->_db; //only works in the model or table classes, J4 onwards

// 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->execute();
</source>
<translate>

(Here the <tt>quoteName()</tt> function adds appropriate quotes around the table and column names to avoid conflicts with any database reserved word, now or in the future.)</translate>
<translate>

To get the ID of the row that you just inserted, you can use the ''insertid'' method, for example.</translate>
<source lang="php">
// Get the row that was just inserted
$new_row_id = $db->insertid();
</source>

<translate>
====How to Store Empty Value as NULL==== 
</translate>
<translate>

If your default value of a column is NULL, you should not add that column name in the array. Let the database system store NULL as the default value.
If the default value of a column is not NULL and it is allowed to store NULL values, you should specify that in the code. See how to do it in the following example.
</translate>

<source lang="php">
// Get a db connection.
$db = JFactory::getDbo();

// Create a new query object.
$query = $db->getQuery(true);

/** First Case [NULL as default value] **/
// The column 'profile_value' has NULL as default value. So, we will not add it to the array. The database engine will store NULL as value for column 'profile_value'.
// $columns = array('user_id', 'profile_key', 'profile_value', 'ordering');
$columns = array('user_id', 'profile_key', 'ordering');

// Insert values.
$values = array(1001, $db->quote('custom.message'), 1);

/** Second Case [string as default value and you can also store NULL value] **/
// The column 'profile_value' has empty string '' as default value but we can also store NULL value. So, we have to add the column name and NULL value to $columns and $values.
$columns = array('user_id', 'profile_key', 'profile_value', 'ordering');

// Insert values.
$values = array(1001, $db->quote('custom.message'), $db->quote('NULL'), 1);
</source>

<translate>

===Using an Object=== 
</translate>
<translate>
The <tt>JDatabaseDriver</tt> 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.</translate>

<source lang="php">
// 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);
</source>

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

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

<translate>
If you are providing a unique primary key value (as in the example above), it is highly recommended that you select from the table by that column value before attempting an insert.</translate>

<translate>

If you are simply inserting the next row in your table (i.e. the database generates a primary key value), you can specify the primary key column-name as the third parameter of the insertObject() method and the method will update the object with the newly generated primary key value.</translate>

<translate>

For example, given the following statement:</translate>

<source lang=php>

$result = $dbconnect->insertObject('#__my_table', $object, 'primary_key');

</source>

<translate>

after execution, $object->primary_key will be updated with the newly inserted row's primary key value.</translate>

<translate>

'''HINT''' Set $object->primary_key to null or 0 (zero) before inserting.</translate>

<translate>
====How to Store Empty Value as NULL When Inserting an Object==== 
</translate>
<translate>

If your default value of a column is NULL, you should not add that column name to the object. Let the database system store NULL as the default value.
If the default value of a column is not NULL and it is allowed to store NULL values, you should specify that in the code. See how to do it in the following example.
</translate>

<source lang="php">
// Create and populate an object.
$profile = new stdClass();
$profile->user_id = 1001;
$profile->profile_key='custom.message';
$profile->ordering=1;

/** First Case [NULL as default value] **/
// The column 'profile_value' has NULL as default value. So, we will not add it to the object. The database engine will store NULL as value for column 'profile_value'.
// $profile->profile_value='Inserting a record using insertObject()';

/** Second Case [string as default value and you can also store NULL value] **/
// The column 'profile_value' has empty string '' as default value but we can also store NULL value. So, we have to add the column name and NULL value as its value.
$profile->profile_value = $db->quote('NULL');

// Insert the object into the user profile table.
$result = JFactory::getDbo()->insertObject('#__user_profiles', $profile);
</source>

<translate>
==Updating a Record== 
</translate>
<translate>
===Using SQL=== 
</translate>
<translate>
The [https://api.joomla.org/cms-3/classes/JDatabaseQuery.html JDatabaseQuery] class also provides methods for building update queries, in particular [https://api.joomla.org/cms-3/classes/JDatabaseQuery.html#method_update update] and [https://api.joomla.org/cms-3/classes/JDatabaseQuery.html#method_set set]. We also reuse another method which we used when creating select statements, the ''where'' method.</translate>

<source lang="php">
$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',

// If you would like to store NULL value, you should specify that.
$db->quoteName('avatar') . ' = NULL',
);

// 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->execute();
</source>

<translate>
=== Using an Object === 
</translate>
<translate>
Like <tt>insertObject</tt>, the JDatabaseDriver class provides a convenience method for updating an object.</translate>

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

<source lang="php">
$updateNulls = true;

// 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.';

// If you would like to store NULL value, you should specify that.
$object->short_description = null;

// Update their details in the users table using id as the primary key.
// You should provide forth parameter with value TRUE, if you would like to store the NULL values.
$result = JFactory::getDbo()->updateObject('#__custom_table', $object, 'id', $updateNulls);
</source>

<translate>
Just like insertObject, updateObject takes care of escaping table names for us.</translate>

<translate>
The updateObject method will throw a error if there is a problem updating the record into the database table.</translate>

<translate>
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.</translate>

<translate>
==Deleting a Record== 
</translate>
<translate>
Finally, there is also a delete method to remove records from the database.</translate>

<source lang="php">
$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->execute();
</source>

<translate>
== Sample Module Code == </translate>
<translate>

Below is the code for a simple Joomla module which you can install and run to demonstrate use of the JDatabase functionality for updating records in the database and which you can adapt to experiment with some of the concepts described above. If you are unsure about development and installing a Joomla module then following the tutorial at [[S:MyLanguage/J3.x:Creating a simple module/Introduction| Creating a simple module]] will help.</translate>

<translate>

'''Important note: In any Joomla extensions which you develop that you should avoid accessing the core Joomla tables directly like this and should instead use the Joomla APIs if at all possible. The database structures may change without warning.'''</translate>

<translate>

In a folder mod_db_update create the following 2 files:</translate>

<tt>mod_db_update.xml</tt>
<source lang="xml">
<?xml version="1.0" encoding="utf-8"?>
<extension type="module" version="3.1" client="site" method="upgrade">
<name>Database update demo</name>
<version>1.0.1</version>
<description>Code demonstrating use of Joomla Database class to perform SQL UPDATE statements</description>
<files>
<filename module="mod_db_update">mod_db_update.php</filename>
</files>
</extension>
</source>

<tt>mod_db_update.php</tt>
<source lang="php">
<?php
defined('_JEXEC') or die('Restricted Access');

use Joomla\CMS\Factory;

$db = Factory::getDbo();

$me = Factory::getUser();

if ($me->id == 0)
{
echo "Not logged on!";
}
else
{
$email = $me->email;
// change the case of the email address
$email_uppercase = strtoupper($email);
if ($email == $email_uppercase)
{
$new_email = strtolower($email);
}
else
{
$new_email = $email_uppercase;
}

$query = $db->getQuery(true);

$fields = array($db->quoteName('email') . " = '{$new_email}'");

$conditions = array($db->quoteName('id') . ' = ' . $me->id);

$query->update($db->quoteName('#__users'))->set($fields)->where($conditions);

echo $db->replacePrefix((string) $query);

$db->setQuery($query);

if ($result = $db->execute())
{
echo "Email case successfully changed!";
}
}
</source>
<translate>

The code above updates the email address field in the <tt>users</tt> record of the currently logged-on user, toggling between upper case and lower case in successive reloads of the web page. </translate>
<translate>

The method <tt>Factory::getUser()</tt> returns the <tt>user</tt> object of the currently logged-on user, or if not logged on, then a blank <tt>user</tt> object, whose <tt>id</tt> field is set to zero. </translate>
<translate>

The <tt>$db->replacePrefix((string) $query)</tt> expression returns the actual SQL statement and outputting this can be useful in debugging. </translate>

<translate>

Zip up the ''mod_db_update'' directory to create <tt>mod_db_update.zip</tt>.</translate>

<translate>

Within your Joomla administrator go to Install Extensions and via the Upload Package File tab. Upload this zip file to install this sample log module.</translate>

<translate>

Make this module visible by editing it (click on it within the Modules page) then:
# Make its status ''Published''
# Select a position on the page for it to be shown
# Specify the pages it should appear on in the Menu Assignment tab</translate>

<translate>

When you visit a site web page then you should see the module in your selected position and it should output the SQL UPDATE statement and affirm that it has updated the record successfully. To confirm that it has updated the record correctly, go into PHPMyAdmin and view the <tt>users</tt> table within the Joomla database. The updates aren't visible via the admin Users functionality on the backend, as Joomla displays in lower case all the email addresses in the records. </translate>

<noinclude>
<translate>

[[Category:Database]]
</translate>
</noinclude>

How to add CSRF anti-spoofing to forms

2022-10-07T21:33:10Z

Blueline0:

{{version|2.5,3.1,4.0}}
== What is a CSRF Attack? ==
A Cross Site Request Forgery ([[Wikipedia:Cross-site_request_forgery|CSRF]]) attack relies on the trust a website has for a user to execute unauthorized requests and or transactions. For example, say a user is logged into their Joomla! websites' administrator interface in one tab and is browsing a compromised site in another tab. A simple CSRF attack can be launched simply by tampering with IMG elements in some browsers so that they point to something like
<nowiki>http://some/joomla/site/administrator/index2.php?option=com_users&task=delete...</nowiki>
When the user browses the compromised site, that image will be requested and because the user is currently logged in to the administrator interface of her Joomla! site, the forged request will be positively authenticated and executed. To prevent simple CSRF attacks like the one above, request tokens have been added to all forms in the front-end and back-end Joomla! interfaces. The tokens are randomized strings that are used to authenticate that the request being made is coming from a valid form and a valid session. This simple measure is very effective at preventing a large percentage of potential CSRF attacks, however, due to the nature of CSRF they are extremely difficult, if not impossible, to secure against completely.

== Protecting Against CSRF Attacks ==

Joomla! attempts to protect againt CSRF by inserting a random string called a token into each POST form and each GET query string that is able to modify something in the Joomla! system. This random string provides protection because not only does the compromised site need to know the URL of the target site and a valid request format for the target site, it also must know the random string which changes for each session and each user.

The {{jf}} makes it easy for you to include such protection in your components as well. This is simple to implement in both POST and GET requests.

=== POST Request ===

POST requests are submitted in HTML using forms. In order to add the token to your form, add the following line inside your form:
<?php echo JHtml::_( 'form.token' ); ?>
Joomla 4
use Joomla\CMS\HTML\HTMLHelper;
<?php echo HTMLHelper::_('form.token'); ?>

This will output something like the following:
<input type="hidden" name="1234567890abcdef1234567890abcdef" value="1" />

=== GET Request ===

GET requests are submitted in HTML using query strings. In order to add the token to your query string, use a URL like:
<?php
echo JRoute::_( 'index.php?option=com_example&controller=object1&task=save&'. JSession::getFormToken() .'=1' );
?>
Joomla 4
use Joomla\CMS\Router\Route;
use Joomla\CMS\Session\Session;
<?php
echo Route::_( 'index.php?option=com_example&controller=object1&task=save&'. Session::getFormToken() .'=1' );
?>

This will generate a URL with the token in the query string.

=== Using JavaScript ===

var csrf = Joomla.getOptions("csrf.token", "");

=== Checking the Token ===

Once you have included the token in your form or in your query string, you must check the token '''before your script carries out the request'''. This is done with the following line:
JSession::checkToken() or die(Text::_('JINVALID_TOKEN_NOTICE'));
Joomla 4
$this->checkToken();
OR
use Joomla\CMS\Session\Session;
if(!Session::checkToken()) {
throw new \Exception(Text::_('JINVALID_TOKEN_NOTICE'), 403);
}

If the request is coming from the query string, you must specify this. The code then becomes:
JSession::checkToken( 'get' ) or die( 'Invalid Token' );
Joomla 4
$this->checkToken();
OR
use Joomla\CMS\Session\Session;
if(!Session::checkToken('get')) {
throw new \Exception(Text::_('JINVALID_TOKEN_NOTICE'), 403);
}

== Recommended Security Procedures ==

While these methods help to prevent against these types of attacks, it is important to realize that as a system administrator, there are good security practices to follow which will prevent a site from being compromised.

# Don't browse other sites in the same browser while you are logged into your site.
# Log out from your site after you are done.
# Don't stay logged into your site while you are not doing anything.
# Ensure that the address in the browser bar matches the address of your site.

By practicing these safe surfing habits you will eliminate most threats to your web site.

<noinclude>[[Category:FAQ]] [[Category:Security_FAQ]] [[Category:Security]] [[Category:Development]]
[[Category:Form fields]]
</noinclude>

How to add CSRF anti-spoofing to forms

2022-10-07T21:30:46Z

Blueline0: /* Checking the Token */

{{version|2.5,3.1,4.0}}
== What is a CSRF Attack? ==
A Cross Site Request Forgery ([[Wikipedia:Cross-site_request_forgery|CSRF]]) attack relies on the trust a website has for a user to execute unauthorized requests and or transactions. For example, say a user is logged into their Joomla! websites' administrator interface in one tab and is browsing a compromised site in another tab. A simple CSRF attack can be launched simply by tampering with IMG elements in some browsers so that they point to something like
<nowiki>http://some/joomla/site/administrator/index2.php?option=com_users&task=delete...</nowiki>
When the user browses the compromised site, that image will be requested and because the user is currently logged in to the administrator interface of her Joomla! site, the forged request will be positively authenticated and executed. To prevent simple CSRF attacks like the one above, request tokens have been added to all forms in the front-end and back-end Joomla! interfaces. The tokens are randomized strings that are used to authenticate that the request being made is coming from a valid form and a valid session. This simple measure is very effective at preventing a large percentage of potential CSRF attacks, however, due to the nature of CSRF they are extremely difficult, if not impossible, to secure against completely.

== Protecting Against CSRF Attacks ==

Joomla! attempts to protect againt CSRF by inserting a random string called a token into each POST form and each GET query string that is able to modify something in the Joomla! system. This random string provides protection because not only does the compromised site need to know the URL of the target site and a valid request format for the target site, it also must know the random string which changes for each session and each user.

The {{jf}} makes it easy for you to include such protection in your components as well. This is simple to implement in both POST and GET requests.

=== POST Request ===

POST requests are submitted in HTML using forms. In order to add the token to your form, add the following line inside your form:
<?php echo JHtml::_( 'form.token' ); ?>
Joomla 4
use Joomla\CMS\HTML\HTMLHelper;
<?php echo HTMLHelper::_('form.token'); ?>

This will output something like the following:
<input type="hidden" name="1234567890abcdef1234567890abcdef" value="1" />

=== GET Request ===

GET requests are submitted in HTML using query strings. In order to add the token to your query string, use a URL like:
<?php
echo JRoute::_( 'index.php?option=com_example&controller=object1&task=save&'. JSession::getFormToken() .'=1' );
?>
Joomla 4
use Joomla\CMS\Router\Route;
use Joomla\CMS\Session\Session;
<?php
echo Route::_( 'index.php?option=com_example&controller=object1&task=save&'. Session::getFormToken() .'=1' );
?>

This will generate a URL with the token in the query string.

=== Checking the Token ===

Once you have included the token in your form or in your query string, you must check the token '''before your script carries out the request'''. This is done with the following line:
JSession::checkToken() or die(Text::_('JINVALID_TOKEN_NOTICE'));
Joomla 4
$this->checkToken();
OR
use Joomla\CMS\Session\Session;
if(!Session::checkToken()) {
throw new \Exception(Text::_('JINVALID_TOKEN_NOTICE'), 403);
}

If the request is coming from the query string, you must specify this. The code then becomes:
JSession::checkToken( 'get' ) or die( 'Invalid Token' );
Joomla 4
$this->checkToken();
OR
use Joomla\CMS\Session\Session;
if(!Session::checkToken('get')) {
throw new \Exception(Text::_('JINVALID_TOKEN_NOTICE'), 403);
}

== Recommended Security Procedures ==

While these methods help to prevent against these types of attacks, it is important to realize that as a system administrator, there are good security practices to follow which will prevent a site from being compromised.

# Don't browse other sites in the same browser while you are logged into your site.
# Log out from your site after you are done.
# Don't stay logged into your site while you are not doing anything.
# Ensure that the address in the browser bar matches the address of your site.

By practicing these safe surfing habits you will eliminate most threats to your web site.

<noinclude>[[Category:FAQ]] [[Category:Security_FAQ]] [[Category:Security]] [[Category:Development]]
[[Category:Form fields]]
</noinclude>