SQL, Cold Fusion, PHP and Javascript code snippets

Code Archive

T-SQL

Search all columns in all tables for an occurrence of a string

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + 'CHANGE_THIS_TO_SEARCH_CRITERIA' + '%','''')
WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0 ) WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') AND QUOTENAME(COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN INSERT INTO #Results EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 ) END END END SELECT ColumnName, ColumnValue FROM #Results
DROP TABLE #Results

Replace text in every column in every table in a database. This was developed to recover from the recent SQL Injection hacks that have been coming out of China. You only need to change the @badtext variable on line 2 (@replacewith too if necessary) then run this in Query Analyzer.

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @BadTextFixed nvarchar(110), @SQL nvarchar(4000), @ReplaceCount int, @BadText nvarchar(120), @ReplaceWith nvarchar(120) SET @BadText = 'Bad text goes here' SET @ReplaceWith = '' SET @TableName = '' SET @BadTextFixed = QUOTENAME('%' + @BadText + '%','''') SET @ReplaceCount = 0 WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0 ) WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') AND QUOTENAME(COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN SET @SQL= 'UPDATE ' + @TableName + ' SET ' + @ColumnName + ' = REPLACE(' + @ColumnName + ', ' + QUOTENAME(@BadText, '''') + ', ' + QUOTENAME(@ReplaceWith, '''') + ') WHERE ' + @ColumnName + ' LIKE ' + @BadTextFixed EXEC (@SQL) SET @ReplaceCount = @ReplaceCount + @@ROWCOUNT END END END SELECT CAST(@ReplaceCount AS varchar) + ' instances replaced' AS 'Results'

Retreive everything in a string based column up to the first comma.

SUBSTRING(dbo.mytable.keywords, 1, CAST(PATINDEX('%,%', replace(dbo.mytable.keywords, ' ', ',')) -1 as integer))

Select by specific date, ignoring the time portion

SELECT count(dt) as hits, CONVERT(CHAR(10),dt,120) as date FROM visitors
group by CONVERT(CHAR(10),dt,120)
order by CONVERT(CHAR(10),dt,120) desc

Dynamic SQL example

CREATE PROCEDURE dbo.Emailer
@storeid integer
AS
exec ('Insert into #temp (CustID, CustEmail, CustFirstName, CustLastName)
SELECT CustID, ltrim(rtrim(CustEmail)), CustFirstName, CustLastName
FROM dbo.Customer C
WHERE (CustID IN
(SELECT CustID
FROM SnapshotOrders
WHERE StID = ' + @storeid + '))')

Remove duplicate records from a table

DELETE FROM Items
WHERE EXISTS (
SELECT Id
FROM Items ItemsInner
WHERE ItemsInner.Itemnumber = Items.Itemnumber
AND ItemsInner.Id < Items.Id )

Remove duplicates in a temp table

(We've used this for a client to remove duplicates prior to sending a mass email)
CREATE PROCEDURE dbo.EmailerMComposeRun AS
declare @sql nvarchar (2000)
declare @fixsql nvarchar (2000)
declare @stid integer
create table #temp (CustID integer, CustEmail varchar(100), CustFirstname varchar (70), CustLastName varchar(80))
set @sql = 'Insert into #temp (CustID, CustEmail, CustFirstName, CustLastName) SELECT CustID, ltrim(rtrim(CustEmail)), CustFirstName, CustLastName
FROM dbo.Customer C
WHERE (CustID IN
(SELECT CustID
FROM tblOrders
WHERE StID = 1)) AND (CustEmail IS NOT NULL) AND CustEmail LIKE ''%@%'' AND CustEmail LIKE ''%.%'''
exec sp_executesql @sql
set @fixsql = '
DELETE FROM #temp
WHERE EXISTS (
SELECT CustID
FROM #temp GetCustInner
WHERE GetCustInner.CustEmail = #temp.CustEmail
AND GetCustInner.CustID < #temp.CustID )'
exec sp_executesql @fixsql
Select * from #temp
drop table #temp

Get the ID of the record added in a stored procedure

CREATE PROCEDURE dbo.CreateGallery
@galleryname varchar(80),
@galleryid int OUTPUT
AS
insert into photogallery(galleryname)
VALUES (@galleryname)
SET @GalleryID = @@IDENTITY

Get a list of all records added in the last X days

SELECT COUNT(*) as ItemsAdded, CONVERT(varchar(8), dateadded, 101) as DateAdded
FROM dbo.products
where dateadded > DATEADD(Day, -10, getdate())
group by CONVERT(varchar(8), dateadded, 101)
order by CONVERT(varchar(8), dateadded, 101) desc

Replace a string of data in a TEXT field.

UPDATE TableName SET myFieldName = REPLACE(SUBSTRING(myFieldName, 1, DATALENGTH(myFieldName)), 'My Bad Text', 'My New Text')

Get recordcounts for all tables in a database.

select 'Table Name'=convert(varchar(50),t.TABLE_NAME), 'Records'=max(i.rows) from sysindexes i, INFORMATION_SCHEMA.TABLES t where t.TABLE_NAME = object_name(i.id) and t.TABLE_TYPE = 'BASE TABLE' group by t.TABLE_SCHEMA, t.TABLE_NAME ORDER BY Records DESC

Update one table with information from another

UPDATE dbo.archive
SET dbo.archive.name = dbo.clients.name
FROM clients
WHERE clients.clientID = archive.clientID

Update one table (accessoryitems) with information from another (products)

INSERT INTO AccessoryItems(productid, accessoryid)
SELECT products.productID, products.accessories as Accessoryid
FROM Products

Pad a varchar field with some character (zeros in this case)

UPDATE Tracking
SET trackingnumber = Right('0000000000'+CAST(trackingnumber AS varchar(10)),10)

Very simple case statement

CASE WHEN left(Name, 3) = 'old' THEN (dbo.clientinfo.Last_Name) ELSE (dbo.clientarchive.LastName) END

Pick a random record from a table (the NEWID() function is the key here)

SELECT TOP 1 dbo.products.ProductName, dbo.products.ProductImage, dbo.products.ProductID
FROM dbo.products
ORDER BY NEWID()

Combine multiple columns or rows into one string variable or comma delimited value

(The output here would be CardNumbers = "02/2007, 02/2007, 02/2007")
DECLARE @AList varchar(25)
SELECT @AList = COALESCE(@AList + ', ', '') + cast(cardexpmonth as varchar(2)) + '/' + cast(cardexpyear as varchar(4))
FROM dbo.clientinfo
SELECT @AList as CardNumbers
Coalesce means 'Take this column and if its not null or the first record, add the comma and then add whatever other columns I want and then keep looping. I use this to dynamically populate keywords on a data generated page.

COLD FUSION

Round down to the nearest tenths place

There are no functions that allow this since all of them round up if a decimal is higher than .5. The following will "fake" a round down by dropping the last digit. You can round to the nearest hundredths by just changing the numberformat mask in the function. Explaination: Force a number to the hundredths, reverse it, remove the first (formerly last) digit, reverse it again, add the last zero back.
#numberformat(reverse(removechars(reverse(numberformat(thenumber, "999999.99")), 1, 1)), "999999.99")#

Always round up to the nearest tenths place

Same idea as the above example but this forces a round up when you have an integer with 3 floated numbers (thousanths place). Explaination: If the number has 2 decimal places, leave it. Otherwise add .004 if it wouldn't round up on it's own (.5 or greater).
<cfif not right(thetax, 3) contains "."> <Cfset curnum = right(thetax, "999.999")> <cfif curnum LT 5><cfset rounded = thetax + .004> </cfif> <cfelse> <cfset rounded = thetax> </cfif>

Simple pagination routine

<cfquery NAME="Getnames" DATASOURCE="icard_trans"> SELECT Name FROM Clients ORDER BY Name </cfquery> <cfparam name="url.startRow" default="1"> <cfparam name="url.rowsPerPage" default="20"> <cfif not isNumeric(url.startRow) or url.startRow lt 1 or url.startRow gt GetTrans.recordCount or round(url.startRow) neq url.startRow> <cfset url.startRow = 1> </cfif> <cfset totalRecords = Getnames.recordcount> <cfset totalPages = totalRecords / rowsPerPage> <cfset endRow = (startRow + rowsPerPage) - 1> <cfparam name="currentPage" default="1">  <cfif endRow GT totalRecords> <cfset endRow = totalRecords> </cfif>  <cfif (totalRecords MOD rowsPerPage) GT 0> <cfset totalPages = totalPages + 1> </cfif>  <cfif totalPages gte 2><strong>Pages: </strong> <cfloop from="1" to="#totalPages#" index="i"> <cfset startRow = ((i - 1) * rowsPerPage) + 1> <cfif currentPage neq i> <a href="search.cfm?startRow=#startRow#¤tPage=#i#"> <cfelse> <span class="pagination"> </cfif> #i# <cfif currentPage neq i> </a> <cfelse> </span> </cfif> </cfloop> </cfif> <cfoutput QUERY="Getnames" startrow="#url.startRow#" maxrows="#rowsPerPage#"> Your record output here </cfoutput>

Loop over all fields posted by a form

<cfloop list="#form.fieldnames#" index="i">
<cfset string="form.#i#">
#i#: #evaluate(string)#
</cfloop>

Count occurances of some text within a string

This example searches for the number of line breaks in a variable called address (to see how many lines there are in an address)
<cfset foundcount = (len(address) - len(replace(address,'<br>','','all'))) / len('<br>')>

Check to see if a user is already logged in on another computer or browser

This is probably the easiest way to do this. You just drop this on your login page. Change email to memberid or whatever you use as your identifier. Form/variable validation removed for this example.
<cfparam name="alreadylogged" default="0">
<cfset sessions = application.tracker.getSessionCollection(application.applicationName)>
<cfloop collection="#sessions#" item="sessionkey">
<cfloop collection="#sessions[sessionkey]#" item="keys">
<cfif keys EQ "email" and sessions[sessionkey][keys] EQ form.email>
<cfset alreadylogged = 1>
</cfif>
</cfloop>
</cfloop>
<cfif alreadylogged EQ 1>
<script language="JavaScript">alert('You are already logged in on another computer.'); history.back(-1);</script><cfabort>
</cfif>
<cfset session.email = trim(form.email)>

Alter columns without SQL Enterprise Manager or SQL Express.

This is a quick CF script to let you add columns to a table. Easy alteration will let you delete columns, etc.
<Cfif isdefined("form.tablename")>
<cfquery name="gettables" datasource="#datasrc#" username="#dbuser#" password="#dbpass#">
Alter Table #form.tablename#
Add #columnname# #definition#
<cfif len(defaultvalue) GT 0>Default #defaultvalue#</cfif>
</cfquery>
<cfquery name="getdata" datasource="#datasrc#" username="#dbuser#" password="#dbpass#">
select *
from #form.tablename#
</cfquery>
<cfset QueryName = "getdata">
<table border="1" cellpadding="2" cellspacing="0" width="100%">
<tr>
<cfoutput>
<cfloop list="#Evaluate("#QueryName#.ColumnList")#" index="Item">
<td>#Item#</td>
</cfloop>
</cfoutput>
</table>
</CFIF>
<cfquery name="gettables" datasource="#datasrc#" username="#dbuser#" password="#dbpass#">
select table_name
from information_schema.tables
</cfquery>
<form action="altertable.cfm" method="post">
Alter Table<br>
<select name="tablename">
<cfoutput query="gettables">
<option value="#table_name#">#table_name#</option>
</cfoutput>
</select>
ADD
<input type="text" name="columnname">
<select name="definition">
<option value="varchar(200)" SELECTED>varchar(200)</option>
<option value="int">int</option>
<option value="smalldatetime">smalldatetime</option>
<option value="decimal(9,2)">decimal(9,2)</option>
<option value="number(8)">number(8)</option>
</select>
Default <input name="defaultvalue" type="text">;<br><br>
<input type="submit" value="Alter Table">
</form>

Get just the name of the current script with no path names or info.

#right(cgi.script_name, find('/', reverse(cgi.script_name), 1) - 1)#

Get everything up to the first space.

This is good for extracting just a first name from a full name. It can be modified to search for everything after the first comma or whatever you want, just change the " " to ",".
#mid(productname, 1, find(' ', productname, 1))#

Simple (free) CF/JS CAPTCHA style form validation.

This is a session based CAPTCHA code to help reduce spam submitted by web forms. It writes a javascript ascii code which is difficult for robots to read and outputs it on the page then writes a CF session variable which gets passed to the form validator.
Form Page:
<Table>
<tr><Td class="bodycopy" align="right">To reduce spam, please type the following number:
<script type="text/javascript">
<cfset session.valid="">
<cfloop from="1" to="5" index="loopin">
<cfset thisdigit = randrange(0,9)>
<cfset thisascii = thisdigit + 48>
<cfset session.valid = session.valid & thisdigit>
<cfoutput>document.write(String.fromCharCode(#thisascii#));</cfoutput>
</cfloop>
</script>
</td><td>
<input class="bodycopy" type="text" name="SpamValidationCode" size="26">
</td></tr>
</Table>
Validation Page:
<cfif not form.SpamValidationCode EQ session.valid>
<script language="JavaScript">alert('Invalid spam validation. Please re-enter code.'); history.back(-1);</script><cfabort>
</cfif>

Add a space before all uppercase letters

#REReplace("DiversifiedProgrammingEnterprises", "([A-Z])", " \1", "ALL")#

Strip all special characters

rereplace("#yourtext#", "[[:punct:]|[:space:]]", "", "All")

Export all columns and records in all tables in a database to Excel

Useful for backing up a table direct to a user through an admin area. In case of major failure, its a bit of work to restore since there are no separate worksheets but better than a major loss and makes the client feel better about having control over the data.
<cfcontent type="application/msexcel">
<cfheader name="Content-Disposition" value="filename=tenantlist.xls">
<cfquery name="gettables" datasource="#dsrc#" username="#dbuser#" password="#dbpass#">
select table_name
from information_schema.tables
</cfquery>
<cfoutput query="gettables">
#table_name#<br>
<cfquery name="getdata" datasource="#dsrc#" username="#dbuser#" password="#dbpass#">
select *
from #table_Name#
</cfquery>
<cfset QueryName = "getdata">
<table border="1" cellpadding="3" cellspacing="0" width="100%">
<tr bgcolor="Gray">
<cfloop list="#Evaluate("#QueryName#.ColumnList")#" index="Item">
<td><font color="ffffff">
<b>#Item# </b>
</font>
</td>
</cfloop>
</tr>
<cfloop from="1" to="#Evaluate("#QueryName#.RecordCount")#" index="Vars">
<tr bgcolor="#IIf(Vars Mod 2, DE('ffffff'), DE('dddddd'))#">
<cfloop list="#Evaluate("#QueryName#.ColumnList")#" index="Item">
<td>
<cfif len(Evaluate("#QueryName#.#Item#[#Vars#]")) LT 1> <cfelse>#Evaluate("#QueryName#.#Item#[#Vars#]")#</cfif>
</td>
</cfloop>
</tr>
</cfloop>
</table><br>
</cfoutput>

Grab RSS news feeds from Yahoo News. This gets the top three.

<cfhttp url="http://news.search.yahoo.com/news/rss?ei=UTF-8&p=new+hampshire&c=&eo=UTF-8" method="get">
<cfset objRSS = xmlParse(cfhttp.filecontent)>
<cfset newslength = arrayLen(objRSS.rss.channel.item)>
<table width="800" align="center">
<cfloop index=i from=1 to=3>
<tr>
<cfoutput>
<td><a href="#objRSS.rss.channel.item[i].link.xmltext#">
#objRSS.rss.channel.item[i].title.xmltext#</a></td>
<td><a href="#objRSS.rss.channel.item[i].link.xmltext#">
#objRSS.rss.channel.item[i].description.xmltext#</a></td>
</cfoutput>
</tr>
</cfloop>
</table>

Docking loose tabs in Homesite 5

In the View menu, open Files 1 Primary. At the bottom of the tab (where the files appear, NOT where directories appear) right click and choose Move Files 1 To - Left side. Do the same thing for File 2 Secondary. For the snippets tab (and maybe others), redocking requires the following registry alteration. Close Homesite, click Start - Run. Type regedit. In the registry editor, navigate to HKEY_CURRENT_USER\Software\Macromedia\HomeSite5\Docking\frmDockSnippets. Make your registry look like the default reg settings, which are below:
"Visible"="1"
"AlwaysOnTop"="1"
"Position"="0,0,197,694"
"Floating"="0"
"DockHost"="AllaireTabDockForm0"
"PageIndex"="3"
"DockTab"="0"

Get dynamically generated form input boxes on the processing page

(Example: Input boxes in a loop called name1, job1, name2, job2, etc.)
#evaluate("form.name" & id)#
#evaluate("form.job" & id)#

Basic stored procedure code

Delete all session variables

Delete one particular session variables

Use CFFile to upload an attachment from a form posting to a different directory.

(remember to use enctype="multipart/form-data" instead of the default enctype="application/x-www-form-urlencoded" in your form!
<cfset curdirectory = #getdirectoryfrompath(expandpath("*.*"))#>

<cfset curdirectory = #replacenocase(curdirectory, "\admin\", "\images\", "All")#>
<CFIF form.theformfield eq "">
<script language="JavaScript">alert('You must upload a menu!'); history.back(-1);</script><cfabort>
<cfelse>
<CFFILE action="upload" filefield="theformfield" destination="#curdirectory#" nameconflict="MAKEUNIQUE">
<CFSET ServerFile1 = FILE.SERVERFILE>
</cfif>

Write a form posting to a text file in your current directory

Read from a text file

<cfset thisfile = getdirectoryfrompath(getcurrenttemplatepath()) & "specials.txt">
<cffile action="Read" file="#thisfile#" variable="specialtext">
<cfoutput>#specialtext#</cfoutput>

CFDUMP all variables

Get absolute directory path up one level

I wouldn't try to understand this one... your head would explode, but it works. We reverse the directory, strip to the second slash, reverse again.
<cfset uponedir = reverse(replace(reverse(mid(getdirectoryfrompath(getcurrenttemplatepath()), 1, len(getdirectoryfrompath(getcurrenttemplatepath())) - 1)), mid(reverse(mid(getdirectoryfrompath(getcurrenttemplatepath()), 1, len(getdirectoryfrompath(getcurrenttemplatepath())) - 1)), 1, find('\', reverse(mid(getdirectoryfrompath(getcurrenttemplatepath()), 1, len(getdirectoryfrompath(getcurrenttemplatepath())) - 1)), 1) - 1), "", "one"))>

A handy, short date loop. Remove the - before prevyears or leave to show years past

M <select name="birth_month">
<cfloop from="1" to="12" index="themonth">
<option value="#themonth#">#themonth#</option>
</cfloop>
</select>
D <select name="birth_day">
<cfloop from="1" to="31" index="theday">
<option value="#theday#">#theday#</option>
</cfloop>
</select>
Y <select name="birth_year">
<cfloop from="0" to="80" index="prevyears">
<cfset theyear = dateadd('yyyy', -prevyears, now())>
<option value="#dateformat(theyear, "yyyy")#">#dateformat(theyear, "yyyy")#</option>
</cfloop>
</select>

JAVASCRIPT

Disable the submit button to prevent double processing

onclick="this.disabled=1; this.form.submit(); this.value='Processing Order. Please wait...';"

Open the print dialog box

<a href="javascript:void(0);" onclick="window.print();">CLICK HERE TO PRINT THIS PAGE</a>

Open a confirm alert box. If they click No, it aborts the submission.

onclick="if (confirm ('Are you sure?')) {return true} else {return false}"

GOOGLE

Generate a result set compatible with Froogle

SELECT DISTINCT TOP 100 PERCENT
  (dbo.products.ProductName) AS title,
  cast(cast(dbo.products.ProductPrice AS Decimal(10,2)) as varchar) AS price,
  'http://www.yoursite.com/images/large/' + dbo.products.ProductImageLg AS image_link,
  cast('http://www.yoursite.com/productdetails.cfm?ID=' + cast(dbo.Products.productid AS Varchar) AS Varchar) AS link,
  cast(dbo.products.ProductID as varchar) AS id,
  ltrim(rtrim(left(dbo.products.ProductDescriptionShort, 255))) AS description,
  replace(ltrim(rtrim(dbo.products.ProductName)), ' ', ',') AS label
FROM dbo.products
WHERE ProductPrice > 0 AND len(ProductName) > 3

Standard META tags

htaccess redirect a directory link to a file (client advertises a directory, redirect to a file instead).

RewriteRule ^Classes/?$ availableclasses.cfm [L,NC]

Amazon CloudSearch working example in PHP

<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');
$feed_url = 'http://search-***YOUR AWS DOMAIN HERE***-***YOUR AWS DOMAIN ID HERE***.us-east-1.cloudsearch.amazonaws.com/2011-02-01/search?q=bain&return-fields=resourcename%2Clast_modified%2C&results-type=xml';
$xml = simplexml_load_file($feed_url);
foreach($xml->hits->hit as $hit){
echo $hit->d[1] . '<br>';
echo $hit->d[0] . '<br>';
echo '<br>';
}
?>

CATEGORIES