Course Code Library
Working Environment
//J3.x
define('_JEXEC', 1);
define('JPATH_BASE', __DIR__);
require_once JPATH_BASE . '/includes/defines.php';
require_once JPATH_BASE . '/includes/framework.php';
$app = JFactory::getApplication('site');
//J2.5
define('_JEXEC', 1);
define('DS', DIRECTORY_SEPARATOR);
define('JPATH_BASE', dirname(__FILE__));
require_once JPATH_BASE.'/includes/defines.php';
require_once JPATH_BASE.'/includes/framework.php';
$app = JFactory::getApplication('site');
$app->initialise();
Echo Out the Query
$theQuery = $query->__toString();
Single Value SELECT Function
// SAMPLE FUNCTION:
function getSingleDBValue( $getColumn, $fromTable, $whereColumn, $whereValue, $varType ) {
// returns one value from a target column / table where $column = X
// for the last param, pass: "int" or "string"
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('`'.$getColumn.'`');
$query->from($db->quoteName($fromTable));
if ($varType == "int" ) { $query->where('`'.$whereColumn.'` = ' . (int) $whereValue ); }
if ($varType == "string" ) { $query->where('`'.$whereColumn.'` = ' . $db->quote($whereValue) ); }
$db->setQuery($query);
$db->execute($query);
$result = $db->loadResult();
return $result;
}
// SAMPLE USE:
$getArticleID = 342;
$theTitle = getSingleDBValue( 'title', '#__content', 'id', $getArticleID, 'int' );
Get a Whole Row
function getRowFromTable( $fromTable, $whereColumn, $whereValue, $varType ) {
// returns ALL from TABLE where COLUMN equals VALUE
// for the last param, pass: "int" or "string"
// NB To get a single value, checkout getSingleDBValue()
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('*');
$query->from($fromTable);
if ($varType == "int" ) { $query->where('`'.$whereColumn.'` = ' . (int) $whereValue ); }
if ($varType == "string" ) { $query->where('`'.$whereColumn.'` = ' . $db->quote($whereValue) ); }
$db->setQuery($query);
$db->query($query);
$recordSet = $db->loadAssocList();
return $recordSet;
}
// e.g.: Get the entire record for customer #123:
$customerId = 123;
$customerProfile = getRowFromTable( '#__myCustomers', 'id', $customerId, 'int' );
print_r($customerProfile);
Single Value UPDATE Function
function doUpdateSingleDBValue ($setTable, $setColumn, $setValue, $setType, $whereColumn, $whereValue, $whereType) {
// similar to the *get* single value function, but more complex as both the set and where need types specified, and the set needs a value sent here, too. The order of the variables is a bit different, too.
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->update($db->quoteName($setTable));
//SET
if ($setType == "int" ) { $query->set('`'.$setColumn.'` = ' . (int) $setValue ); }
if ($setType == "float" ) { $query->set('`'.$setColumn.'` = ' . (float) $setValue ); }
if ($setType == "string" ) { $query->set('`'.$setColumn.'` = ' . $db->quote($setValue) ); }
// WHERE
if ($whereType == "int" ) { $query->where('`'.$whereColumn.'` = ' . (int) $whereValue ); }
if ($whereType == "string" ) { $query->where('`'.$whereColumn.'` = ' . $db->quote($whereValue) ); }
$db->setQuery($query);
$db->execute($query);
return;
}
// SAMPLE USE:
$newLoginName = "TheDude";
$targetID = 123;
doUpdateSingleDBValue ('#__users', 'username', $newLoginName, 'string', 'id', $targetID, 'int');
Sample Join Query
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('table1name.`fieldName01`, table1name.`fieldName02`, table2name.`fieldName03`, table2name.`fieldName04`');
// OR: $query->select('table1name.`fieldName01` as `newName01`, table1name.`fieldName02`, table2name.`fieldName03`, table2name.`fieldName04`');
$query->from('#__table1 AS table1name');
$query->innerJoin('#__table2 AS table2name ON table2name.`id` = table1name.`id`');
$db->setQuery($query);
$db->execute($query);
$results = $db->loadAssocList();
Delete Query
function doDeletion ($submittedTable, $submittedColumn, $submittedID) {
// versatile, as it works well for key index values where
// there is only 1 row AND/OR in situations where you target all
// columns that have an ID of X...
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->delete();
$query->from($submittedTable);
$query->where('`'.$submittedColumn.'` = ' . (int) $submittedID);
$db->setQuery($query);
$db->execute($query);
return;
}
Sample Usage:
$badUserId = 123;
doDeletion ("#__users", 'id', $badUserId);
Not severe enough for you? Well, you can also do something like:
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query = "TRUNCATE TABLE `#__mytable`";
$db->setQuery($query);
$db->execute($query);
Some Other Unusual Ones
function doFindValueInSet ($targetNumber) {
// returns the ID of the record if `fieldname`, which is a list like "1,2,3,4,5,6" has the $target number in it.
// useful for sort-of pseudo relational DB situations where, say, you have companies, and categories, and instead of a proper relational setup, you want to just keep the company categories in a field in the company table 9instead of a mapping table). Weird, but handy in certain situations.
// just presented to show how some unusual MySQL commands can work in most J! Jdatabase scenarios.
// Sometimes you have to play around w/ these to get the syntax right, but pretty much anything available in MySQL you can do via Jdatabase
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('`id`');
$query->from('#__tablename');
$query->where ('(FIND_IN_SET('. (int) $targetNumber.',`fieldname`))');
$db->setQuery($query, $offset=0, $limit=1);
$db->query($query);
$result = $db->loadResult();
return $result;
}
Nesting Parts of Your Queries Within Conditionals
(Fictitious) Example of how you can nest your operators and other goodies inside conitional statements to accomplish dynamic things.
function getCustomerRecords ($submittedUserID, $paymentMethod) {
// returns info on customers
// "credit" -- shows only those that used CCs
// "paypal" -- shows only those that used Paypal
// if $paymentMethod omitted, it shows ALL
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('*');
$query->from('#__customer_purchases');
$query->where('(`customer` = '. (int) $submittedUserID.')');
if ($paymentMethod == "credit"){
$query->where('(`paymentMethod` = 100)');
$query->order('`date` ASC');
}
if ($paymentMethod == "paypal"){
$query->where('(`paymentMethod` = 200)');
$query->order('`date` DESC');
}
$db->setQuery($query);
$db->execute($query);
$recordSet = $db->loadAssocList();
return $recordSet;
}
Get # of Rows in a Table
function getNumRowsInTable ($submittedField, $submittedTable) {
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($submittedField);
$query->from($submittedTable);
$db->setQuery($query);
$db->query($query);
$num_rows = $db->getNumRows();
return $num_rows;
}
e.g.:
$numRows = getNumRowsInTable ('id', '#__myTable');
echo $numRows;