Retrieving a database query in Joomla

Something that was mentioned several times at Joomla Day UK 2013 was the desire to be able to dump out the database query in a format that you can copy and paste straight into phpMyAdmin (or similar). In fact such a function already exists in the Joomla Database library called replacePrefix. This is about how to use it.

It really is as simpleas the usage case below:

// Get a db connection.
$db = JFactory::getDbo();
// Create a new query object
$query = $db->getQuery(true);
// Select all records form the weblinks table
$query->select('*')
      ->from('#__weblinks')
      ->order('created DESC');
$db->setQuery($query);
// Now run the function to get the query
$db->replacePrefix((string) $query);

This will output something along the lines of:

SELECT * FROM jos_weblinks ORDER BY created DESC

depending on your database prefix and database type (mysql, postgres etc.) so you can insert it straight into phpMyAdmin (or similar) to try to execute the code manually The relevant code on GitHub can be found here: https://github.com/joomla/joomla-cms/blob/3.4.1/libraries/joomla/database/driver.php#L1551