This post is for Joomla beginners, who start’s learning in Joomla development. These four queries are very common and used every time in the module, plugin and component development for retrieving, inserting, updating and deleting operation. In this post, I am explaining all these queries with #__users table.

Few things need to understand before using it:

  • getDBO: Returns a reference to the global database object, only creating it if it doesn’t already exist.
  • setQuery: Sets the SQL query string for later execution.
  • loadObjectList: Returns an indexed array of PHP objects from the table records returned by the query.

Select Query:

// Get a database object

$db = JFactory::getDBO();

$query = "SELECT * FROM #__users";
$db->setQuery($query);
$result = $db->loadObjectList();

foreach($result as $data){
    echo $data->username;
}

 

Insert Query:

// Get a database object

$db = JFactory::getDBO();

$name = mysql_real_escape_string($_POST['name']);
$username = mysql_real_escape_string($_POST['username']);

$query = $db->getQuery(true);
$query->insert('#__users');
$query->set("name='".$name."', username='".$username."'");
$db->setQuery($query);
$db->query();

 

Update Query:

// Get a database object

$db = JFactory::getDbo();

$id = "10";

$name = mysql_real_escape_string($_POST['name']);
$username = mysql_real_escape_string($_POST['username']);

$query = $db->getQuery(true);
$query->update('#__users');
$query->set("name='".$name."', username='".$username."'");
$query->where('id = ' . $id);
$db->setQuery($query);
$db->query();

 

Delete Query:

// Get a database object

$db = JFactory::getDBO();

$id = "10";

$query = $db->getQuery(true);
$query->delete($db->nameQuote('#__users'));
$query->where($db->nameQuote('id').'='.$db->quote($id));
$db->setQuery($query);
$db->query();

I hope you like the post and it helps you, so feel free and give your comment 🙂


Leave a Reply

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