|
|
Server Time:
Tuesday May 13 2008 06:29 PM |
|
Your Time:
|
 |
The idea behind aliasing in SQL statements is to group a set of columns together into a newly named "virtual" column that you refer to in your code. You can also use SQL functions such as SUM, or even concatenate strings, as we will do in this example.
Here is a very simple example of aliasing your SQL statements:
-------------------------------------------------------------------------
Our database (Access2000 in this case), consists of the following fields in a Table named "tblCtcs":
ctcID - autonumber
ctcFname - text
ctcLname - text
ctcBirthday - date/time
-------------------------------------------------------------------------
Note that in our Query below:
1) The database columns "ctcFname" and "ctcLname" are being concatenated and seperated by a space... that's the &' '&
2) " Birthday: " is actually a string included in the statement
3) "AS Name" defines our virtual column that we can refer to simply in our code as... you guessed it, "Name"
<cfquery name="jim"
datasource="jimdb">
SELECT ctcFname &' '& ctcLname &' Birthday: '& ctcBirthday AS Name
From tblCtcs
ORDER BY ctcFname
</cfquery>
-------------------------------------------------------------------------
Based on our SQL query above, we can print out a list of of Contacts, and their Birthdays, by referring to just one variable... the "virtual column" we created, like this:
<ul>
<cfoutput query="jim"><li>#jim.Name#</li></cfoutput>
</ul>
Which would show up in the browser like this:
* Carl Walker Birthday: 5/22/1968
* Diane Summer Birthday: 2/25/1972
* Ehrman Mack Birthday: 10/2/1932
* Jake Summer Birthday: 10/13/1995
* Leena Summer Birthday: 10/6/1998
* Marilyn Lavato Birthday: 4/2/1950
* Norma Mack Birthday: 7/4/1942
* Sonny Summer Birthday: 12/7/1962
-------------------------------------------------------------------------
Thanks a lot and Happy Aliasing!
Date added: Tue. December 10, 2002
Posted by: Jim Summer | Views: 14426 | Tested Platforms: CF5 | Difficulty: Intermediate
SQL
|
|
Search Engine Bot Notifier
This code detects the most common user agents (web browsers) and notifies you via email if it is not a recognized user agent as defined in the code. Usually this will be a bot of some sort. Extrememly useful for tracking how often Google, Yahoo, etc visits your site. It will email you the bot and a reverse IP lookup url with the IP appended so you can verify if it is a "good bot" or a "bad (spam) bot" (then you can block that IP or stop processing of the page). Use this in your home page or as an include file throughout your site. Nothing fancy it should work on MX also although I have not tested it there. - Date added: Wed. April 14, 2004
Replacing "enter" key with "<br>" tag
This little piece of code will transform those pesky "enter" keys in a textarea into "<br>" tags so your users input is printed out properly in your html page. - Date added: Fri. December 13, 2002
Navigation as an include file
Create an include file (custom tag) for your navigation to help make maintenance easier! This include file lights up the button depending on where the user is at in your website, and is XHTML 1.1 validated! If you need to edit your navigation, just do it in 1 place, the include file! Javascript included :) - Date added: Thu. December 12, 2002
Database Dates (between ranges)
This deals with database dates:
(1)inserting a properly formatted date into the database, and then
(2)pulling a query from the database between a date range defined by 2 text boxes. - Date added: Mon. December 9, 2002
Recordset Paging in Cold Fusion
This will pull a predefined number of records from a database, allow the user to change the number of records to be shown, and write the "NEXT" or "BACK" (or both) buttons at the bottom of the page. Thus allowing the user to "surf" through the database.
See it in action at http://freecfm.com/t/tentonhead/ and click on the "CTCS" link when you get there.
The HTML for this page has changed a little since I first did this (so it validates as XHTML1.1) but the CFML remains as it is in this snippet. - Date added: Mon. December 9, 2002
|
|
|
|
|
|
|
|
ColdFusion Hosting
Blink
Del.icio.us
Digg
Furl
Google
Simpy
Spurl
Y! MyWeb