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. See Substitution Variables - Howto for info on how to use these variables.

 

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 (12)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

cbeavec said:

0
 ...
How can I add to this query to only list records that have expired? (b.expire_date older than today)

SELECT a.id, a.username, a.name, a.email, b.job_title, b.publish_date,b.expire_date, b.is_active
FROM jos_users a, jos_tpjobs_job b
WHERE a.id=b.employer_id AND b.is_active ='y'
ORDER BY a.username

thanks

 
September 19, 2010
Votes: +0

Mats @ Joomla-R-Us.com said:

admin
 ...
@cbeavec:

Try this:

SELECT a.id, a.username, a.name, a.email, b.job_title, b.publish_date,b.expire_date, b.is_active
FROM jos_users a, jos_tpjobs_job b
WHERE a.id=b.employer_id AND b.is_active ='y' AND b.expire_date < NOW()
ORDER BY a.username

-Mats
 
September 19, 2010
Votes: +0

sdavies68 said:

sdavies68
 ...
Is there a way to insert a URL link into the SQL statement built from {SERVER_URL}
, {JOOMLA_PARM_*}, etc.
 
October 03, 2010
Votes: +0

Mats @ Joomla-R-Us said:

admin
 ...
@sdavies68: SERVER_URL should work in both the free version as well as in Pro. Passing parameters requires Pro version. If you have defined a variable, for example MYVAR1, then you can use that in your SQL as well, for example:


CONCAT('{SERVER_URL}/index.php?com_mycom&limit={MYVAR1}&id=',a.id) as `hyperlink`

Parameter values will be sent to the download through a URL when a user downloads the Workbook. Search the support forum for help on how to setup the URL for download.

-Mats
 
October 04, 2010
Votes: +0

humaxDVB said:

humaxDVB
 ...
hi, i have this command but the date on excel comes in general and not in date format, how can i use date/time variables to get the correct results?

SELECT bb.idbook,bg.name,bg.surname,bg.phone,bc.name AS country, bb.idguests, DATE_FORMAT(bb.valid_from,'%d/%m/%Y') AS 'check in',DATE_FORMAT(bb.valid_to,'%d/%m/%Y') AS 'check out',bb.nguests,bb.nchilds,bcat.name AS room_type,bb.idroom FROM frhotel_bookitbooking AS bb LEFT JOIN frhotel_bookitguests bg ON bb.idguests=bg.idguests LEFT JOIN frhotel_bookitcountry bc ON bg.idcountry=bc.idcountry LEFT JOIN frhotel_bookitcategory bcat ON bb.idcategory=bcat.idcategory
 
August 23, 2011
Votes: +1

Write comment
You must be logged in to post a comment. Please register if you do not have an account yet.

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.