Close session after executing database query

问题:

I have very little experience with joomla and sql and I would really appreciate your help! I am using joomla 2.5 and I am querying data from the database and storing it in memory with the following code:

function getList()
{
    $mainframe = JFactory::getApplication('site');
        $db = JFactory::getDBO();
        $query = "  SELECT 
                    * 
                FROM 
                    #__ListUser 
                WHERE
                    $db->setQuery( $query );"
        $rows = $db->loadObjectList();
        return $rows;
}

I have 3 questions,

1. When I query the database, a new DB session is opened, Do I need to close it after or is automatic?
   
2. Do you know of a more efficient way to achieve this method (a user session memory size is about 11MB!)
   
3. Is there any security issue with accessing the database using this method?

Thank you very much! any help would be very appreciated!

回答1:

The code should look like this (I don't see how it can work now):

function getList()
{
   // $mainframe = JFactory::getApplication('site'); // you don't need this line!
        $db = JFactory::getDBO();
        $query = "  SELECT 
                    * 
                FROM 
                    #__ListUser 
                WHERE
                    1=1"; // just some condition to extract selected rows
        $db->setQuery( $query ); // this sets the query and it's joomla, not sql.
        $rows = $db->loadObjectList();
        return $rows;
}

Please note the WHERE .... needs a condition (else if you want all the rows, remove WHERE and what follows)

1. You don't need to close it
2. 11Mb is not necessarily due to that query, try adding LIMIT 0,1 (to return just one row) you'll see your memory doesn't change much. Turn on debug in the global configuration, and reload the component. At the very bottom of the page you'll see which extensions are eating up your memory. 11Mb is acceptable though on most installations.
3. Should you create your WHERE condition using input params, just make sure you $db->quote() any values to prevent SQL-injection.

回答2:

Try

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->quote('*')
      ->from($db->quoteName('#__Listuser') // Do you really have upper case there?
      ->where('your condition with proper quoting');
$db->setQuery($query);
$rows = $db->loadObjectList();

回答3:

1.

UNCOOL:

If you want to close or disconnect the database-session, you may use:

$db->disconnect(); // See: http://api.joomla.org/cms-3/classes/JDatabaseDriver.html#method_disconnect

But i guess, that the database-connection for every other module, plugin or template that want to use JFactory::getDBO(); is also closed then and needs to be reopened.

BETTER:

You should use FREE RESULT instead after a query is transfered to a PHP-Variable: http://api.joomla.org/cms-3/classes/JDatabaseDriverMysql.html#method_freeResult

$db->freeResult();