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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | $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
1 2 3 | $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
1 2 3 | $data=$db->loadResultArray(); |
returns an array of single field results into an array.
3. loadAssoc
1 2 3 | $data=$db->loadAssoc(); |
returns an associative array of the first row.
4. loadAssocList
1 2 3 | $data=$db->loadAssocList(); |
returns an array of the result set rows in an associative array
5. loadRow
1 2 3 | $data=$db->loadRow(); |
returns the first row returned by the query in a numeric array.
6. loadRowList
1 2 3 | $data=$db->loadRowList(); |
returns a list of database rows in a numeric array.
7. loadObject
1 2 3 | $data=$db->loadObject(); |
returns the first row of a query into an object.
8. loadObjectList
1 2 3 | $data=$db->loadObjectList(); |
returns a list of database objects.
9. loadNextObject
1 2 3 | $data=$db->loadNextObject(); |
returns the next row returned by the query.
8. loadNextRow
1 2 3 | $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.
1 2 3 4 5 6 7 | $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:
1 2 3 | $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.
1 2 3 4 5 6 7 8 | $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.
1 2 3 4 5 6 7 | $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.
1 2 3 4 5 6 | $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)