SQL 2 Excel - Variable Substitution - Reference PDF Print E-mail

Substitution variables were added in SQL 2 Excel version 0.9.0. You can use variables in the header, footer or even the SQL query. These variables gets substituted with the current value when the Workbook is being built.

User Variables

Variable Description Example H/F SQL
{USER_ID}
 The user id, $user->id    (0 when user not is logged in)
 177 X X
{USER_NAME}
 User name, $user->name Joomla-R-Us X X
{USERNAME} Username, $user->username
 admin X X
{USER_EMAIL}
 Users email address, $user->email
 admin@joomla-r-us X X
{USER_TYPE} user type,  "" or "Public Frontend"=not logged in [test for both], otherwise "Registered", "Author", "Editor", "Publisher", "Manager", "Administrator" or "Super Administrator" Registered X X
{USER_RDATE} User register date, $user->registerDate
 "2007-05-17 01:25:52" X X
{USER_LDATE} Users last visited date, $user->lastvisitDate
 "2007-11-02 18:51:29" X X
{USER_GID}
 User group ID, $user->gid;
 2 X X
{USER_ACTIVATION}
 User activation status,  $user->activation
 0 or 1 X X
{USER_AID}
 Users access id, $user->aid
 0,1 or 2
 X X
{USER_TZ}
 Users timezone, $user->params->timezone
 X X
{USER_LANG}
 Users group ID, $user->params->language X X
{USER_IP}
 Users IP Address, $_SERVER['REMOTE_ADDR'] 119.27.17.33 X X
{USER_AGENT}
 Users web browser agen, $_SERVER['HTTP_USER_AGENT'] Mozilla, Internet Explorer X X

 

Server Variables

Variable Description Example H/F SQL
{SERVER_URL}
 Server root URL, JURI::root()
 http://Joomla-R-Us/ X X
{SERVER_HOST}
 Server hostname, $_SERVER['HTTP_HOST']
 X X
{SERVER_IP} IP address of the server, $_SERVER['SERVER_ADDR']
 X X
{SERVER_SOFTWARE}
 Server software, $_SERVER['SERVER_SOFTWARE']
 X X
{SERVER_REFERER} HTTP Referer,  $_SERVER['HTTP_REFERER']
 http://xyz.com X X



 

Joomla Variables

Variable Description Example H/F SQL
{JOOMLA_VER}
 Joomla version,   $version->getShortVersion()
 1.5.9 X X
{JOOMLA_VER_LONG}
 Joomla version, long,  $version->getLongVersion()
X X
{JOOMLA_PARM_*}

URL parameters.

For instance, if the URL is /index.php?option=com_content&id=124

{JOOMLA_PARM_OPTION} == com_content
{JOOMLA_PARM_ID} == 124

 
X X


SQL 2 Excel Variables

Variable Description Example H/F SQL
{SQL2EXCEL_DLURL}
 Current download URL
X
{SQL2EXCEL_WB_FN}
 Workbook filename
 articles.xls X
{SQL2EXCEL_WB_LINK}

Workbook Download Link

 

 Articles X
{SQL2EXCEL_WB_DLCNT}
 Workbook download count
 28 X
{SQL2EXCEL_WS_SN}
 Worksheet Name
 Latest Articles
 X
{SQL2EXCEL_WS_DLCNT}
 Worksheet download count 44 X
{SQL2EXCEL_WS_SQL_RAW}
 Worksheet SQL Query, Raw SELECT *
FROM jos_content
WHERE created={USER_ID}
 X
{SQL2EXCEL_WS_SQL}
 Worksheet SQL Query,with substitutions
 SELECT *
FROM jos_content
WHERE created=83		 X
{SQL2EXCEL_WS_ROWS}
 Number of rows returned by Worksheet query
 287
 X



Date / Time Variables

(same as the PHP date() format strings)

Variable Description Example H/F SQL
Day
{d}
 Day of the month, 2 digits with leading zeros 01 to 31 X X
{D}
 A textual representation of a day, three lettersMon through Sun Mon through Sun X X
{j} Day of the month without leading zeros 1 to 31 X X
{l}
 A full textual representation of the day of the week Sunday through Saturday X X
{N} ISO-8601 numeric representation of the day of the week (added in PHP 5.1.0) 1 (for Monday) through 7 (for Sunday) X X
{S} English ordinal suffix for the day of the month, 2 characters s st, nd, rd or th. Works well with j X X
{w}
 Numeric representation of the day of the week 0 (for Sunday) through 6 (for Saturday) X X
{z} The day of the year (starting from 0) 0 through 365 X X
Week
{W} ISO-8601 week number of year, weeks starting on Monday (added in PHP 4.1.0) Example: 42 (the 42nd week in the year) X X
Month
{F} A full textual representation of a month, such as January or March January through December X X
{m} Numeric representation of a month, with leading zeros 01 through 12 X X
{M} A short textual representation of a month, three letters Jan through Dec X X
{n} Numeric representation of a month, without leading zeros 1 through 12 X X
{t} Number of days in the given month 28 through 31 X X
Year
{L} Whether it's a leap year 1 if it is a leap year, 0 otherwise. X X
{o} ISO-8601 year number. This has the same value as Y, except that if the ISO week number (W) belongs to the previous or next year, that year is used instead. (added in PHP 5.1.0) Examples: 1999 or 2003 X X
{Y} A full numeric representation of a year, 4 digits Examples: 1999 or 2003 X X
{y} A two digit representation of a year Examples: 99 or 03 X X
Time
{a} Lowercase Ante meridiem and Post meridiem am or pm X X
{A} Uppercase Ante meridiem and Post meridiem AM or PM X X
{B} Swatch Internet time 000 through 999 X X
{g} 12-hour format of an hour without leading zeros 1 through 12 X X
{G} 24-hour format of an hour without leading zeros 0 through 23 X X
{h} 12-hour format of an hour with leading zeros 01 through 12 X X
{H} 24-hour format of an hour with leading zeros 00 through 23 X X
{i} Minutes with leading zeros 00 to 59 X X
{s} Seconds, with leading zeros 00 through 59 X X
{u} Microseconds (added in PHP 5.2.2) Example: 54321 X X
Timezone
{e} Timezone identifier (added in PHP 5.1.0) Examples: UTC, GMT, Atlantic/Azores X X
{I} Whether or not the date is in daylight saving time 1 if Daylight Saving Time, 0 otherwise. X X
{O} Difference to Greenwich time (GMT) in hours Example: +0200 X X
{P} Difference to Greenwich time (GMT) with colon between hours and minutes (added in PHP 5.1.3) Example: +02:00 X X
{T} Timezone abbreviation Examples: EST, MDT ... X X
{Z} Timezone offset in seconds. The offset for timezones west of UTC is always negative, and for those east of UTC is always positive. -43200 through 50400 X X
Full Date/Time
{c} ISO 8601 date (added in PHP 5) 2004-02-12T15:19:21+00:00 X X
{r}
» RFC 2822 formatted date
Example: Thu, 21 Dec 2000 16:01:07 +0200 X X
{U} TSeconds since the Unix Epoch (January 1 1970 00:00:00 GMT) See time() X X


H/F : Worksheet Header / Footer
SQL : SQL Query



Comments (7)add comment
feedSubscribe to this comment's feed

scandaloustruth said:

scandaloustruth
 ...
Fantastic! Can this list be added to the componant so that it's available for reference while writing SQL? In a popup window maybe.
 
March 06, 2009
Votes: +1

Mats @ Joomla-R-Us said:

admin
 ...
Of course it can be added! Consider it done! Should have thought of that myself, duh. Will be included in the next release.
 
March 06, 2009
Votes: +0

Mats @ Joomla-R-Us said:

admin
 ...
Added Help Links under the SQL Query in the Worksheet form in v 0.9.1 which was just released. You can add/change these links as well. Click on Parameters in the SQL 2 Excel Toolbar and you can add/change the links as you wish.
 
March 08, 2009
Votes: +0

Mnervi said:

0
 ...
Hello,

after update to v1.6.2 (PHP 5.2.smilies/cool.gif the hour variables {H} and {h} won't be substituted; under v1.4.0 worked well.

and thank you for the very nice job.
 
August 02, 2009
Votes: +0

Mats @ Joomla-R-Us said:

admin
 ...
@Mnervi : Sorry, it's been fixed in version 1.6.3 which was released just now.
 
August 02, 2009
Votes: +1

Mnervi said:

0
 ...
wonderful, it works like a clockwork..

thank you very much
 
August 03, 2009
Votes: +1

Stephen said:

0
 ...
Can these variable subst. be added to the Extra Cells section of the SQL2EXCEL PRO.

When doing a sum you dont know how many lines to sum so the {SQL2EXCEL_WS_ROWS} is a good tool to use.
 
September 09, 2009
Votes: +1

Write comment

security image
Write the displayed characters


busy
 
 
  Design by augs-burg.de & go-vista.de  
 
     
 
   
  

The Joomla!(R) name is used under a limited license from Open Source Matters in the United States and other countries. http://Joomla-R-Us.com is not affiliated with or endorsed by Open Source Matters or the Joomla! Project.