Procedural File: queryutil.php

void buildQuery( $tablenames, $columnnames, [ $where = ''], [ $orderby = ''], [ $limitmax = ''], [ $limitmin = ''])

buildSimpleQuery

This can build a fairly complex query in a cross-database fashion. It takes arrays for most of the fields and converts it into a fully-qualified SQL query for the database held in $pnconfig['dbtype']

Example: The following db abstract code -

$authorscolumn = $pntable['authors_column']; $storiescolumn = $pntable['stories_column']; $result = $dbconn->query ("SELECT $authorscolumn[aid], $authorscolumn[name], $storiescolumn[title] FROM $pntable['authors'], $pntable['stories'] WHERE $authorscolumn[aid]=$storiescolumn[aid] AND $authorscolumn[aid]='$aid' ORDER BY $storiescolumn[time] DESC LIMIT 2, 4");

Could be re-written as:

$authorscolumn = $pntable['authors_column']; $storiescolumn = $pntable['stories_column']; $myquery = buildQuery (array ('authors', 'stories', array ($authorscolumn[aid], $authorscolumn[name], $storiescolumn[title]), "$authorscolumn[aid]=$storiescolumn[aid] AND $authorscolumn[aid]='$aid'", "$storiescolumn[time] DESC", 4, 2); $result = $dbconn->query($myquery);

(you could, of course, skip storing the string in $myquery...but for illustration I included it)

The nice thing about using this is that it takes care of db-specific ways of handling the "LIMIT" clause because it uses buildQuery internally.

ARGUMENTS: array: $tablenames = array of $pntable mapping names $columnames = array of fully qualified column names (table.column) $orderby = ORDERBY sort order ('ORDER BY' added automatically) sort order is based on indeces: ORDER BY $orderby[0], $orderby[1]...

string: $where = WHERE clause conditions ('WHERE' added automatically)

int: $limitmax = The maximum number of rows to return $limitmin = the offset in the result set to start at

Parameters

	$tablenames
	$columnnames
	$where
	$orderby
	$limitmax
	$limitmin

void buildSimpleQuery( $tablename, $columnnames, [ $where = ''], [ $orderby = ''], [ $limitmax = ''], [ $limitmin = ''])

buildSimpleQuery

This is a simplified version of buildQuery for use on one table. The arguments are specified as strings in most cases instead of arrays.

Example: The following db abstract code -

$column = $pntable['authors_column']; $result = $dbconn->query ("SELECT $column[aid], $column[name] FROM $pntable['authors'] WHERE $column[radminarticle]=1 ORDER BY $column[aid] LIMIT 1");

Could be re-written as:

$column = $pntable['authors_column']; $myquery = buildSimpleQuery ('authors', array ('aid', 'name'), "$column[radminarticle]=1", "$column[aid]", 1); $result = $dbconn->query($myquery);

(you could, of course, skip storing the string in $myquery...but for illustration I included it)

The nice thing about using this is that it takes care of db-specific ways of handling the "LIMIT" clause because it uses buildQuery internally.

ARGUMENTS: Strings: $tablename = $pntable mapping name $where = WHERE clause conditions ('WHERE' added automatically) $orderby = ORDERBY sort order ('ORDER BY' added automatically)

int: $limitmax = The maximum number of rows to return $limitmin = the offset in the result set to start at

array: $columnames = array of $pntable mapping names to return from the query

Parameters

	$tablename
	$columnnames
	$where
	$orderby
	$limitmax
	$limitmin

void dbescape( $textstring)

This method escapes strings appropriately for the 'dbtype' database currently in use. No "unescaping" is necessary when you pull it out since the result strings are un-escaped anyway.

Takes a string arg and returns a string argument escaped for the appropriate database or processed through the addslashes method if no database-specific method found.

Parameters

$textstring

void getAllColumns( $tablename)

getAllColumns

returns a string containing the fully qualified column names for a select, insert, or update statement. The order is not guaranteed, however. The safest use for this is where you would use a "SELECT *" query. Separating whitespace is automatically added for safety.

for example: if the table 'example' has columns 'id', and 'name' a call of:

$sel = getAllColumns('example');

would resulting in $sel having the value:

" example.id as \"id\", example.name AS \"name\" "

see: getAllColumnsFrom for inclusion of the tablename getSelectAllColumnsFrom for inclusion of select and tablename getColumnsViaHashKeys for grabbing a specific set of columns buildSimpleQuery for building a query on one table in a single call buildQuery for building a join query in a single call

Parameters

$tablename

void getAllColumnsFrom( $tablename, [ $whereclause = ''])

getAllColumnsFrom

adds "FROM $pntable[$tablename] " to the value returned from getAllColumns

also adds an optional WHERE class to be automatically added

Parameters

	$tablename
	$whereclause

void getColumnsViaHashKeys( $tablename, $column_key_hash)

Gets a list of column names, properly quoted for associative array use later on based on the keys in the column_key_hash variable. For example, if:

$tablename = 'downloads_newdownload' $column_key_hash = array ('lid' => '');

then

this will return (assuming the orig, default column name mappings):

"{$prefix}_downloads_newdownload.lid as \"lid\""

This ensures that associative array mappings will be correct since even case-insensitive databases such as oracle respect your "AS" naming if it is enclosed in literal quotes.

fifers: should probably base this on an indexed array instead...

Parameters

	$tablename
	$column_key_hash

void getColumnsViaHashKeysFrom( $tablename, $column_key_hash, [ $whereclause = ''])

Returns a statement of the form:

tablename.hashval1 as \"hashkey1\", ...

based on the KEYS in the $column_key_hash variable. useful to build a query that only needs a couple of columns returned.

fifers: should probably base this on an indexed array instead...

Parameters

	$tablename
	$column_key_hash
	$whereclause

void getSelectAllColumns( $tablename)

getSelectAllColumns

prepends "SELECT" to the value returned from getAllColumns

Parameters

$tablename

void getSelectAllColumnsFrom( $tablename, [ $whereclause = ''])

getSelectAllColumnsFrom

prepends "SELECT" to the value returned from getAllColumnsFrom

also adds an optional WHERE class to be automatically added

Parameters

	$tablename
	$whereclause

void getSelectColumnsViaHashKeysFrom( $tablename, $column_key_hash, [ $whereclause = ''])

This method is simply for naming convenience. It could be eliminated

since it merely calls getSelectViaHashKeysFrom and returns what it returns. If you follow the naming conventions I used in this file, however, it must exist for consistency!

Parameters

	$tablename
	$column_key_hash
	$whereclause

void getSelectViaHashKeysFrom( $tablename, $column_key_hash, [ $whereclause = ''])

This adds the SELECT to the front of the getColumnsViaHashKeysFrom method's returned value.

Parameters

	$tablename
	$column_key_hash
	$whereclause