Back to Top

How to use Custom Query in Joomla?

Custom Query with Joomla

Joomla is an open source content Management System.Sometimes, we are working on some customization in an existing functionality and database.so for that it is necessary to write custom queries into Joomla.

Joomla provides JDatabase class to execute joomla database. joomla 2.5 has introduced the JDatabaseQuery class concept.

Let’s understand the custom queries.Here I am going to start with select query

SELECT a records

$db = JFactory::getDBO();
$query->select('column1,column2,...,columnn');
$query->from('#__tablename');
$db->setQuery((string)$query);
$datas = $db->loadObjectList();
if (isset($datas)){
	foreach($datas as $data)
	{
	//$data->column1
	//$data->column2;
	}
}

Here,getDBO is for the connection to the global database which creates global database object if not exists.setQuery the method sets the SQL query for later execution.For listing fields, I have used loadObjectList method which returns the list of fields into Object form but Joomla has various methods to fetch records and it’s depending on what format of data you want. so Let’s take a look into it.

1. loadResult

$data=$db->loadResult();

returns the first column from a row or a single specified column similar to the MySQL function “mysql_fetch_row()” if you specify a single column.

2. loadResultArray

$data=$db->loadResultArray();

returns an array of single field results into an array.

3. loadAssoc

$data=$db->loadAssoc();

returns an associative array of the first row.

4. loadAssocList

$data=$db->loadAssocList();

returns an array of the result set rows in an associative array

5. loadRow

$data=$db->loadRow();

returns the first row returned by the query in a numeric array.

6. loadRowList

$data=$db->loadRowList();

returns a list of database rows in a numeric array.

7. loadObject

$data=$db->loadObject();

returns the first row of a query into an object.

8. loadObjectList

$data=$db->loadObjectList();

returns a list of database objects.

9. loadNextObject

$data=$db->loadNextObject();

returns the next row returned by the query.

8. loadNextRow

$data=$db->loadNextRow();

returns the next row returned by the query.

INSERT a record

After fetching rows from the database, let’s see how to insert records into the database.

$db = JFactory::getDBO();
$query->insert('#__tablename');
$query->set("column1='value1', columnn='valuen'");
$db->setQuery($query);
$db->query();

To get the last inserted id,you can use following code:

$db->insertid();

UPDATE a record

Joomla provides methods for building update queries.update method generates exception if a record does not exist or fail to update.

$db = JFactory::getDBO();
$query->update('#__tablename');
$query->set("column1='value1', columnn='valuen'");
$query->where(array('column1'=>'value1'));
$db->setQuery($query);
$db->query();

DELETE a record

As like Joomla have selected, insert and update method calls, there is also a delete method for deleting records from the database.

$db = JFactory::getDBO();
$query->delete('#__tablename');
$query->where(array('column1'=>'value1'));
$db->setQuery($query);
$db->query();

COMMON query

This is for executing any query, even for join and other complex query, you can manage simply using below line of code.

$db = JFactory::getDBO();
$query = "[YOUR QUERY]";
$db->setQuery($query);
$db->query();

As always thank you for reading and feel free to share your thoughts! Don’t Forget to Follow us on Twitter or Subscribe us to Get the Latest Updates.

Comments (2)

  1. Hi, I would be curious whether SQL injection is an issue here or not?

    1. JDatabaseQuery class automatically filters the input so query is sql injection safe.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Most Popular Posts

AWS Cloud Database Migration Service

Posted on 2 years ago

Bhumi

How to get Current URL in WordPress?

Posted on 5 years ago

Bhumi

Hello world

Updated 1 week ago

Nick Carter

In depth Understanding of Laravel Core

Updated 2 months ago

Bhumi

ECMAScript 6 New Features – 2

Updated 1 year ago

Bhumi

The Reader’s Poll – June 2015

Posted on 4 years ago

Bhumi

The Reader’s Poll – August 2014

Posted on 4 years ago

Bhumi

The Reader’s Poll – June 2014

Posted on 5 years ago

Bhumi

The Readers’ Poll – May 2014

Posted on 5 years ago

Bhumi

The Readers’ Poll – August 2013

Posted on 5 years ago

Bhumi