From MediaWiki

Jump to: navigation, search

MySQL is installed on this site.

Hundreds of pages that use MySQL or MySQLInline are in the database category. If you use this on new pages, please put [[Category:Database]] at the end.

You can also hunt them down by putting "mySQL" or "mySQLInline" in the general search field at left.


* $Id: MySQL.php,v 1.13 2016/01/25 23:24:30 jan Exp jan $
* JWS-2016-01-26: Began converting to parser functions by faking named parameters.
* JWS-2015-12-06: First cut at images; re-sizing not implemented until PHP is upgraded >= 5.4.
* JWS-2015-11-29: Integrated $acc and $pattern into paging links.
* JWS-2015-11-22: Made a series of numbered paging links (1 2 3 4 5 ...), but these don't work with $acc nor $pattern yet.
* JWS-2015-11-19: "#Records" arbitrarily tagged onto paged result links. Put paged results under a heading like "==Records=="
*		   to make the browser jump there after paging a result set. This is yukky, but the anchor is not passed to PHP.
* JWS-2015-11-17: implemented long_text_columns, to allow truncation of long text with tooltip of full text.
* JWS-2015-11-15: accumulator added for carrying running total (or other information) over pagination using $pg_count.
* JWS-2013-09-09: automagically right justify data beginning with a numeral or dollar sign.
* JWS-2012-03-09: started long text truncation and image display.
* Wiki named parameters:
*	$MySQL_handles[] (REQUIRED global): array of associations of database login credentials, specified as:
*			handle => array(host, user, password, database)
*		For security reasons, the GRANT for the 'user' should be restricted to the given 'database', and SELECT only.
*	$input (REQUIRED arg): SQL SELECT statement that returns records to display.
*	$database (REQUIRED arg): the handle of the database login credentials to use.
*    Pagination:
*	$pg_count (REQUIRED): integer, number of rows to display at a time, together with pagination links.
*	$pg_rec_count (REQUIRED): string, an SQL query to return the number of records expected.
*	$pg_exponent (OPTIONAL): integer, if list is long, show $pg_exponent links, followed by $pg_exponent^2, folowed by $pg_exponent^3, etc.
*	$pg_acc_col (OPTIONAL): string, the name of the column used as an accumulator ("%AcCuMuLaToR%"), in order to span pagination. Such
*		queries must have a JOIN that pre-sets the accumulator, such as:
*			JOIN (SELECT @Running_Total := %AcCuMuLaToR%) x
*		The SELECT column to be accumulated typically has two columns: the one named in $acc_col, and an accumulator column, such as:
*			    Sale AS Sales,
*			    @Running_Total := @Running_Total + Sale  AS Total,
*			...
*			JOIN (SELECT @Running_Total := %AcCuMuLaToR%) x
*			...
*			|pg_count=25
*			|pg_rec_count=SELECT COUNT(*) FROM ...
*			|pg_acc_col=Total
*			|pg_acc_sum=SELECT SUM(Sale) FROM (SELECT Total FROM ... LIMIT 0, %AcCuMuLaToRsUm%) x
*	$pg_acc_sum (REQUIRED with $pg_acc_col): an SQL query to return the sum of the accumulator field, to be uset with a LIMIT
*		statement to accumulate through pagination. It must use a subquery for the FROM source, ending with
*		"LIMIT 0,%AcCuMuLaToRsUm%) x", where %AcCuMuLaToRsUm% will be replaced with the amount to be summed to the
*		accumulator column named in $pg_acc_col, in order to carry the accumulator to the next result page.
*	    Example:
*		|acc_sum=SELECT sum(Total)
*			FROM (SELECT Total FROM sa_general_journal
*			WHERE Credit = {{{1}}} OR Debit = {{{1}}} ORDER BY Date LIMIT 0, %AcCuMuLaToRsUm%) x
*    Searching:
*	where (POST): user-entered search criteria, which replaces "%SeArCh%" in the WHERE clause.
*	whereMsg (OPTIONAL, only valid with "where"): a message to be displayed next to the search string input box.
*    Text manipulation:
*	long_text_columns (REQUIRED): a space-separated list of columns that are to be truncated at a certain length, default 50.
*	long_text_column_size (OPTIONAL): a number of characters to display before truncating, if 50 is not desired.
*	right_justify_columns (OPTIONAL) (not yet implemented): a space-separated list of column indeces that are to be right-justified.
*		If not defined, any column data that begins with a numeral, dollar sign, or minus sign automagically right-justifies.
*    Images:
*	image_columns (REQUIRED): a space-separated list of columns that are to be displayed as images
*	image_column_sizes (not yet implemented) (OPTIONAL, requires 'image_columns'): a space-separated list of pixel dimensions, like '640x480'
*    Graphlets: (not implemented)
*	gr_byRow_columns (EITHER/AND): a space-separated list of columns to be graphed at the end of each row
*	gr_byCol_columns (EITHER/AND): a space-separated list of columns to be graphed at the end of the column; disables pagination and sorting by column
*	gr_byRow_size (OPTIONAL): pixel dimensions of the row graphlets, default 85x20
*	gr_byCol_size (OPTIONAL): pixel dimensions of each of the column graphlets, default 85x20, separated by spaces
*	gr_byRow_type (OPTIONAL): type of the dimensions of the row graphlets, either "line" or "hist", default "line"
*	gr_byCol_type (OPTIONAL): type of the dimensions of the column graphlets, either "line" or "hist", default "line", separated by spaces
* URL (GET) parameters:
*	acc: number, an accumulator that can be used to maintain a running total over pagination, substituted
*		for "%AcCuMuLaToR%", typically in a cross-join (such as 'JOIN (SELECT @accumulator := %AcCuMuLaToR%")') in order
*		to initialize a record-to-record quantity, such as a running total. Dollar signs and commas are removed.
*	acc_prev: number, the value of "acc" from the previous page, use in the "prev" link.
*	start: number of records to be skipped when paginating through a long record set.
* Columns are specified by zero-based index, rather than by column name, because we often use column names that are very complex, such
* as clickable URLs. This not only makes entering them more difficult, but it is nigh well impossible to specify a sentinel character
* for separating such column names. Keep in mind that column indeces will change if you insert or delete columns in your query.


* This function requires a SELECT that returns exactly one row with exactly one column, which output directly in place, without
* an enclosing "
* Example:
*     "My gramma drank {#mySQLinline|SELECT SUM(`Beers` FROM BeerLog WHERE `Drinker` = "Gramma"|database=DrinkingBuddies} beers."

Share your opinion

blog comments powered by Disqus
Personal tools
Get our newsletter!
Email Address:

entry points
help (off site)
Environmental jobs, green volunteering, good work! Powered by the wind! This server and other
EcoReality operations
are 100% wind powered.
Powered by Mac OS X Powered by Mac MediaWiki Powered by MariaDB Powered by Valentina Studio Pro