Joomla: Executing Sql Queries with JDatabase

1) Getting the default database connection:


$db =& JFactory::getDBO();

2) Set the query. Can pass in start and length LIMIT values to shrink the resultset. This will add LIMIT 0,3 to the end.


$db->setQuery($query,0,3);

3) Execute the query. You can call various methods depending on what amount of data is being returned

$db->loadObject() – returns first row as an object. e.g. $obj->field.
$db->loadObjectList() – returns resultset as an object. For multiple records.
$db->loadResult() – returns first field of first row as a value. A single value
$db->loadRow() – returns the first row as an indexed array.
$db->loadAssoc() - returns first row as an associated array.
$db->loadAssocList() - returns resultset as an associated array. For multiple records.
$db->Execute($sql) - Execute sql that doesn't return anything. Pass sql as parameter.

Looping through a resultset:


$db->setQuery($query,0,3);
$rows = $db->loadObjectList();
foreach($rows as $row)
{
	echo $row->fldname;
}


This will create a resultset object with specified field as key index value.


$db->setQuery("SELECT category_id, name FROM category");
$categories = $db->loadObjectList('category_id');

Leave a comment

7 Comments.

  1. Can you help me with the syntax to input this or similar in a form field?

    $sql = “SELECT `title` FROM `jos_usergroups` WHERE 1 LIMIT 0, 30 “;

    I am using j2.5.4

    • $db =& JFactory::getDBO();
      $query = ‘SELECT `title` FROM `jos_usergroups` WHERE 1′;
      $db->setQuery($query,0,30);
      $results = $db->loadObjectList();

  2. Greetings from Ohio! I’m bored too tears at work
    so I decided to browse your site on my iphone during lunch break.
    I reslly like the knowledge you provide here and can’t wait to take a look wen I get home.
    I’m shocked at how quick your blog loladed on my mobile ..
    I’m not even uwing WIFI, just 3G .. Anyways, amazkng site!

  3. I am going to instantly knowledge your own rss as I are unable to to uncover your current e-mail request website link or even e-zine assistance. Are there virtually any? Be sure to allow for myself have an understanding of in order that I may sign up to. Cheers.. Κατασκευή Ιστοσελίδων, Κατασκευή Site, Με τις μεγαλύτερες πλατφόρμες CMS παγκοσμίως – WORDPRESS – JOOMLA – OPEN CART – PRESTA SHOP – MAGENTO

  4. Excellent points permanently, you simply got a completely new target audience. What would anyone recommend about your article you created a 7 days in the past? Every specified?

  5. I am now not certain the place you’re getting your
    information, however great topic. I needs to spend a while studying much more or figuring out more.
    Thank you for fantastic info I used to be on the lookout for thiis inforkation ffor
    my mission.

  6. Wow! At last I got a website from where I know how to genuinely obtain useful data regarding my study
    and knowledge.