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;