Monday 8 April 2013

Joomla Database

Joomla can use different kinds of SQL database systems and run in a variety of environments with different table-prefixes. In addition to these functions, the class automatically creates the database connection. Besides instantiating the object you need just two lines of code to get a result from the database in a variety of formats. Using the Joomla database layer ensures a maximum of compatibility and flexibility for your extension.

Preparing the query

 First we instantiate the database object by  $db = JFactory::getDBO();
then we prepare the query. Joomla uses a placeholder for the prefix, the “#__”. In the next step, the $db->setQuery(), this string is replaced with the correct prefix. 

setQuery($query)

The setQuery($query) method sets up a database query for load result.

Basic Query Execution
The query() method is the basic tool for executing SQL queries on a database. 
Note: $query() returns an appropriate database resource if successful, or FALSE if not.
 
$db = JFactory::getDBO();
$query = "/* some valid sql string */";
$db->setQuery($query);
$result = $db->query();


Query Execution Information

 1) insertid()

 If you insert a record into a table that contains an AUTO_INCREMENT column, you can obtain the value stored into that column by calling the insertid() function.

$query = "INSERT INTO '#__example_table' ('name','email','username')
        VALUES ('John Smith','johnsmith@domain.example','johnsmith')";
$db->setQuery($query);
$db->query();
$user_id = $db->insertid();

2) loadResult()
Use loadResult() when you expect just a single value back from your database query.
idnameemailusername
1John Smithjohnsmith@domain.examplejohnsmith
2Magda Hellmanmagda_h@domain.examplemagdah
3Yvonne de Gaulleydg@domain.exampleydegaulle
This is often the result of a 'count' query to get a number of records:
$db = JFactory::getDBO();
$query = "
  SELECT COUNT(*)
    FROM ".$db->nameQuote('#__my_table')."
    WHERE ".$db->nameQuote('name')." = ".$db->quote($value).";
  ";
$db->setQuery($query);
$count = $db->loadResult();

3) loadRow()
loadRow() returns an indexed array from a single record in the table:
idnameemailusername
1John Smithjohnsmith@domain.examplejohnsmith
2Magda Hellmanmagda_h@domain.examplemagdah
3Yvonne de Gaulleydg@domain.exampleydegaulle

. . .
$db->setQuery($query);
$row = $db->loadRow();
print_r($row);
will give:
Array ( [0] => 1 [1] => John Smith [2] => johnsmith@domain.example [3] => johnsmith ) 

Notes:
  1. The array indices are numeric starting from zero.
  2. Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful.

4) loadAssoc()
loadAssoc() returns an associated array from a single record in the table:
. . .
$db->setQuery($query);
$row = $db->loadAssoc();
print_r($row);
will give:
Array ( [id] => 1 [name] => John Smith [email] => johnsmith@domain.example [username] => johnsmith )

Notes:
  1. Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful.

 5) loadObject()
loadObject returns a PHP object from a single record in the table:
. . .
$db->setQuery($query);
$result = $db->loadObject();
print_r($result);
will give:
stdClass Object ( [id] => 1 [name] => John Smith [email] => johnsmith@domain.example [username] => johnsmith )

Notes:
  1. Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful.

6) loadResultArray()
idnameemailusername
1John Smithjohnsmith@domain.examplejohnsmith
2Magda Hellmanmagda_h@domain.examplemagdah
3Yvonne de Gaulleydg@domain.exampleydegaulle

loadResultArray() returns an indexed array from a single column in the table:
$query = "
  SELECT name, email, username
    FROM . . . ";
. . .
$db->setQuery($query);
$column= $db->loadResultArray();
print_r($column);
will give:
Array ( [0] => John Smith [1] => Magda Hellman [2] => Yvonne de Gaulle )

Notes:
  1. The array indices are numeric starting from zero.
  2. loadResultArray() is equivalent to loadResultArray(0).

7) loadResultArray($index)
loadResultArray($index) returns an indexed array from a single column in the table:
$query = "
  SELECT name, email, username
    FROM . . . ";
. . .
$db->setQuery($query);
$column= $db->loadResultArray(1);
print_r($column);
will give:
Array ( [0] => johnsmith@domain.example [1] => magda_h@domain.example [2] => ydg@domain.example )


oadResultArray($index) allows you to iterate through a series of columns in the results
. . .
$db->setQuery($query);
for ( $i = 0; $i <= 2; $i++ ) {
  $column= $db->loadResultArray($i);
  print_r($column);
}
will give:
Array ( [0] => John Smith [1] => Magda Hellman [2] => Yvonne de Gaulle )
Array ( [0] => johnsmith@domain.example [1] => magda_h@domain.example [2] => ydg@domain.example )
Array ( [0] => johnsmith [1] => magdah [2] => ydegaulle )
Notes:
  1. The array indices are numeric starting from zero.


 8) loadRowList()
Each of these results functions will return multiple records from the database.
idnameemailusername
1John Smithjohnsmith@domain.examplejohnsmith
2Magda Hellmanmagda_h@domain.examplemagdah
3Yvonne de Gaulleydg@domain.exampleydegaulle

loadRowList() returns an indexed array of indexed arrays from the table records returned by the query:
. . .
$db->setQuery($query);
$row = $db->loadRowList();
print_r($row);
will give (with line breaks added for clarity):
Array ( 
[0] => Array ( [0] => 1 [1] => John Smith [2] => johnsmith@domain.example [3] => johnsmith ) 
[1] => Array ( [0] => 2 [1] => Magda Hellman [2] => magda_h@domain.example [3] => magdah ) 
[2] => Array ( [0] => 3 [1] => Yvonne de Gaulle [2] => ydg@domain.example [3] => ydegaulle ) 
)


9) loadAssocList()
loadAssocList() returns an indexed array of associated arrays from the table records returned by the query:
. . .
$db->setQuery($query);
$row = $db->loadAssocList();
print_r($row);
will give (with line breaks added for clarity):
Array ( 
[0] => Array ( [id] => 1 [name] => John Smith [email] => johnsmith@domain.example [username] => johnsmith ) 
[1] => Array ( [id] => 2 [name] => Magda Hellman [email] => magda_h@domain.example [username] => magdah ) 
[2] => Array ( [id] => 3 [name] => Yvonne de Gaulle [email] => ydg@domain.example [username] => ydegaulle ) 
) 


10) loadAssocList($key)
loadAssocList('key') returns an associated array - indexed on 'key' - of associated arrays from the table records returned by the query:
. . .
$db->setQuery($query);
$row = $db->loadAssocList('username');
print_r($row);
will give (with line breaks added for clarity):
Array ( 
[johnsmith] => Array ( [id] => 1 [name] => John Smith [email] => johnsmith@domain.example [username] => johnsmith ) 
[magdah] => Array ( [id] => 2 [name] => Magda Hellman [email] => magda_h@domain.example [username] => magdah ) 
[ydegaulle] => Array ( [id] => 3 [name] => Yvonne de Gaulle [email] => ydg@domain.example [username] => ydegaulle ) 
)



11) loadObjectList()
loadObjectList() returns an indexed array of PHP objects from the table records returned by the query:
. . .
$db->setQuery($query);
$row = $db->loadObjectList();
print_r($row);
will give (with line breaks added for clarity):
Array ( 
[0] => stdClass Object ( [id] => 1 [name] => John Smith 
    [email] => johnsmith@domain.example [username] => johnsmith ) 
[1] => stdClass Object ( [id] => 2 [name] => Magda Hellman 
    [email] => magda_h@domain.example [username] => magdah ) 
[2] => stdClass Object ( [id] => 3 [name] => Yvonne de Gaulle 
    [email] => ydg@domain.example [username] => ydegaulle ) 
)



12) loadObjectList('key')
loadObjectList($key) returns an associated array - indexed on 'key' - of objects from the table records returned by the query:
. . .
$db->setQuery($query);
$row = $db->loadObjectList('username');
print_r($row);
will give (with line breaks added for clarity):
Array ( 
[johnsmith] => stdClass Object ( [id] => 1 [name] => John Smith 
    [email] => johnsmith@domain.example [username] => johnsmith ) 
[magdah] => stdClass Object ( [id] => 2 [name] => Magda Hellman 
    [email] => magda_h@domain.example [username] => magdah ) 
[ydegaulle] => stdClass Object ( [id] => 3 [name] => Yvonne de Gaulle 
    [email] => ydg@domain.example [username] => ydegaulle ) 
)



13) getNumRows()
getNumRows() will return the number of result rows found by the last query and waiting to be read. To get a result from getNumRows() you have to run it after the query and before you have retrieved any results.
. . .
$db->setQuery($query);
$db->query();
$num_rows = $db->getNumRows();
print_r($num_rows);
$result = $db->loadRowList();
will return
3
Note: if you run getNumRows() after loadRowList() - or any other retrieval method - you may get a PHP Warning:
Warning: mysql_num_rows(): 80 is not a valid MySQL result resource 
in D:\xampp\htdocs\joomla1.5a\libraries\joomla\database\database\mysql.php on line 344




Tips, Tricks & FAQ

Subqueries

Subqueries should be written as follows:
SELECT * FROM #__example WHERE id IN (SELECT id FROM #__example2);
These kinds of queries are supported since MySQL 4.1. If this method fails, you can split the query into two as demonstrated below. Please note that this will (often unnecessarily) increase the load on the database server.
$query = "SELECT id FROM #__example2";
$database->setQuery($query);
$query = "SELECT * FROM #__example WHERE id IN (". implode(",", $database->loadResultArray()) .")";
 
 
 
 
 
Thanks guys