Database

At this point I would suggest you install phpmyadmin, a decent front-end for browsing tables and performing database queries and operations. On Debian variants the following works:

#apt-get install phpmyadmin

Now to reach your phpmyadmin page go to http://yourserverip/phpmyadmin/

Ok, have a look at the interface and check your jos tables to get an idea of what’s in there. Our first task is to display the titles of all articles in the database. Basically, getting stuff from the database is just three steps, get a reference to the database handler, setup a query and execute the query against the database. It couldn’t be any simpler, here is the code:

<?php
  $db =& JFactory::getDBO();
  $sql = 'SELECT id,title from jos_content';
  $db->setQuery($sql);
  $rows = $db->loadObjectList();
?>

The first line invokes JFactory::getDBO() that returns a reference to the global JDatabase object (database driver) and the second one just defines the SQL statement string, the third line executes the query against the database and then the resulting objects are passed to $rows. What we need now is just to print the result. In general, this is what we can use to access the Joomla database without worrying too much about the internal mechanics.

On the other hand, what we need to realize is what the loadObjectList creature returns. It returns an array of rows but, each row is an object having as member properties the column headers. You might think this is complex, well it just sounds complex, the common way of representing tables is with arrays of arrays, the array of objects we have is even simpler, its use is very straight forward as you will see with the following example. By the way, the row objects belong to the stdClass which is something I do believe in but, I don’t understand.

<?php
  $db =& JFactory::getDBO();
  $sql = 'SELECT id,title from jos_content';
  $db->setQuery($sql);
  $rows = $db->loadObjectList();
  foreach ($rows as $row)
  {
     echo "<p>The article id of '$row->title' is $row->id </p>\n";
  }
?>

If you are traditional and you like examining tables as arrays of arrays you can use the loadAssocList alternative. This one returns an array of rows, with each row being an associative array of fields indexed by the column header. The following example is similar to the one above, the notation is just different. To be honest, I do prefer the previous example. Oh my God, I am transforming!

<?php
  $db =& JFactory::getDBO();
  $sql = 'SELECT id,title from jos_content';
  $db->setQuery($sql);
  $rows = $db->loadAssocList();
  foreach ($rows as $row)
  {
      echo "<p>The article id of '$row[title]' is $row[id] </p>\n";       
  }
?>

There are plenty other Jdatabase methods you may want to experiment with, look at the Joomla documentation API and see what you can get. The ones presented here are the ones most widely used.

One important thing to stress out is not all people prefix their Joomla databases with jos_ thus, we have to use the symbolic prefix #__ at the beginning of all table names, Joomla code will do the substitution magically. Hence, our SQL statement should look like this:

$sql = 'SELECT id,title from #__content';