The Joomla! Community Magazine™

Using Joomla to Make Joomla! Starting with JDatabase

Written by | Sunday, 01 June 2014 00:00 | Published in 2014 June
Last month the magazine editors asked me to stop writing the series of articles for non-technologists because they determined that the objective had been achieved. I undertook the following article considering a more techy audience. After talking to the Spanish magazine team about writing articles that facilitate developers’ access to classes which allow us to build on Joomla using Joomla, the idea for this article arose. So I will try to write for those programmers who already have some knowledge ... although perhaps not yet the depth of knowledge that they want, all that Joomla offers to make your work more comfortable.

My colleague, Daniel Hernandez, helped to make this article legible and presentable, and I encourage him to write for the magazine in the future so that all may enjoy his knowledge and good work.

Let's cut to the chase… today we are going to talk about JDatabase:

PHP allows different ways of connection and interaction to a database. Joomla! contains strong functions in its API capable of extracting the layer of the database of proper third party developments, allowing to amplify the connectivity to new servers of databases, while overall giving greater ease and simplicity to the developer for the SQL codification. This avoids having to find and change each one of the methods where an instance is found when performing any change in the database.

For this we advise the use of the JDatabase library. This library allows us to instantiate a call to the database in a few lines and without needing to know the name of the database or the prefix of the tables to be used, since all these values are contained in the configuration.php:

$db = JFactory::getDbo();

$query = $db->getQuery(true);

While we do use an instantiate to a database with PHP we would have to specify the connection data, the name of the database to be used and the name of the complete table: 

$host = "host";

$user = "user_name";

$pass = "password";

$dbname = "data_base_name";

$connection = mysqli_connect($host,$user,$pass);

mysqli_select_db($dbname,$connection);

Another improvement which JDatabase allows is the creation of queries chained from the functions itself of this library which support one another allowing a greater readability of the code developed and more importantly, hiding the syntax of the query without compromising the portability of the code.

// Instantiate the connection.

$db = JFactory::getDbo();

// Create the query object.

$query = $db->getQuery(true);

// Prepare the name of the columns of our insert.

$columns = array('column1', 'column2', 'column3', 'column4');

// Insert values.

$values = array('value1', 'value2', 'value3', 'value4');

// Prepare the insert query.

$query

   ->insert($db->quoteName('#__table_withoutprefix'))

   ->columns($db->quoteName($columns))

    ->values(implode(',', $values));

// Establish the query with our object and execute the query.

$db->setQuery($query);

$db->query();

 

Now if we were to use the easier option, also valid, we would be sending the query's syntax completely.  

// Instantiate the connection.

$db = JFactory::getDbo();

// Create the object of the query.

$query = $db->getQuery(true);

// Prepare the name of the columns of are insert.

$columns = array('column1', 'column2', 'column3', 'column4');

// Insert values.

$values = array('value1', 'value2', 'value3', 'value4');

$query = 'INSERT INTO #__table_withoutprefix ('. $columns. ') VALUES('.$values.')' ;

$db->setQuery($query);

$db->query();

One of the strongest improvements that has been made in Joomla! 3.x, is the implementation of SQL transactions, such as TransactionStart of JDatabaseDriver, TransactionCommit and TransactionRollback, always when these are compatible with the database motor. This replaces the method queryBatch that we were using since Joomla! 2.5.

$db = JFactory::getDbo();

try

{

   

$db->transactionStart();

$query = $db->getQuery(true);

$columns = array('column1', 'column2', 'column3', 'column4');

$values = array('value1', 'value2', 'value3', 'value4');

        $query

    ->insert($db->quoteName('#__table_withoutprefix'))

    ->columns($db->quoteName($columns))

    ->values(implode(',', $values));

$db->setQuery($query);

$result = $db->execute();

$db->transactionCommit();

}

catch (Exception $e)

{

    //Capture the error and perform an execution rollback.

    $db->transactionRollback();

    JErrorPage::render($e);

}

If an exception occurs we can undo the changes using the TransactionRollback method. This allows us to take the database back to its original state if a problem occurs.

Let's not forget, for example, that we can make our extension independent of the database's motor technology if we strive a little more to learn how to use the classes offered to us in Joomla.

The following chart shows supported databases for Joomla in its different versions:

And with these first strokes about the use of “Joomla to make Joomla”, I hope that we have awoken the interest so you can start to discover how to do more with less effort.

Read 4254 times
Tagged under Developers, English
Pedro F. Vidal Lopez

Pedro F. Vidal Lopez

Simply... I love Open Source... I love Joomla!

I'm collaborating on projects Open Source since 1988 and with the Joomla community since 2005...

Learning English now;-)