Create a DSN-less database connection using ColdFusion 8
For a long time, I had been searching around online to find a solution to switch to MySQL database for my ColdFusion school server from Microsoft Access database. Since I have no control over the ColdFusion Administration settings, I can’t change the DSN connection.
Recently I have been finishing my remaining undone projects. I use Railo open source CF server and MySQL under Ubuntu to do the assignments for the class. The SQL code I was writing is a bunch of aggregate functions:
SELECT Â b.BookId, Â Â Â b.BookTitle, Â s.SalePrice, Â Â Â Â Â Â COUNT(s.SalePrice) AS bcount, Â Â Â Â Â Â SUM(sd.TotalSale) AS ts, Â Â Â Â Â Â SUM(sd.Quantity) AS qt FROM tblBooks AS b, Â Â Â tblSales AS s, Â Â Â Â tblSalesDetail AS sd WHERE b.BookId=s.BookId AND s.SaleId=sd.SaleId AND s.SalePrice IS NOT NULL GROUP BY b.BookTitle ORDER BY #variables.ob# #URL.s#;
When I was testing these code on the localhost which is MySQL-powered, the page was rendered fine without any errors. However, once I uploaded to the school server which is powered by MS Access, the errors began to appear. Noticeably, I can’t use the name such as b.BookId, b.BookTitle, s.SalePrice without using aggregate functions. It struggled me a bit because the final is due tomorrow.
I then remembered my unsolved solution for connecting MySQL database dynamically through CF code because most of the DSN-less code online only works in CF 5. Once again I began to searching for the solution to release my struggle to the MS Access. Finally, I found a CF custom tag to do just that. It is called query, similar to cfquery. Since it’s a custom tag, the use of it is cf_query. This tag is primary used to connect to the MS SQL server by its author. Fortunately he has given a code snippet to dump out the available JDBC drivers on the server.
After I tested this code, I was surprised to see that my school server supports lots of databases that I don’t know about. I chose the MySQL one and copied its JDBC URL to the CF test page. I noticed that I was unable to connect to my own MySQL server on this web server since my hosting provider doesn’t allow remote connection to MySQL. Therefore I googled for “free mysql hosting” and found a website called FreeSQL.org that specifically for hosting free MySQL databases. I connect to this newly created database by using the cf_query code:
<cf_query jdbcURL="jdbc:mysql://www.freesql.org/ColdFusion" username="username" password="password" qName="qGetData">
Note that ColdFusion is the database name. Within the cf_query beginning and ending tag, I wrote the SQL statements to import the data since the phpMyAdmin link does not work on FreeSQL.org. After that, I replaced the above cf_query code with cfquery in the project that I struggled with.
You can download the above code here along with the custom tag and the original code from its author. The project page that I was struggled with can be found here.
Calculate the degrees from the given Data to draw PHP Graph
During the final exam for my PHP class today, I encountered a math problem that I learned long time ago. The problem is to draw a chart based on the data from the database in order to show the ratio between men and women on a pie chart. The number of men represented as blue and red represent as women. I thought that it was as simple as draw two arcs using imagefilledarc function. However, when I noticed that the data needs to be in degrees in order to use this function. I totally forget how to convert a number to a degree since I haven’t been practice math for two years. Thanks to Google, I learned how to do the conversion on Yahoo Answers. The formula that I came up with is the degree of each men and women equals the number of men or women divide by total number of men and women multiply by 360. Therefore I wrote the following code:
<?php
   $image = imagecreatetruecolor(300, 300);
   $blue = imagecolorallocate($image, 0, 0, 255);
   $red = imagecolorallocate($image, 255, 0, 0);
   $white = imagecolorallocate($image, 255, 255, 255);
   imagefill($image, 0, 0, $white);
$male = $_GET['m'];
   $female = $_GET['f'];
$total = $male + $female;
   $maleDegree = $male / $total * 360;
   $femaleDegree = $female / $total * 360;
imagefilledarc($image, 130, 130, 200, 200, $femaleDegree-270, $maleDegree-90, $blue, IMG_ARC_EDGED);
   imagefilledarc($image, 120, 130, 190, 190, $maleDegree-90, $femaleDegree-270, $red, IMG_ARC_EDGED);
imagepng($image);
   imagedestroy($image);
?>
The f and m variables are URL variables, which means I can pass the number of males and females inside the normal img tag:
<img src="3_image.php?m=<?php echo $numMale; ?>&f=<?php echo $numFemale; ?>" alt="Problem 3 - Graph" />
You can download the source code here to experiment with it. There is a MySQLÂ database file inside the downloaded archive, which it contains the needed MEMBERS table for this script.
Playing Around with PHP URL Extraction
I discovered today that there is a feature in PHP that can grab current URL through $_SERVER['REQUEST_URI'] and convert it into an array using explode function. I think this is extremely useful to me. Partly because I always wanted to have an URL that is similar to the URL for this blog. Another reason is that I don’t have to edit my sever configuration either directly or through PHP to achieve the same result. The method I use to create an URL that only contains the folder of the page, that is, the page is index.php, might be useful for small websites that have less than fifty web pages or possibly less. I didn’t test it on a large set of web pages because I just thought about using this type of URL format in the final project for my CA 282 class. It is a four-page project, therefore I cannot guarantee that it can work on a larger set of web pages. I personally think that it can become disorganized and confused once I have a little more web pages by using this method. Here is the code if you want to test it. Basically, it includes all the files inside the main index page based on the value at the last slash of URL, e.x. http://www.example.com/folder/item :
$currentURL = $_SERVER['REQUEST_URI'];
$URLpart = explode("/", $currentURL);
$endURL = $URLpart[sizeof($URLpart)-2];
switch ($endURL) {
case 'signup': // http://zebra0.com/Chen/final/account/signup/
include_once 'includes/signup.php';
break;
case 'login': // http://zebra0.com/Chen/final/account/login/
include_once 'includes/login.php';
break;
case 'account': // http://zebra0.com/Chen/final/account/
include_once 'includes/account.php';
break;
case 'signout': // http://zebra0.com/Chen/final/account/signout/
include_once 'includes/signout.php';
break;
default:
include_once 'includes/index.php';
}
And the index.php file inside each of the folder simply include the main index.php at the root of the project folder. Because of the nature of the included file, I used the dynamic path using PHP $_SERVER array to generate an absolute URL for the links and style sheets instead of the static ones.
Use Samba to Transfer Files between Fedora and Windows XP
Samba is a GNU/Linux network service that provides file sharing between GNU/Linux and Windows. One week ago, I switched my netbook from Ubuntu back to Windows XP thanks to the Google Earth plugin. I need to use Google Earth API for my ColdFusion class and it needs Google Earth plugin to test the API code. However, the Google Earth plugin doesn’t have a Linux version yet despite the popularity of Google Chrome and Google Earth on GNU/Linux. I initially thought that Google Earth plugin could work on virtualized Windows XP through VirtualBox. It failed to display the data due to the low performance of VirtualBox graphics card.
Anyway, to use Samba, you need to first install it by entering the command below to the terminal:
sudo yum install samba samba-client samba-common samba-swat
After the installation, I followed a helpful tutorial on linuxhomenetworking.com to complete the Samba setup. It provided me step-by-step instructions to setup the Samba server using SWAT web interface.
I use Asus Eee PC 1005HA, which means that it has Windows XP Home Edition. The instructions for this tutorial on how to connect Samba server with Windows XP Home confused me. I was never able to connect to the server based on it. Fortunately, I found a post on linuxforums.org that discusses about the same issue. According to the discussion, the firewall must be disabled to allow Samba to accept the connection. This inspired me to enable the Samba and Samba Client ports in Firewall setting without completely disable the Firewall. This prevents any unnecessary security issues. Windows XP successfully connected to the Samba server without any problem.
I have not yet tested with transferring files from Windows XP back to Fedora directly on Fedora, but I think it’s the same technique. Some of the Samba server security concerns would rise if Samba is used on a non-password protected network. According to the article on linuxhomenetworking.com, you need to setup a local HTTPS Certificate for the Samba server if you have any security concerns. I don’t need the certification because I only use the sever on the home network and the network is password-protected.
Google Maps API and ColdFusion
While I was experimenting with Google API for my CA 288 ColdFusion class final project, I discovered that the ColdFusion variables can be used within the script tag. So I tried it out. I put the cfoutput tag outside the script tag in order to use the ColdFusion variables inside the script tag. And then I added cfif statement inside script tag to set the default latitude and longitude values to New York if there is no URL variables. Here is the cfelse part of the code:
var co_lat=#URL.a#;
var co_long=#URL.o#;
initialLocation = new google.maps.LatLng(co_lat,co_long);
var marker = new google.maps.Marker({
position: initialLocation,
map: map
});
contentString = “<br />#URL.d#:” + “<br />” + “Latitude: ” + co_lat + “<br />” + “Longitude: ” + co_long;
map.setCenter(initialLocation);
infowindow.setContent(contentString);
infowindow.setPosition(initialLocation);
infowindow.open(map);
I also used ColdFusion variables to convert them into the JavaScript variables as you can see on the first two lines of code. I also added a marker to the map to show the accurate location for the data provided. This is best used for creating maps from a list of places. For usage example, go to http://160.253.0.40/Students/Students/0111/browserGeo.cfm?d=Addo%20Entrance&a=-33.44431&o=25.73872. To download the source code, visit http://code.google.com/p/ca288-final-spring-2010-mc/downloads/list
PHP Form Validation Tip
While doing the assignment for my CA 282 PHP class today, I came up with a trick to minimize the amount of code that I need to write for the validation rule of each form element. This trick especially best used for validating forms with lots of form fields. The following is the PHP code:
if (isset($_GET['submitted'])) {
$field=”username,password,pwconfirm,fname,lname,address,city,state,zip,email”;
$fieldName=”User Name,Password,Confirm Password,First Name,Last Name,Home Address,City,State,Zip,Email”;
$field = split(“,”,$field);
$fieldNm=split(“,”,$fieldName);
for ($i=0;$i<10;$i++) {
if (empty($_POST[$field[$i]])) {
$errorMsg .= $fieldNm[$i].” is required.<br />”;
$success .= “False”;
}
else {
$success .= “True”;
}
}
if (!strstr($success,”False”)) {
$errorMsg=”The form successfully submitted.”;
}
}
else {
$errorMsg = “”;
}
echo “<div id=”noVal”>”.$errorMsg.”</div>”;
As you can see from the code above, I use split function to convert the combined form field names and the actual name for each form field to arrays. I then used a for loop to loop between form fields to validate the form. This is a simple form. It only checks all the form fields that left blank. You can use continue command inside the for loop for certain form fields that don’t need to be validated. You can also use email validation and password=password_confirm validation inside the for loop. Also note that the noVal id at the end of above code is just used to display the error massages to red color and bold.
Successfully using ColdFusion array for the First Time Without Knowing Anything
I just improved my ColdFusion class website. I used more fileExists functions to automate the detection of the project files in the URL variables. Now I don’t have to worry about adding the projects to the cfswitch statement anymore. Here is the code snippet for one of my cfcase statements:
<cfcase value=”4″>
<cfif fileExists(filePath[4])>
<cfinclude template=”../projects/project4.cfm” />
<cfelse>
<div id=”noVal”>This project is not available yet!</div>
<cfinclude template=”index.cfm” />
</cfif>
</cfcase>
Noted that I used an array in the fileExists function. Here is the cfloop statement for the filePath array:
<cfloop from=”1″ to=”10″ index=”i”>
<cfset filePath[#i#] = ExpandPath(“projects/project#i#.cfm”)>
</cfloop>
As you can see, I used an array inside cfset statement to store 10 ExpandPath functions. This is my first time to use the array in a ColdFusion variable. In fact, I didn’t know anything about how to use ColdFusion array. I just applied the array based on the other programming languages that I have already known. According to course syllabus, we should learn ColdFusion array at later chapter.
At first, I used filePath#i# inside the loop to declare a regular ColdFusion variable. ColdFusion outputted an error stated that “ColdFusion was looking at the following text: #” . Then I tried the traditional method of adding an array by surrounding the loop variable with a pair of brackets. I was very surprised that it actually worked.
I applied the same technique to the title of each webpage so that I no longer forget about adding project title to the title.cfm when completed a project. Here is the code snippet for one of the cfcase statements:
<cfcase value=”7″><cfif fileExists(filePath[7])>#titleName[7]#<cfelse>#defaultTitle#</cfif></cfcase>
The defaultTitle variable is the default title on the site homepage.
The following are the additional updates for the site:
- Projects are now re-organized
- Project 1 has become homepage
- Project 2 has changed to project 1
- New project 2, Advanced SQL, was completed and added to the homepage
Discovered a Bug in the Web Application
I encountered a small bug in my Computer Concepts web app while I was doing the assignment using the app. It seems to always output a forward slash in front of each single quote on the print layout table. Please refer to the following screenshot if you don’t know what I’m talking about.
Forward slash bug. Click the image to enlarge
Thankfully, I found this post in the Dev Shed forum that provide a native PHP function to remove the forward slashes. I applied the function stripslashes($string) to the output value and the forward slashes are now gone.
Here is the updated source code for anyone who downloaded my last source code:
http://www.robbychen.com/cs110/worksheet_sourcecode.zip
If you find more problems while using the app, please leave a comment. I will fix it as soon as possible. Thanks.
Computer Concepts Assignment Aid Web App released
I finally completed the project and will be released under CS 110 homepage over robbychen.com soon. The following are some features of the web application (NOTE: This application is primarily made to help me to finish the assignments easier. However, anyone can use it if it helps to make life easier. This web app doesn’t contain any database-related function nor server/client log script, so you can safe to use it):
- Each text field is auto expandable thanks to the autoResize jQuery plug-in.
Each text field will automatically expand once text in the text field reached the bottom of the text field.
- The ability to add more rows using PHP.
The default number of rows is 20. Once you reached the last row at the bottom of the page, the Add a New Row button will appear. NOTE: this button will disappear if I don’t enter anything to any three of the text fields, it assumes that I had completed entering the data. After clicking the button, the page will refresh and it will scroll to the bottom of the page to letting me to enter data in the new row. I use the scrollTo jQuery plug-in to achieve this effect.
- The option to print out the completed sheet after clicking the Submit button.
Once I clicked the Submit button, two buttons will appear on both top and bottom of the page, print button and edit button. It allows me to print out the completed sheet in order to hand in to the professor. The buttons on the page will not print out with the assignment sheet. I specified the print style sheet to hide the buttons while printing.
- The option to edit the fields after it was submitted by clicking the Edit button.
The Edit button is simply using history.back() Javascript function to go back one page. I have not think of a way to achieve this without using database or session variables yet since my goal is to not leave any trace of the application on the computer and on the server.
Here is the side-to-side comparison between the original (non-digitized) version of the assignment sheet and digitized one:
Comparison between original and digitized version. Click the image to enlarge
The following is the URL for this web app if you want to go to it directly:
http://www.robbychen.com/cs110/worksheet/
Changing Plan
I just found out this morning school is open today despite that today is President’s Day in the US. I have to postpone my project to Wednesday since I don’t have access to a scanner to scan the original assignment sheet. Right now I’m completing assignment for Computer Concepts class. I know the web app for helping me to complete this assignment is not finished yet. Since this assignment is due tomorrow, I have to complete this by hand. Besides, if I finished building the web app, I don’t have access to a printer to print out the assignment, and I have classes until ten o’clock on both evenings today and tomorrow.
So I decided to delay my web app “release” to Wednesday. Today I’ll complete my Computer Concepts assignment. The completion of the web app will be tomorrow. On Wednesday, I will scan the original assignment sheet to my laptop, compare between the web app and the original, and upload the project. As I wrote on previous post, it will be uploaded under the CS 110 homepage on robbychen.com website.