CMXtraneous: SQL Server

Right on the edge of useful

3 posts

Track browser resizing in your database using AJAX -- part 2

Posted Thursday, November 17, 2005 9:11:56 PM by Tom Muck

Tom Muck

Latest Posts by Tom Muck

Part 1 of this post showed the server-side code for a browser resize tracker. This part will show the client-side script. This can go on any type of page -- php, coldfusion, html, etc. The scripts consist of several functions:

  • getBrowserSize() -- called in the onload and onresize event to capture the browser size and pass to the server-side page
  • getSize() -- gets the size of the browser window
  • passFields() -- takes an array of fields (fieldname, value, fieldname, value, etc) and a URL and passes the fields to the URL as querystring variables
  • resetSizeTimer() -- creates a timer so that when the browser is resized, only one event is recorded (browser resizing typically fires the onResize event numerous times in succession.)

In addition, we set a global variable to act as a flag for the resize timer. The code is pretty straightforward, and can be placed in the head of any file:

<script>
var size_timer = false;

// Subroutine to get the size of the window
function getSize() {
 var myWidth = 0, myHeight = 0;
 if(typeof(window.innerWidth) == 'number') {
  //Non-IE
  myWidth = window.innerWidth;
  myHeight = window.innerHeight;
 }else if(document.documentElement &&
  (document.documentElement.clientWidth || document.documentElement.clientHeight)) {
  //IE 6+ in 'standards compliant mode'
  myWidth = document.documentElement.clientWidth;
  myHeight = document.documentElement.clientHeight;
 } else if(document.body && (document.body.clientWidth || document.body.clientHeight)) {
  //IE 4 compatible
  myWidth = document.body.clientWidth;
  myHeight = document.body.clientHeight;
 }
 return [myWidth, myHeight];
}

// Pass fields to server given a URL and fields in name/value pairs
function passFields(url,args) {
 url += "?";
 for(var i=0; i<args.length; i=i+2) {
  url += args[i] + "=" + args[i+1] + "&";
 }
 //Set up the AJAX communication
 if (window.XMLHttpRequest) {
  req = new XMLHttpRequest();
 } else if (window.ActiveXObject) {
  req = new ActiveXObject("Microsoft.XMLHTTP");
 }
 try {
  // Pass the URL to the server
  req.open("GET", url, true);
  req.send(null);
 }catch(e) {
 //nothing;
 }
}
function resetSizeTimer() {
 size_timer = false;
}

// Get the size and pass to the server
function getBrowserSize() {
 if(size_timer)return;
 size_timer = true;
 self.setTimeout('resetSizeTimer()',1000);
 var theArray = getSize();
 var url = "getBrowserSize.php";
 var args = new Array();
 args.push("width");
 args.push(theArray[0]);
 args.push("height");
 args.push(theArray[1]);
 args.push("screenwidth");
 args.push(screen.width);
 args.push("screenheight");
 args.push(screen.height);
 args.push("pagename");
 args.push(window.location);
 passFields(url, args);
}

</script>

All you need to do is to add the getBrowserSize() function to the onload and onresize events of the <body> tag:

<body onload="getBrowserSize();" onresize="getBrowserSize();">

Now, when you browse the page, the server records the browser size upon load and upon resize. Typical information would look like this:

Width Height Screen
width		 Screen
height		 IP Page name
85678812801024192.168.1.2http://mysite.com/index.cfm
76662512801024 192.168.1.2http://mysite.com/index.cfm
94875112801024 192.168.1.2http://mysite.com/index.cfm
102575712801024192.168.1.2http://mysite.com/index.cfm

The technique is handy and can be used for any other situation where you need to pass client-side information to the server.

Cross posted at Tom-Muck.com

Category tags: ColdFusion, Dreamweaver, JavaScript, SQL Server

Posted by Tom Muck
Add comment | View comments (3) | Permalink | Trackbacks (0) | Digg This

Track browser resizing in your database using AJAX -- part 1

Posted Thursday, November 17, 2005 9:10:15 PM by Tom Muck

Tom Muck

Latest Posts by Tom Muck

It's always interesting to find out about viewing habits of web visitors. One of the things that is hard to determine when building a web page is how big to make your pages. Do you assume the user has 1280x1024? Do you assume 800x600? Do you assume that the user will have a fully maximized browser? One way to find out this information is to read the properties via JavaScript and store them. AJAX gives a web developer a valuble tool that allows the server to communicate with the browser in real time based on client-side events (such as resizing). I wrote a little script that I can insert on a page to track the resizing made by a user in relation to his screen resolution. After getting this information from a variety of users, I can run queries on the data and get some insight into browsing habits and adjust my page designs accordingly (or have them adjusted by a designer, in my case.) The code will be presented for ColdFusion and PHP.

First, I create a table in my database to store the information. The following is for SQL Server:

CREATE TABLE BrowserSize (
 browsersize_id int IDENTITY (1, 1) NOT NULL ,
 browsersize_width int NULL ,
 browsersize_height int NULL ,
 browsersize_screenwidth int NULL ,
 browsersize_screenheight int NULL ,
 IP varchar (50) NULL ,
 pagename varchar (255) NULL
)

The following is the equivalent for MySQL:


CREATE TABLE BrowserSize (
 browsersize_id int AUTO_INCREMENT PRIMARY KEY NOT NULL ,
 browsersize_width int NULL ,
 browsersize_height int NULL ,
 browsersize_screenwidth int NULL ,
 browsersize_screenheight int NULL ,
 IP varchar (50) NULL ,
 pagename varchar (255) NULL
);

You could also add a timestamp field, if you want to track times.

Next, I create a server-side page to grab the information and pass it to the database. The information will be passed in the URL. The code is self-explanatory. Basically, we pass width, height, screenwidth, screenheight, and page location in the URL, and insert it into our database table, along with the IP address of the user. The following is for ColdFusion:

<cfparam name="url.width" default=0>
<cfparam name="url.height" default=0>
<cfparam name="url.screenwidth" default=0>
<cfparam name="url.screenheight" default=0>
<cfparam name="url.pagename" default="">
<cfset url.width = val(url.width)>
<cfset url.height = val(url.height)>
<cfset url.screenwidth = val(url.screenwidth)>
<cfset url.screenheight = val(url.screenheight)>
<cfset ip = cgi.REMOTE_ADDR>

<cftry>
<cfquery datasource="yourdsn">
INSERT INTO browserSize
(browsersize_width
, browsersize_height
, browsersize_screenwidth
, browsersize_screenheight
, IP
, pagename)
VALUES
(#url.width#
,#url.height#
,#url.screenwidth#
,#url.screenheight#
,'#ip#'
,'#url.pagename#')
</cfquery>
<cfcatch>
<!--- do nothing --->
</cfcatch>
</cftry>

The following is for PHP:

<?php
$_GET["width"] = isset($_GET["width"]) ? intval($_GET["width"]) : 0;
$_GET["height"] = isset($_GET["height"]) ? intval($_GET["height"]) : 0;
$_GET["screenwidth"] = isset($_GET["screenwidth"]) ? intval($_GET["screenwidth"]) : 0;
$_GET["screenheight"] = isset($_GET["screenheight"]) ? intval($_GET["screenheight"]) : 0;
$pagename = isset($_GET['pagename']) ? $_GET['pagename'] : "";
$ip = isset($_SERVER['REMOTE_ADDR']) ? $_SERVER['REMOTE_ADDR'] : "";

$conn = mysql_connect("localhost", "username", "password");
$query_rs = sprintf("INSERT INTO browserSize
(browsersize_width
, browsersize_height
, browsersize_screenwidth
, browsersize_screenheight
, IP
, pagename)
VALUES (%s, %s, %s, %s, '%s', '%s')"
,$_GET["width"]
,$_GET["height"]
,$_GET["screenwidth"]
,$_GET["screenheight"]
,$ip
,$pagename);
mysql_select_db("cwtest");
$rs = mysql_query($query_rs);
?>

Both of the pages can be run in the browser to test (saved as getBrowserSize.cfm and getBrowserSize.php respectively). If the values of 0 are inserted in the database, everything is working. I'll post the client-side AJAX code in Part 2.

Category tags: ColdFusion, Dreamweaver, JavaScript, SQL Server

Posted by Tom Muck
Add comment | View comments (1) | Permalink | Trackbacks (0) | Digg This

SQL function to chop a field by number of words

Posted Monday, June 20, 2005 6:37:59 PM by Tom Muck

Tom Muck

Latest Posts by Tom Muck

I was prompted by a question on the CMX forums today to finally break down and write a function to return a number of words from a database field, which I've been meaning to do for a long time. There are script examples on the web for ASP and ColdFusion code to truncate a specific database field to a certain number of words (split at the word rather than mid-word, as the LEFT function does), but there is no easy way to do it in SQL, unless you use a loop. The following function will truncate any field to a specific number of words. Pass in the string you want to parse, and the number of words to return.

CREATE FUNCTION fnGetNumberOfWords (
  @stringToSplit varchar(8000),
  @numberOfWords int
)

RETURNS varchar(8000) AS

BEGIN

DECLARE @currentword varchar(8000)
DECLARE @returnstring varchar(8000)
DECLARE @wordcount int
SET @wordcount = 0
SET @returnstring = ''
SET @currentword = ''
SET @stringToSplit = ltrim(rtrim(@stringToSplit))
Declare @index int

WHILE @wordcount < @numberOfWords AND len(@stringToSplit) > 0
  BEGIN
    Select @index = CHARINDEX(' ', @stringToSplit)
    if @index = 0
      BEGIN
        SELECT @currentword = ltrim(rtrim(@stringToSplit))
        SELECT @wordcount = @numberOfWords
      END
    else
      BEGIN
        IF (len(@stringToSplit) - @index > 0) BEGIN
        SELECT @currentword = ltrim(rtrim(LEFT(@stringToSplit, @index-1)))--the new shortened string
        SELECT @stringToSplit = RIGHT(@stringToSplit,LEN(@stringToSplit) - @index) -- the rest
      END
    END
  SELECT @returnstring = @returnstring + ' ' + @currentword
  SELECT @wordcount = @wordcount + 1
END

SET @returnstring = LTRIM(@returnstring)
RETURN @returnstring

END

Call it like this:

SELECT dbo.fnGetNumberOfWords(MyField, 10) FROM mytable

(returns first 10 words from MyField)

The advantage to doing it in the database rather than on the web page, is that you are only returning a small portion of the field to the web page, rather than the entire field. This can speed up the query. A few preliminary tests show that the smaller number of words you return, the quicker the query will execute. In other words, if your query returns a field that can contain up to 8000 characters in it (like a blog entry, for example) and you only need the first 50 words for a summary, the query to return the 50 words will be faster than a query that returns the whole field. Also, your scripted page will execute faster because it is simply displaying the field and not performing any further logic, looping, or parsing on the field.

I talked about user-defined SQL functions in one of my articles at Community MX as well:

Using CSV Strings in SQL Server: Part 2

Note that the function does not work on text or ntext data types. I hope you find it useful.

Category tags: ColdFusion, Community MX, Dreamweaver, SQL Server

Posted by Tom Muck
Add comment | View comments (7) | Permalink | Trackbacks (0) | Digg This

3 posts