HOME - ABOUT

Archive for the ‘Programming’ Category

SQL - Simplifying User-Defined Functions

Sunday, August 10th, 2008

I recently learned of the concept of Spartan Programming and since then, I’ve been trying to find ways to apply some of the techniques.

As a talentless hack, some of the ideas are beyond me, but one of them — reducing the number of variables — I am capable of. I was working on a typical user-defined SQL function that was something like this…

CREATE FUNCTION do_something_function(@id AS INT)

RETURNS DATETIME

AS
BEGIN
   DECLARE @date DATETIME

   SELECT @date = date_field
     FROM some_table
    WHERE id = @id

   RETURN @date
END

I got me wondering if I could get rid of the return variable in simple functions that return a scalar variable like this. Well, it turns out you can. After working on several stored procedures that were written by someone obsessed with sub-queries, I thought I might be able to use one of his techniques in a way that was actually useful. This is what I did…

CREATE FUNCTION do_something_function(@id AS INT)

RETURNS DATETIME

AS
BEGIN
   RETURN (
      SELECT date_field
        FROM some_table
       WHERE id = @id
   )
END

I crossed my fingers and ran it and to my surprise it worked. That was a satisfying five minutes.

 Posted in Database, Programming, SQL | No Comments »

Export HTML Data to MS Excel: a PHP/JavaScript Solution

Tuesday, October 30th, 2007

Google up “export HTML to Excel” and most of your matches will provide methods using ASP/VBScript by either declaring the Response.ContentType = “application/vnd.ms-excel” (exhibit A) or building a new ActiveXObject and fuddling around with that (exhibit B).

Well, neither of these helped me at all since I was using PHP for this particular project, so I had to come up with my own solution.

First things first — PHP has the ability to generate an Excel file “on the fly” simply by setting the Content-Type in your page header.
Put the following lines of code (inside your PHP tags, of course) at the top of an HTML document and force the browser to open the page in Excel:

<?php
	header('Content-Type: application/vnd.ms-excel'); //IE and Opera
	header('Content-Type: application/x-msexcel'); // Other browsers
	header('Expires: 0');
	header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
?>

This is all well and good if you simply want to direct a specific page to always open in Excel. I, however, had some additional requirements. Because the page I was working on works a little bit like a query portal, it would have been extremely obnoxious to re-code every single query page to simultaneously write a PHP/Excel file at the same time the data was being displayed. Obviously, this would also significantly increase the load on the server and slow down browsing. This was not something I was willing to sacrifice when the PHP/Excel page might only be viewed 10 or 5 or 1 percent of the time a query was run! So, I determined that I wanted to export:

  • only upon request by the user (or, on demand — like when an “Export” button is clicked.)
  • only part of a page to Excel (in this case, the contents of a DIV, which contained table(s) of data.)

Using some JQuery AJAX functions and a couple PHP scripts as middlemen, here’s how I accomplished this.

Initially, I created a simple PHP file called export.php that contains HTML headers, a CSS link and an HTML body with one PHP statement included. Here is the full text of export.php:

<html>
	<head>
		<link href='./css/styles.css' rel='stylesheet' type='text/css'></link>
	</head>
	<body>
	<?php
		echo $_POST['text'];
	?>
	</body>
</html>

What we’ll do with this, is send the entire contents of the desired container (in my case, the DIV) as a POST. (I just use the name “text” in this example.) This script will generate a temporary HTML source for everything I want to send to Excel. However, we still need to add the Content-Type declaration — we just can’t do it here or else the PHP POST won’t execute because the file would’ve been already send to Excel! (Annoying, yes.)

So, we have to create a second script that basically contains everything in this one, plus the PHP headers. I made another file called export2.php which creates yet a third file (excel.php) and writes the PHP headers, the contents of export.php (including POST data) and does any text manipulation that may be needed or desired. export2.php looks something like this:

<?php
	$writeData = "<php\n";
	$writeData .= "header('Content-Type: application/vnd.ms-excel');\n";
	$writeData .= "header('Content-Type: application/x-msexcel');\n";
	$writeData .= "header('Expires: 0');\n";
	$writeData .= "header('Cache-Control: must-revalidate, post-check=0, pre-check=0');\n";
	$writeData .= "?>\n";

// the following is useful for removing tags you don't want to export (INPUT, IMG, etc.)
	$allowedTags = '<html><head><body><link><div><table><th><tr><td><thead><tbody><br><b><i><span>';
	$_POST['content'] = strip_tags($_POST['content'], $allowedTags);

// the following may be necessary if there are quotes in your POST data
	$writeData .= stripslashes(stripslashes($_POST['content']));
	$exportFile = "./excel.php"; 

        if (!$handle = fopen($exportFile, 'w+')) {
           echo "Cannot open file ($exportFile)";
	     exit;
	}
	if (is_writable($exportFile)) {
	    if (fwrite($handle, $writeData) === FALSE) {
               echo "Cannot write to file $exportFile";
         	   exit;
	    }
          fclose($handle);
	} else {
	    echo "The file $exportFile is not writable";
	}
?>

Now that our data handling scripts are in place, we need to tell our initial page how to process them. First, I created a button to trigger the event which initiates the AJAX requests.

<input id="expexcel" type="button" value="Export to Excel"></input>

Next, add the jQuery to handle the click event of this button, the AJAX POST requests and the redirect to the newly created excel.php file.

$(document).ready(function() {
	$("#expexcel").click(function() {
		$.post("./tmp/export.php", {text: $("#container").html()}, function(data) {
			$.post("./tmp/export2.php", {content: data}, function() {
				window.location = "./tmp/excel.php";
			});
		});
	});
});

This small bit of code produces two AJAX requests and one redirect every time the Export to Excel button is clicked.
Line 03 makes the first POST to export.php. It sends the entire HTML content of the “#container” DIV as the POST data. The result of this POST is returned in jQuery as “data”, which is used in line 04. This second POST (to export2.php) sends the result of the first POST as “content”. Once export2.php processes this content and writes it to the file excel.php, the AJAX request is successful and the page is redirected to excel.php in line 05.

 Posted in PHP, Programming | 5 Comments »

jQuery, AJAX and Redundant Event Handlers

Monday, September 10th, 2007

It didn’t take me long to realize that jQuery’s AJAX functions don’t trigger the $(document).ready() event. But then, it wouldn’t really make any sense if they did. Naturally, this event fires just once and naturally, AJAX leaves it alone. Therefore, it becomes necessary to supply event handlers to the AJAX callback functions, even if it means duplicating handlers in the DOM.

In my scenario, I had a styled table using the tablesorter plugin (id #tablesort).

Initial code sample:

$(document).ready(function() {
 $("#tablesort").tablesorter({
 	widgets:['zebra'],
 	sortInitialOrder: 'asc'
      });
 $("#tablesort tr").hover(function(){
 	$(this).addClass("over");
 		 },function(){
 	$(this).removeClass("over");
 });
 $("#tablesort tbody").hover(function() {
 	$(this).css({overflow-y: "auto"});
 }, function() {
 	$(this).css({overflow-y: "hidden"});
 });
});

With the inclusion of the AJAX function, each record in the data table would contain a link to generate further detail in a new table, for which I also wanted to apply the tablesorter styles (another id #tablesort.) This looked something like:

 	$("input.more").click(function() {
		$("#ajax_div").html("Retrieving data...");
 		 $.post("./include/ajax_table.php", {sql: $(this).val() },  function(data){
     				$("#ajax_div").html(data);
   		});
   	});

While this is enough to generate and display a second #tablesort, the new table lacks the same styles that jQuery applied to the first #tablesort on $(document).ready(). So, my first instinct was to simply copy & paste the event handlers into my jQuery callback function, like so:

 		$("input.more").click(function() {
 			$("#ajax_div").html("Retrieving data...");
 		 	$.post("./include/ajax_table.php", {sql: $(this).val() },  function(data){
     					$("#ajax_div").html(data);
 					$("#ajax_div tablesort").tablesorter({
 						widgets:['zebra'],
 						sortInitialOrder: 'asc'
 					     });
 					$("#ajax_div tablesort tr").hover(function(){
 						$(this).addClass("over");
 							 },function(){
 						$(this).removeClass("over");
 					});
 					$("#ajax_div tablesort tbody").hover(function() {
 						$(this).css({overflow-y: "auto"});
 					}, function() {
 						$(this).css({overflow-y: "hidden"});
 					});
			 });
		});

Now, I had what I wanted — two #tablesort tables with identical styles. However, I also had a bunch of redundant and messy code at the top of my page. To fix this, I simply stored a function in an external file to handle the styling of all my #tablesort tables. Utilizing the find() function, this was even easier to do:

function tablesortStyles(jthis) {
	jthis.find("#tablesort").tablesorter({
		widgets:['zebra'],
		sortInitialOrder: 'asc'
	     });
	jthis.find("#tablesort tr").hover(function(){
		$(this).addClass("over");
			 },function(){
		$(this).removeClass("over");
	});
	jthis.find("#tablesort tbody").hover(function() {
		$(this).css({overflow-y: "auto"});
	}, function() {
		$(this).css({overflow-y: "hidden"});
	});

	return true;
}

Now, I only needed to use two lines in my source file to style my tables:

$(document).ready(function() {
		tablesortStyles($(document));

		$("input.more").click(function() {
                   $("#ajax_div").html("Retrieving data...");
			 $.post("./include/ajax_table.php", {sql: $(this).val() },  function(data){
     					$("#ajax_div").html(data);
					tablesortStyles($("#ajax_div"));
   			 });
   		}); 

	});

 Posted in Programming | No Comments »

ASP.Net - Error: Newline in constant

Friday, September 7th, 2007

Here’s a tip from a few years ago. I was working in C# on an ASP.Net application and needed to run some Javascript code from the server side. That’s when I ran into the error.

I was trying to run something similar to the following code…
(more…)

 Posted in ASP.Net, Programming | 1 Comment »

MS SQL Server: Find a Value In Any Field In Any Table

Monday, August 20th, 2007

As part of a project at work, I found myself needing to update every field in an entire database that contained a certain value. If I had needed to do this in a database I had created or a database that didn’t have hundreds of tables, I might have done it manually like I have done it in the past. But that wasn’t the case, so I needed to find a better solution.
(more…)

 Posted in Database, Programming | 5 Comments »


← Back to Home

© wkm. Powered by WordPress using the wkm Theme.