I solve problems. What kinds of problems? All kinds. Got a problem? Yo, I'll solve it. (Check out the hook while my DJ revolves it. Ice, ice baby.)
My current gig is Web and social media editor for the Kennebec Journal and Morning Sentinel newspapers in Augusta and Waterville, Maine.
I write computer code. ASP.NET, PHP, Windows Forms, MySQL, Transact-SQL, JavaScript, jQuery, VBA, VBScript, APIs, XML, SOAP, AJAX, Web Services, etc., etc.
Education: Cony High School, Augusta, ME, Class of 1985; University of Maine, Orono, 1986-1991 (BS, Broadcast Journalism, 1996).
Previous Employment: Reporter, Star Herald, Presque Isle, ME, 1991-1992; Reporter, Kennebec Journal, Augusta, ME, 1992-1996; Editorial page editor, Morning Sentinel, Waterville, ME, 1996-1998; Editorial page editor, KJ & MS, 1998-1999; columnist, KJ & MS, 1999-2004.
Miscellaneous: Hail from & currently live in Augusta, ME. Previously lived in Presque Isle, Orono and Portland, ME.
I am in charge of social media and the Web sites for two of Maine's seven daily newspapers. In addition to creating and managing content, I also am the point person for interaction with social media networks, including Twitter and Facebook.
I also have an extensive background in both server- and client-side programming, including PHP, ASP.NET and most major database platforms.
Rescue-ME provides custom Web and database programming, as well as technical assistance to small- and medium-sized companies. It specializes in subcontracting technical projects that lie outside the scope of IT departments, using current Web and database technologies to solve business needs. Additionally, Rescue-ME provides b2b marketing assistance, help desk support for both Windows and MacOS computers, and additional technical services as requested by clients. "Questions answered, problems solved!"
Owner
Self-taught in several Web / programming / database technologies, listed above.
Extensive experience with project management, design and scoping.
Extensive experience working as, and with, subcontractors and multiple stakeholders.
Wide-ranging skills set appropriate to answering divergent business needs.
Publishers of the Kennebec Journal and Morning Sentinel newspapers.
Editor and reporter
Worked crime, government and business reporting beats and served as editorial page editor.
Won multiple, state and regional, writing and editing awards.
Last weekend I was in Cambridge for the fourth annual New England GiveCamp, which brings together tech and design people with nonprofits in need of their help.
Again this year, I was team lead for The Esplanade Association, a nonprofit which cares and advocates for the eponymous park along the Charles River on the Boston side.
Last year, I lead the team that rebuilt the association’s website on a WordPress backend, tossing in some scheduling and form feedback bells and whistles.
This year, TEA was looking for an interactive map of the park. They had an embedded Google Map, but it was, in a word (and a pun), pedestrian: stock icons, little detail in generic infoWindows, etc. Expedient, but hardly exciting.
I took the project because 1. The Esplanade Association is a dream client (see last year’s recap for why) and 2. I’ve done a pile of work in the Google Maps API.
Once again, I was blessed with a team of very skilled, very dedicated and very amenable people to do most of the work: Nate Bates and Will Klein, both experienced JavaScript and Web UX men; and Bryan Phillips, a graphic designer and GiveCamp fixture. My biggest problem last week was coming up with a polite way to to ask them to wait until we were all together at GiveCamp before programming the entire solution.
That enthusiasm and ownership served us well, even allowing us (well, not “us”; Nate) to tackle fixing the website’s CSS to be responsive to screen size, in effect making it mobile.
Bryan Phillips works with Ashton Porter, center, and Meghan Buco on Friday, sketching ideas for their interactive map’s icons.
On Friday, we met Ashton Porter and Meghan Buco, TEA’s liaisons for the weekend, and settled upon a basic set of responsibilities.
Nate and Will would take care of finding a mapping provider they liked and put together the JavaScript that would power the front end map. Bryan would work with Ashton and Meghan on getting the icons drawn. I would work on the backend, which leveraged a WordPress custom post type and taxonomy with some metadata.
Nate and Will soon settled on using Leaflet, a JavaScript provider that gives access to several different mapping providers, because they wanted the broadest base of maps from which to choose — namely, as Nate put it, whichever one made the park look nicest. Turns out that was MapQuest.
The basic design for getting points on the map was straightforward: We would use JSON. It would have two primary objects, one for categories — that is, the types of icons we would plot, such as docks, footbridges, monuments, bathrooms, etc. — and another containing the points themselves, keyed to the relevant categories / icons.
Nate and Will worked until about 2 a.m. Saturday, but had a functional map at that time. And after working with Ashton and Meghan, Bryan had the iconography pretty much ready to go first thing Saturday morning.
Will Klein, standing, and Nate Bates discuss on Friday the approach they’ll take to make the interactive park map.
I spent most of the evening working on GiveCamp stuff in general; I manage the website, Facebook and Twitter accounts, so there was a bunch of stuff I had to get out to the campers. But I managed to hit the sack at about midnight, which is early by GiveCamp standards.
On Saturday morning at 9 a.m. we had a functional map and all the art. That was both a complete relief and a bit shocking, as I have been to three GiveCamps previously and not a one of them was anywhere near its goal at 9 p.m. on Saturday, nonetheless 9 a.m.
In fact, I was really the only holdup on our project, as I had not begun work on the WordPress backend.
The concept of the backend was simple: I would create a custom post type in WordPress, with a custom taxonomy, that directly related to the map. The taxonomy would be the map categories, and the custom posts each a point on the map. Ashton and Meghan, who are used to using WordPress, could then add, edit and delete points on the map just as they would any other content.
All my custom posts really needed was a few custom fields: One to record latitude, one to record longitude, and two to record external hyperlink text and links, which they wanted for certain points on the map.
Piece of cake; a few meta boxes would give me the custom fields. I would use the Google Maps API to put a map into the post editor, along with a draggable marker that they could place wherever they wanted the related icon to appear, which in turn would populate the lat / lng textboxes with the correct coordinates.
(It doesn’t matter which mapping API gives coordinates or renders them; after all, a point on the Earth is a fact, and so long as your tiles are to scale, you can mix and match APIs as you please, which is the whole idea behind Leaflet.)
I just needed to hook the CRUD events for the taxonomy and posts, write new JSON any time one of those events fired, and our map would be up-to-date at all times.
It took me about eight hours, largely because WordPress changed how it hooks custom taxonomy CRUD and it’s poorly documented. But by dinner time, we were pretty much done, save some tweaking. Which, again, is amazing.
Nate pretty much spent the entire day fixing TEA’s website skin, and pretty much had it ready to go by 9 p.m. So we were wildly ahead of the game, and while it felt great, I didn’t quite know what to make of it, because normally at that time things have come completely apart.
Interestingly, being ahead of the game was par for the course at this year’s GiveCamp; a few teams were a distance from their goals on Saturday night, but most were “green,” in the vernacular of coordinators Rachel Nichols and Kelley Muir.
We did hit a few bumps on Sunday. It turns out we did not fully test the JSON I was sending out via my plugin, and it was causing parsing errors on the live version of the map, a fact we didn’t discover until about 90 minutes before we were supposed to present our work.
I’m glad to say Will was very understanding of my short temper over that ultimately meaningless bump.
As that was getting fixed, the map also shifted on us, putting playgrounds in the middle of streets and parking in the center of retention ponds. Needless to say, that wasn’t good, but it was a quick fix once we had the JSON straightened out. We managed to have everything nailed down with about 5 minutes to go before we presented.
That felt more like GiveCamp.
Presenting the map on Sunday at New England GiveCamp. I’m at the lectern; on my left, from left to right, are Ashton Porter, Bryan Phillips, Will Klein and Nate Bates.
TEA is quite pleased with its new map and mobile skin.
You can see both on their website. Just shrink your browser window down to 480 pixels or less to see the mobile skin, and click on “Park Map,” under “About The Park,” to see that.
I cannot say enough good things about the team that made it happen. I wound up helping several other teams and generally being distracted the entire weekend, even in the midst of finally getting around to doing my part of the project, and they pretty much managed themselves.
As I’ve said before, when I am answerable for an end result, I like to provide a responsibility, an expectation and a deadline, and let the person doing the work do the work. Nate, Will and Bryan all performed under that freedom remarkably well.
This year, I stayed at Microsoft NERD, the host of GiveCamp, and I would offer to anyone considering doing that two valuable lessons:
All links in this post on delicious: https://delicious.com/dougvdotcom/more-than-one-way-to-skin-a-map-new-england-givecamp-2013-recap
Tags: GiveCamp, Google, graphic design, Jim O'Neil, metadata, Microsoft, mobileI’ll be attending New England GiveCamp 2013 this weekend.
GiveCamp is a way for technical people and designers to donate their time to worthy nonprofits. Organized by Jim O’Neil and Kelley Muir and hosted at Microsoft’s New England Research and Development center on the Massachusetts Institute of Technology campus, New England GiveCamp is in its fourth year.
This year I’ll again be working with The Esplanade Association. Last year, I was the leader of the team that revamped their website. It’s a real pleasure to work with them again.
Over the weekend, we’ll be working on an interactive map, probably built on the Google Maps API, of the Esplanade’s many amenities and features. The fellows assigned to this task are already full of ideas and getting to work, so once again, I’ve been very fortunate to have highly motivated, very capable team members assigned to our task.
It’s probably going to be another hectic, exciting weekend. Can’t wait!
All links in this post on delicious: https://delicious.com/dougvdotcom/new-england-givecamp-2013-this-weekend
Tags: GiveCamp, Google, Jim O'Neil, MicrosoftNot to kiss my own ass, but Microsoft’s official advice on avoiding SQL injection attacks sounds awfully familiar to readers of this blog:
Sanitize (validate) all inputs: “This helps to ensure that the input is free from characters that cause SQL injection attacks.” It also allows you to fix the form and data type of the user input, which pretty much renders basic script kiddie attacks useless.
Parameters, not strings, as query variables: “Creating dynamic queries using string concatenation potentially allows an attacker to execute an arbitrary query through the application.”
In other words, it’s harder to break this:
@person VARCHAR(20); SELECT * FROM table WHERE person = @person;
than it is to break this:
SELECT * FROM table WHERE person = 'some user string';
Stored procedures, not free-form queries: “Stored procedures by themselves do not remove SQL injection vulnerabilities. They only raise the bar on the attacker by hiding much of the underlying database schema.” That is, the attacker can’t easily find out what columns are in a table, or what type of data is in those columns, if you use a stored procedure.
Minimal permissions: “In general, database applications should be using a low-privileged account that has the minimum permissions required to execute the statements submitted to SQL Server.” As in, create a user in your SQL database whose only permission set is to execute your Web-based stored procedures, and connect to the database server as that user.
Those are the basics. And if you don’t understand how to do them, I’ll be putting together a blog series on how to convert your old string-queried Web applications into one secured with stored procedures and proper permissions.
Additionally, Microsoft suggests:
Escape your strings: Microsoft notes that this is important “when SQL identifiers (table names, column names, and so on) might be constructed dynamically.” I’d say, if your solution requires you to dynamically determine table or column names, your solution is doing it wrong; try again. That doesn’t mean you shouldn’t escape strings by default; it means, a dumb or wrong approach isn’t the only reason to do so.
Protect against buffer overruns: “Buffer lengths need to be handled properly to prevent SQL injections through truncation.” In Web development, if you fix the length and type of data, you’ll generally avoid buffer overruns. If you’re taking in bitstreams to dump into BLOBs, that’s another matter altogether; but if you’re skilled enough to do that, you should be skilled enough to armor against overruns.
All links in this post on delicious: http://delicious.com/dougvdotcom/microsofts_-_advice_-_on_-_avoiding_-_sql_-_injection_-_attacks
Tags: data types, elegance, hacking, MicrosoftSome time ago, I received the following e-mail:
Hello
I am interested in getting into computers and designing software and websites. You said in a yahoo post that you did not get a degree but learned everything yourself. How did you do this? Where did you get your information from?
Thanks
My reply:
I started out by playing with Web pages. Then, as people asked me to make things for them, I searched on the Web for examples of how to do it, or read self-help books (think “For Dummies,” “Sams Teach Yourself” and Wrox softcovers) to teach myself how to do things.
If you want to make a career of Web development, my recommendation would be to do so in a more orderly manner than “learn as you go along.”
I would say this: Designing a Web site, and programming it, are two very different skill sets. You can be good at both, and great at one, but it is very difficult to be great at both. Design is left-brain, programming is right-brain.
That doesn’t mean you can’t do both; it means that you should expect to specialize either in design or programming. You may be that rare person who can master both, but expect that one or the other will be your actual focus.
I wrote a post a while back on what I think makes sense for a beginning programmer / designer to self-teach: https://www.dougv.com/2008/10/02/recommended-steps-in-a-web-development-career/
There are a number of fast-track Web development cheats you can undertake these days, that weren’t available when I started out.
The smartest one is to learn WordPress, which is far and away the most popular content management system in the world, meaning there’s plenty of work in it. Start by learning how to make WordPress themes. A great book to learn the basics is WordPress Web Design for Dummies, http://www.dummies.com/how-to/content/wordpress-web-design-for-dummies.html
What you really need is opportunities to practice. To that end, you should look for ways to make very simple Web sites; that is, sites that are just basic HTML and CSS, so you can master the design and basics first.
The obvious source of these opportunities are friends, charities and small nonprofits. Think wedding sites, youth sports teams, animal shelters, food banks, etc.
You can find nonprofit organizations a number of ways. Search Google for your local United Way and Chamber of Commerce, and ask them for the names of some charities; or search for your state name and “nonprofits.”
Don’t just focus on charities that don’t have a Web site; consider those that do, but have ugly / broken ones. Keep in mind, however, that many nonprofits have Web sites made or maintained by volunteers or donors, who may resent you coming in, or may lack the skills needed to care for a well-made Web site. (For example, a lot of nonprofits will make their Web sites in Microsoft Publisher or Microsoft Word, then simply save that as HTML and upload the site.) Or, if you make a Web site for a charity, they will need you to do everything from getting it online to keeping it online.
Once you have made a few nice Web sites that you feel confident in showing off, start applying for jobs. Don’t try to go out on your own at first, unless you have steady employment elsewhere; when possible, learn your trade on someone else’s dime.
Finally, if you decide to go the traditional, learn through school route, make sure that any art / design school you attend is accredited by a NASAD organization. If you are going the programming route, make sure the school or college is accredited by an ABET organization. Going to an accredited school means you can get federal financial aid, your credits will be largely transferable to other accredited schools, and your degree will likely be recognized if you ever decide to get a higher degree.
Hope this helps.
My correspondent followed up with this:
Thanks so much for the information. I’m interested in programming more than websites. Eventually I would like to get into apps and software. Would the best way to do this be take free classes in programming?
Thanks again
My response to that message:
If you intend to work for someone else, or you are fairly young and just starting out, going to an ABET-accredited college is the wisest path. While that costs money, you should find that as long as your grades are good, you will find a good-paying job immediately after graduation, in any economic situation. As in, go to a state school, borrow the $40,000 or so you’ll need to get your degree, and accept that the first couple years out will be lean while you pay those loans back.
If you intend to self-teach, a few seminar / professional classes wouldn’t hurt. If you want to work for someone else, focus on certifications, such as MCPD http://www.microsoft.com/learning/en/us/certification/mcpd.aspx or Zend certification http://www.zend.com/services/certification/php-5-certification/. You don’t necessarily need the certifications, just the classes they teach for certification. (Although, as a practical matter, a certification and a good portfolio is every bit as good as a bachelor’s degree.) Those classes are going to focus on the practicality of getting something done, rather than the theory.
In other words, if you go to programming college, they will teach you the theory of how to program, and give you some hands-on experience to back that theory up. If you go the certification route, they will show you how to program, and explain why it works as they go along.
The former is better for long-term professional growth; the latter is better for getting a job right now.
The bullet points of my advice for getting started in the programming world:
All links in this post on delicious: http://delicious.com/dougvdotcom/basic-advice-for-learning-computer-programming
Tags: Google, hacking, Microsoft, productivityThis question came via email earlier today:
Your Article: An Image-Based ‘Checkbox’
http://www.dougv.com/demo/js_imgswap_checked/example2.htmlAwesome article and very informative. I was trying to get it to work for what I am trying to accomplish but I am having a hell of a time getting it to work I was hoping you can help me.
I have a selection form based on Check Boxes:
item 1 (box)
item 2 (box)
item 3 (box)
item 4 (box)I have images that correspond to the check boxes, item1.png, item2.png, item3.png, item4.png that are located elsewhere on the page. I just need the image to change based on whether the check box is checked or not. It doesn’t matter if it is faded or an entirely new image, so long as the user notices which box they checked. My problem is there are a lot of little images so in your example you had the two set variables, on or off. How do I introduce multiple images???
I have early tests and the problems are that the default unchecked images do not show up until after the check box is checked…all of them and two whenever I try to add multiple images and or check boxes it breaks somewhere, perhaps the array or I am approaching this at the wrong end. I need your help.
To rephrase the question:
Easily enough accomplished.
For this exercise, I will use four pictures of Miss USA 2012 contestants, and ask users to pick which are their favorites. If a contestant is selected, her picture will be at full opacity; if the contestant is not selected, her image will be at reduced opacity (i.e., faded).
I’ll power this with jQuery, since it has good cross-browser support not only for handling DOM events like clicks, but also because it has good cross-browser support for “fading” DOM objects, including pictures, in and out.
For those unfamiliar with jQuery, it’s a JavaScript library. What that means is, it’s already solved a number of common JavaScript needs into handy functions, which have been tested to work in most Web browsers.
If I have a very small, very straightforward JavaScript need — say, echo out an alert if something is clicked — I don’t bother using jQuery. But the more complicated the task I need to accomplish, and the more reliant I am on ensuring that the client properly process that task, the more likely I am to use jQuery.
I used to advise people to upload a copy of jQuery to your Web site and linking to it from there. Of late, however, I pretty much just link to a version of jQuery hosted by the Google Libraries API.
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
Next, we need to prepare our page to handle the effects we will create via jQuery / JavaScript.
The most important part of that process is ensuring we have some way to relate each checkbox to its related image. There are a few ways we can do that:
For this example, I am going to use a hybrid of options 2 and 3.
Specifically, I am going to assign each image the ID of “img_{value},” where {value} is the value of its associated checkbox. For example, if I have a checkbox with the value of “miss_maine_usa”, its associated image will have the id “img_miss_maine_usa.”
Like this:
<img id="img_miss_maine_usa" name="img_miss_maine_usa" src="miss_maine_usa.jpg" class="contestant_pic" alt="Rani Williamson, Miss Maine USA" title="Rani Williamson, Miss Maine USA" /> <input type="checkbox" id="cb_miss_maine_usa" name="favorite_contestant" value="miss_maine_usa" />
We’ll lay out the images and form separately, since that’s what the question implies is the case. However, you could put these images inside the form, if you want. It’s not necessary, but it can be done that way.
<div id="container-photos" class="container"> <div id="column-1" class="column"> <img id="img_miss_alabama_usa" name="img_miss_alabama_usa" src="miss_alabama_usa.jpg" class="contestant_pic" alt="Katherine Webb, Miss Alabama USA" title="Katherine Webb, Miss Alabama USA" /> <a href="http://www.missuniverse.com/missusa/members/profile/651291/year:2012/" title="Katherine Webb, Miss Alabama USA" target="profile"> <p class="caption">Katherine Webb, Miss Alabama USA</p> </a> </div> <div id="column-2" class="column"> <img id="img_miss_maine_usa" name="img_miss_maine_usa" src="miss_maine_usa.jpg" class="contestant_pic" alt="Rani Williamson, Miss Maine USA" title="Rani Williamson, Miss Maine USA" /> <a href="http://www.missuniverse.com/missusa/members/profile/651310/year:2012/" target="profile"> <p class="caption">Rani Williamson, Miss Maine USA</p> </a> </div> <div id="column-3" class="column"> <img id="img_miss_northcarolina_usa" name="img_miss_northcarolina_usa" src="miss_northcarolina_usa.jpg" class="contestant_pic" alt="Sydney Perry, Miss North Carolina USA" title="Sydney Perry, Miss North Carolina USA" /> <a href="http://www.missuniverse.com/missusa/members/profile/651324/year:2012/" target="profile"> <p class="caption">Sydney Perry, Miss North Carolina USA</p> </a> </div> <div id="column-4" class="column"> <img id="img_miss_southdakota_usa" name="img_miss_southdakota_usa" src="miss_southdakota_usa.jpg" class="contestant_pic" alt="Taylor Neisen, Miss South Dakota USA" title="Taylor Neisen, Miss South Dakota USA" /> <a href="http://www.missuniverse.com/missusa/members/profile/651332/year:2012/" target="profile"> <p class="caption">Taylor Neisen, Miss South Dakota USA</p> </a> </div> <div id="column-5" class="column unrelated"> <img id="img_bill_murray" name="img_bill_murray" src="bill_murray.jpg" class="another_class_altogether" alt="Bill Murray" title="Bill Murray" /> <a href="http://www.imdb.com/name/nm0000195/" target="profile"> <p class="caption">Bill Murray</p> </a> </div> </div><!-- /photos --> <br class="clearfix" /> <div id="container-form" class="container"> <form id="myform" name="myform" method="get"> <h4>Who are your favorite Miss USA 2012 contestants?</h4> <label><input type="checkbox" id="cb_miss_alabama_usa" name="favorite_contestant" value="miss_alabama_usa" />Katherine Webb, Miss Alabama USA</label> <br /> <label><input type="checkbox" id="cb_miss_maine_usa" name="favorite_contestant" value="miss_maine_usa" checked="checked" />Rani Williamson, Miss Maine USA</label> <br /> <label><input type="checkbox" id="cb_miss_northcarolina_usa" name="favorite_contestant" value="miss_northcarolina_usa" />Sydney Perry, Miss North Carolina USA</label> <br /> <label><input type="checkbox" id="cb_miss_southdakota_usa" name="favorite_contestant" value="miss_southdakota_usa" />Taylor Neisen, Miss South Dakota USA</label> <br /> <label><input type="checkbox" id="unrelated_checkbox" name="unrelated_checkbox" value="foobar" />This checkbox is not related to any of the pictures above.</label> <br /> <input type="submit" id="submit" name="submit" value="Submit" /> </form> </div><!-- /form -->
Note that in this form, all the checkboxes that have a related image are named “favorite_contestant.” It may be that you need to relate checkboxes and images on a very specific basis. That’s outside the scope of this article; for most intents and purposes, this approach will suffice.
To make this work, we need to incorporate two steps:
Why do we set the initial fade state of images via JavaScript? Simple: Because not all visitors will have JavaScript enabled, or use a browser that understands our code. It’s better to assume this doesn’t work for any given visitor, than to assume it does.
And remember: JavaScript and DOM elements can be altered by a user; always sanitize form variables before processing them.
First, we need to set some global variables: constants for full opacity, faded opacity, the rate at which we want the fade to take place and a simple string variable we’ll use to construct the image ID we want, based on the checked value of a box.
Also, note that we will wrap all of this script in $(document).ready(), which is how jQuery wires up DOM event handlers on page load.
//set global constants & variable var full_opacity = 1; var faded_opacity = 0.3; var fade_speed = 'fast'; var objid;
Next, we want to set the initial state for each checkbox.
I am selecting all the checkboxes with the name “favorite_contestant.”
If all the checkboxes on my page were related to an image, I could have used the selector ‘:checkbox’ to get them all; if the only input elements I had on the page were checkboxes, I could have used ‘input’. But in this case, I have an additional, unrelated checkbox on the page, so I need to be specific about which checkboxes have associated images.
$('input[name="favorite_contestant"]').each(function() {
//we need the image associated with this checkbox
//i am assuming the associated image tag has an ID of "img_" and whatever the value of the associated checkbox is
//e.g., img_miss_alabama_usa is the id for the image associated with the checkbox that has the checked value miss_alabama_usa
objid = "#img_" + $(this).val();
if($(this).prop('checked')) {
//full opacity for prechecked images
$(objid).css('opacity', full_opacity);
}
else {
//partial opacity for unchecked images
$(objid).css('opacity', faded_opacity);
}
});
With the initial fade state of each image set, we can now add the handler that will fade images up and down, depending on whether its related checkbox is clicked.
$('input[name="favorite_contestant"]').change(function() {
//same method as above for associating a checkbox and its image
objid = "#img_" + $(this).val();
if($(this).prop('checked')) {
//full opacity for image if associated checkbox is checked
$(objid).fadeTo(fade_speed, full_opacity);
}
else {
//partial opacity for image if associated checkbox is unchecked
$(objid).fadeTo(fade_speed, faded_opacity);
}
});
(That is, “change” is more accurate about what is happening than “click” is. Although both the change and click events fire when either a checkbox or its label is clicked, I am using change so that my code is self-documenting.)
And with that, we have a fully functional solution. You can see it in action here: http://www.dougv.com/demo/ajax_checkbox_img_switch/
No code download this time; just save the HTML, since it all runs client-side.
All links in this post on delicious: http://delicious.com/stacks/view/DdfDOo
Tags: checkboxes, DOM images, global constants, GoogleReceived in my email today:
Hi
say your blog and thought you might help.
strsql = “SELECT StaffID, DesignationID, StaffName, Password, ShopID from staffT where StaffName =” & UserName.Text & ” AND Password =” & Password.Text & “”
from the string, the username.text and password.text are form controls. what is happening is there are passing null values regardless of what you input in the text boxes resulting in a system error.
“System Error Object reference not set to an instance of an object”
Am using Mysql as the database.
I’m always glad to answer such questions, especially when the questioner is flirting with disaster, as much as this questioner is.
A trained eye can immediately spot the problem with the SQL statement above, aside from the problem of NULL values tossing errors. Namely, it’s wide-open to SQL injection. (And an even keener eye will note that the values for user name and password aren’t delimited with single-quotes.)
So here’s my reply email to the questioner:
Your SQL statement has three problems.
Assuming you are using ASP.NET, and that your user names and passwords are only alphanumeric, the direct fix to your problem is this:
Dim strUser As String
If String.IsNullOrEmpty(UserName.Text) Then
strUser = String.Empty
Else
strUser = UserName.Text.Replace("'", "''")
End If
Dim strPass As String
If String.IsNullOrEmpty(Password.Text) Then
strPass = String.Empty
Else
strPass = Password.Text.Replace("'", "''")
End If
strsql = "SELECT StaffID, DesignationID, StaffName, Password, ShopID from staffT where StaffName = '" & strUser & "' AND Password = '" & strPass & "'"
That should get you going, but you should employ the following best practices fixes:
Hope this helps.
The guidance I gave the emailer, instructing him to escape single quotes, is a bare-minimum escaping sequence. As a practical matter, he should sanitize his inputs against additional MySQL sequences, such as double-dashes and semicolons, as well as reserved SQL statement words, such as DROP, ALTER, DELETE, etc. There’s an HTTPModule example over at the ASP.NET forums that does this automatically for an application.
Using parameterized queries / stored procedures to combat SQL injection is a primary recommendation from both Microsoft and MySQL (pdf).
In addition to securing data type, query parameters limit the ability of an attacker to inject SQL by fixing the form of the query implicitly. In other words, it’s harder for him to mangle a parameter than it is to mangle a string.
All links in this post on delicious: http://delicious.com/stacks/view/Kp3pOU
Tags: coding standards, data types, elegance, hacking, regular expression, Windows ServerLast weekend I was in Cambridge, Mass. for New England GiveCamp 2012, the third of annual meet-ups that match technical and design people with nonprofit organizations that need their help.
The Charles River Esplanade is on the left. Hatch Memorial Shell and Teddy Ebersol's Red Sox Fields are in the foreground.
My cause was The Esplanade Association, an organization that cares for the Charles River Esplanade Park.
The Charles River Esplanade Park is the Boston-side green space along the river, from the Museum of Science to the Boston University Bridge. While it’s owned and managed by the state of Massachusetts, TEA (which has to be the coolest acronym possible for a Boston-based group) exists to organize people to help protect and care for the park.
Much of their work involves organizing volunteers to clean up the park several times each year. TEA also holds a number of programs in the park — yoga, Zumba, dances and the like — and runs several fund raising projects.
They came to GiveCamp, initially, looking for a way to better coordinate singing up groups and individuals for cleanup days.
Currently, the way Jessica B. Pederson, TEA’s project manager, handles such reservations is by posting a schedule of cleanup days on her Web site, and asking people to email her if they are interested in participating. Then, via email exchanges back and forth, the details get worked out.
Even though the majority of these events are done through groups — so Jessica is in contact with a few group representatives, rather than scores of individuals — this proves an understandably arduous process that consumes a great deal of Jessica’s time, and it was something that she knew just screamed out to be automated.
You can’t swing a cat without hitting sample booking / ticketing system code on the Web, and there’s little practical difference between booking, say, a hotel room or buying a ticket to a play, and reserving a volunteer opportunity slot. Just provide the date, the number and types of tickets available, and a way for people to provide their contact information, and you’re done.
So I started by looking for ASP.NET-based ticketing solutions on the Web, since most of the Web developers who participate in GiveCamp are .NET folks. (Actually, I was side-hoping I could request Saurabh Moondhra and William Wade, the two ASP.NET developers I worked with during GiveCamp 2011, whom I knew would shine on such a project.)
And I was also thinking, in the back of my mind, it would be nice to address TEA’s Web site.
Don’t get me wrong; the site they had worked fine and contains a lot of good information presented in a sensible way. But it’s also stuck in 2005, and could use a cosmetic and functional overhaul.
And coincidentally, I am working on a WordPress site for a client right now that included the need for a basic calendaring and ticketing system. I had identified a plugin, Events Manager, for them that wasn’t quite right for that project; but it almost perfectly fit Jessica’s needs, especially if she was willing to upgrade to the “pro” version.
And then, by coincidence again, Kelley Muir — who coordinates the projects at GiveCamp — emailed me to say that she had asked this year’s nonprofits to identify possible second projects, and TEA said they’d like to have a content management system and general site redesign, too.
Jackpot! No need to reinvent the wheel; no need to even resize the wheel. We’re gonna put them in the Honda of the Web — good ol’ WordPress — and slap some 20-inch rims (Events Manager) on it.
I talked with Jessica and Christopher Timmel, TEA’s communications director, on the phone on Wednesday, to go over their needs and expectations, and knew instantly that I had lucked out a second time.
I’m a big fan of green space. You can’t live in Augusta, Maine and not like trees and rivers, after all.
But as a simple matter of public policy, I don’t think parks and open spaces get enough credit or resources, in spite of being a huge factor in quality of life. Understandably, because trees and grass pretty much grow on their own, it’s easy to put government dollars and effort elsewhere.
So when I see a group like TEA, I’m already a huge fan of their work.
Even better was that Jessica and Chris had a very clear picture of what they actually wanted. Even more rarely, what they wanted was actually what they needed. (It’s strange, but often, clients pitch requirements that don’t resolve their problems, or do so in ways that only make the problems worse or create new problems. Not so with Chris and Jessica.)
Plus, they’re personable, sincere and nice people. No coddling, wrangling or placating was in my weekend forecast. Not that it’s been a problem for me in past GiveCamps; just that any time you can get a client that’s positive about its problems, you need to celebrate.
So I asked Kelley to assign a designer and a couple PHP people (since WordPress skinning requires a little knowledge of PHP, and I figured we might need to adjust some of the Events Manager code via overrides) to the project.
That’s how Christina Yung and Jason Dufour were assigned to the project, and they were my third stroke of luck.
Jessica Pederson, program director for The Esplanade Association, and Christina Yung, graphic designer, running through the old esplanadeassociation.org Web site on Friday night of GiveCamp 2012.
An even better perk is that Jason and Christina work together at their day jobs, which means there would be no need to worry about chemistry or communication. And the weekend bore out that both were take-initiative-and-ownership types, which fits my management style perfectly: I like to provide an area of responsibility and a goal, then let people figure out, and do, what needs to get done to achieve it.
So two times I was a GiveCamp project lead, and two times Kelley assigned my team members who were skilled, friendly people who knew what to do and got done what needed to get done.
Even when you have a superior platform (WordPress); the right tools inside that platform (Events Manager); a client with full buy-in, all collateral organized and ready, and authority to make decisions on the fly (Chris and Jessica); and a team that’s got the skills and drive to get the job done (Jason and Christina), putting a Web site together over a weekend is a frenzy.
There were a few points where I thought we wouldn’t make it.
In addition to the usual bumps in any project road — misunderstandings, mistakes, unforeseen complications and unintended consequences are the hallmarks of every Web project — there were many, many steps in getting the content right.
Because of the kinds of information TEA needs to communicate, this WordPress site wasn’t header-footer-sidebar-index-css-and-we’re-done.
They needed fairly complicated page flow, and the calendar itself mutated a couple times as it became clearer what events would need a registration component, and which events they simply wanted to let people know about. And their information required a few different page templates and a couple extra sidebars, to ensure it showed up properly.
Additionally, I had the objective to use as few plugins (aside from Events Manager) and as little code as possible (especially in the templates themselves), because I wanted to ensure whomever helps them with technical needs going forward can make heads and tails out of what we did. So that meant every error we made in terms of approach, taxonomy and content was magnified, since we couldn’t simply find a plugin or write some code to fix the issue.
In the end, we would up with only eight plugins, three of which are standard in all my WordPress installs:
Jessica and Chris are happy with their new site. They’re going to take a week to finish migrating content, come up with a list of questions and tweaks, then present it to their board of directors for feedback.
But you don’t have to wait. Check out the before and after shots:
As I quipped at the GiveCamp closing presentations, it looks good because Christina made it that way; it works because Jason made it that way; and it’s not done because I made it that way.
The site needs to go live still; a fair amount of content remains to be migrated in, there are a few cosmetic tweaks needed, and we didn’t run a complete walkthrough / debugging of the site because all the elements are not in place.
But that is not to detract at all from what was accomplished. While I intend to help TEA go live with this site, really it can be finished by anyone with basic HTML / CSS knowledge. Anything short of complete disapproval of the skin won’t be difficult to accommodate, and that skin is not going to be rejected. Not by sane people, anyway.
So I hit for the cycle: Great project; great client; great team; great results. GiveCamp 2012 was a complete success.
I’d be remiss if I didn’t note, once again, what a great experience GiveCamp is on the whole.
There’s just something reaffirming and enjoyable in being around capable, motivated, positive people who are in it for the love. Sincerely, you meet the nicest and best people at GiveCamp.
You get fed well. (Thank you Green Mountain Coffee Roasters, Naked Pizza, b.good catering, Whole Foods, Cape Cod Chips, Rip van Wafels and Cakes By Kelli.) You get all kinds of great swag (that’s generally true of any event hosted by Jim O’Neil, New England GiveCamp’s host and driving force, but is certainly true of GiveCamp). And you get to be in Cambridge, which is an amazing place.
I should also mention, after the disappointment of the Royal Sonesta hotel last year, that the best deal in town for a stay near MIT has to be the Hampton Inn on Monsignor O’Brien Highway. Even though I figure I paid about $30 per hour to sleep there (11 hours of sleep @ $340 for two nights), it’s very clean, the bed is awesome, the staff is friendly and efficient, it’s 100 percent geared toward serving business travelers (free WiFi, lightning-fast checkin and automatic checkout), the hot breakfast is actually quite palatable and it’s only a one-mile walk up Third Street from NERD, where GiveCamp is hosted.
So, everybody who’s taken part or helped make it happen, thanks. You done good. Real good. And if you couldn’t make it this year, GiveCamp 2013 is April 26-28; mark your calendars now!
All links in this post on delicious: http://delicious.com/dougvdotcom/tea-time-new-england-givecamp-2012-recap
Tags: coding standards, crowdsourcing, ethics, GiveCamp, graphic design, Jim O'Neil, marketing, MicrosoftI’ve reopened comments on all posts, regardless of post date.
Two years ago, I closed comments on posts older than 60 days because generally speaking, they only attract spam.
That’s still true, for the most part. But between WordPress Hashcash (an anti-spam plugin I can’t flog hard enough; seriously, get it) and Akismet, there’s no real additional work load; it’s not half the hassle of dealing with the script kiddies trying to log in to the admin section.
(BTW, I deal with hack attempts via Bluetrait Event Viewer, which lets me know about (among many other things) failed logins; and WordPress SEO by Yoast, which lets me easily edit my .htaccess file and put the banhammer down on offending IP addresses.)
My most popular posts are well over 60 days old, and the majority of questions I get are about those posts. Also, I have a number of very old (3+ years) that need cleaning up; so it would be helpful to enable comments on them, for people to tell me when things are broken / don’t look right on the screen. Thus, it makes sense to open comments back up on all posts.
FYI, all first-time comments are moderated. In other words, if you’re making your first comment here, I will need to approve it before it appears for others; but once that happens, all your subsequent comments will be published immediately. (Also FYI: Your email address determines if you are a first-time comment. So if you’ve commented here before but used a different email address, you’ll once again be moderated. All subsequent comments using the same email address will go live instantly.)
Finally, if you aren’t trolling or a moron, I’ll approve / retain your comment. I may edit a comment, sometimes to clean up its formatting, possibly to clarify something it says, maybe to remove something offensive from an otherwise worthwhile comment.
I reserve the right to act capriciously. You’ve been warned.
All links in this post on delicious: http://delicious.com/dougvdotcom/comments-reopened-on-all-posts
Tags: blogging, SEOSome time ago I wrote a PHP-MySQL based solution to getting all ZIP Codes in a given radius from a known point / ZIP Code. I’ve long intended to do an ASP.NET version of that post, and here it is.
I won’t bother revisiting the mechanics in detail. I do urge you to read the post on the PHP version of this solution, at least to familiarize yourself with the mechanics of what I am doing and the compromises I’ve taken in coming up with this solution.
I will note the following for the “get to the point” types:
Before we get into the nitty-gritty, we need to create two helper functions: One that converts degrees into radians, and another that reverses the process. (In trigonometry, angles are calculated in radians; a radian is the ratio between the length of an arc and its radius. If an arc is as long as its radius, that’s 1c).
First, to convert from degrees to radians:
Function Deg2Rad(ByVal sglDegrees As Single) As Single Return sglDegrees * (Math.PI / 180.0) End Function
And to go from radians to degrees:
Function Rad2Deg(ByVal sglRadians As Single) As Single Return sglRadians * (180.0 / Math.PI) End Function
Now that we can move back and forth between degrees and radians, we can calculate the latitude and longitude of points at a known distance and bearing from an initial geocoordinate.
Given a known starting point, expressed as lat1 and lon1; a known distance from that point, d; a known bearing from that point, b; and a known radius of the sphere over which we are travelling, r; we calculate a new geocoordinate, expressed as lat2 and lon2, thus:
lat2 = asin(sin(lat1) * cos(d/r) + cos(lat1) * sin(d/r) * cos(b)) lon2 = lon1 + atan2(sin(b) * sin(d/r) * cos(lat1), cos(d/r) - sin(lat1)*sin(lat2))
So here’s a function that accepts, as arguments, a starting latitude, radius, bearing and distance; and returns the latitude at the provided bearing and distance from that starting latitude.
Function CalculateLatitudeCoordinate(ByVal sglLat1 As Single, ByVal intRadius As Integer, ByVal intBearing As Integer, ByVal intDistance As Integer) As Single Return Math.Asin(Math.Sin(sglLat1) * Math.Cos(intDistance / intRadius) + Math.Cos(sglLat1) * Math.Sin(intDistance / intRadius) * Math.Cos(intBearing)) End Function
And here’s a function that accepts, as arguments, a starting latitude and longitude, and ending latitude, radius, bearing and distance; and returns a longitude at the provided bearing and distance from the starting coordinates.
Function CalculateLongitudeCoordinate(ByVal sglLon1 As Single, ByVal sglLat1 As Single, ByVal sglLat2 As Single, intRadius As Integer, ByVal intBearing As Integer, ByVal intDistance As Integer) As Single Return sglLon1 + Math.Atan2(Math.Sin(intBearing) * Math.Sin(intDistance / intRadius) * Math.Cos(sglLat1), Math.Cos(intDistance / intRadius) - Math.Sin(sglLat1) * Math.Sin(sglLat2)) End Function
To implement, we need to create a form with a few elements:
<p>
Select all ZIP Codes within
<asp:DropDownList runat="server" ID="ddlDistance">
<asp:ListItem Selected="True">5</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
<asp:ListItem>25</asp:ListItem>
<asp:ListItem>50</asp:ListItem>
<asp:ListItem>100</asp:ListItem>
</asp:DropDownList>
miles of ZIP Code
<asp:TextBox runat="server" ID="tbZip" Columns="5" />
<asp:RequiredFieldValidator
runat="server"
ID="rfvZip"
ControlToValidate="tbZip"
ErrorMessage="Please provide a ZIP Code"
CssClass="warning"
Display="Dynamic"
/>
<asp:RegularExpressionValidator
runat="server"
ID="revZip"
ControlToValidate="tbZip"
ValidationExpression="^[0-9]{5}$"
ErrorMessage="Please enter a valid five-digit ZIP Code"
CssClass="warning"
Display="Dynamic"
/>
<asp:Button runat="server" ID="btnZip" Text="Get ZIP Codes" />
</p>
<p><asp:Label runat="server" ID="lblStatus" Text="Status messages will appear here" /></p>
<asp:GridView
runat="server"
ID="gvZIP"
DataSourceID = "sqlZip"
AutoGenerateColumns="false"
AllowSorting="true"
AllowPaging = "true"
PageSize = "20"
HeaderStyle-BackColor="Yellow"
HeaderStyle-Font-Bold="true"
HeaderStyle-HorizontalAlign="Center"
AlternatingRowStyle-BackColor="WhiteSmoke"
CellPadding="5"
>
<Columns>
<asp:BoundField HeaderText="City" DataField="cityname" SortExpression="cityname" />
<asp:BoundField HeaderText="State" DataField="statecode" SortExpression="statecode" />
<asp:BoundField HeaderText="ZIP Code" DataField="zip_code" SortExpression="zip_code" />
<asp:BoundField HeaderText="Latitude" DataField="latitude" SortExpression="latitude" />
<asp:BoundField HeaderText="Longitude" DataField="longitude" SortExpression="longitude" />
<asp:BoundField HeaderText="Distance" DataField="distance" SortExpression="distance" />
</Columns>
</asp:GridView>
<asp:SqlDataSource
runat="server"
ID="sqlZip"
SelectCommand="sp_get_zips_in_radius"
SelectCommandType="StoredProcedure"
ConnectionString="YOUR CONNECTION STRING"
>
<SelectParameters>
<asp:Parameter Name="maxlat" DbType="Decimal" DefaultValue="0.0" />
<asp:Parameter Name="minlat" DbType="Decimal" DefaultValue="0.0" />
<asp:Parameter Name="maxlon" DbType="Decimal" DefaultValue="0.0" />
<asp:Parameter Name="minlon" DbType="Decimal" DefaultValue="0.0" />
<asp:Parameter Name="startlat" DbType="Decimal" DefaultValue="0.0" />
<asp:Parameter Name="startlon" DbType="Decimal" DefaultValue="0.0" />
<asp:Parameter Name="radius" DbType="Int16" DefaultValue="3959" />
</SelectParameters>
</asp:SqlDataSource>
Although I am going to bind my data via codebehind, and therefore could have used a SqlDataReader, DataTable or the like as my GridView’s data source, I am using a SqlDataSource because I want to be able to page and sort my results, and I am too lazy to write code behind to do all that; those features are native to a GridView bound to a SqlDataSource.
If I didn’t want to page and sort, I’d just use a SqlDataReader and bind the GridView to that.
We need to get, from the database, the initial geocoordinates for the user-supplied ZIP Code. We do that with a stored procedure:
CREATE PROCEDURE [dbo].[sp_get_zip_code]
@zip_code CHAR(5)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT *
FROM zip_codes
WHERE zipcode = @zip_code
END
This query should return to us the city name, state, latitude and longitude for the specified ZIP Code. As always, we want to catch any exceptions in making the query, and we want to make sure we get a record from the database (that is, we can find the starting ZIP Code in the database).
If we can’t get starting coordinates, we’ll report that.
Otherwise, we’ll output details about the starting point to our Label control, and invoke a (yet-to-be-written) subroutine to populate the GridView.
Sub GetInitialCoordinates() Handles btnZip.Click
'This subroutine requires a Label control named lblStatus
'Prepare to connect to db and execute stored procedure
Dim objConn As New SqlConnection("YOUR CONNECTION STRING")
Dim objCmd As New SqlCommand("sp_get_zip_code", objConn)
objCmd.CommandType = CommandType.StoredProcedure
'we need to supply the ZIP code as an input parameter to our stored procedure
objCmd.Parameters.Add(New SqlParameter("zip_code", SqlDbType.Char, 5))
objCmd.Parameters("zip_code").Value = tbZip.Text
'sglMinLat = south, sglMaxLat = north, sglMinLon = west, sglMaxLon = east
Dim sglMinLat As Single
Dim sglMaxLat As Single
Dim sglMinLon As Single
Dim sglMaxLon As Single
Try
'open connection
objConn.Open()
'put results into datareader
Dim objReader As SqlDataReader
objReader = objCmd.ExecuteReader()
If objReader.HasRows Then
'if starting point found, calculate box points
objReader.Read()
sglMinLat = Rad2Deg(CalculateLatitudeCoordinate(Deg2Rad(objReader("latitude")), 3959, Deg2Rad(180), ddlDistance.SelectedValue))
sglMaxLat = Rad2Deg(CalculateLatitudeCoordinate(Deg2Rad(objReader("latitude")), 3959, Deg2Rad(0), ddlDistance.SelectedValue))
sglMinLon = Rad2Deg(CalculateLongitudeCoordinate(Deg2Rad(objReader("longitude")), Deg2Rad(objReader("latitude")), Deg2Rad(sglMinLat), 3959, Deg2Rad(270), ddlDistance.SelectedValue))
sglMaxLon = Rad2Deg(CalculateLongitudeCoordinate(Deg2Rad(objReader("longitude")), Deg2Rad(objReader("latitude")), Deg2Rad(sglMinLat), 3959, Deg2Rad(90), ddlDistance.SelectedValue))
'report starting point details to lblStatus
Dim strOut As String
strOut = "ZIP Code " & tbZip.Text & " is assigned to " & objReader("cityname") & ", " & objReader("statecode") & ".<br />"
strOut &= "It is located at latitude " & objReader("latitude") & ", longitude " & objReader("longitude") & ".<br /><br />"
strOut &= "At a distance of " & ddlDistance.SelectedValue & " miles, the search box coordinates are:<br />"
strOut &= "Maximum latitude (North): " & sglMaxLat & "<br />"
strOut &= "Miniumum latitude (South): " & sglMinLat & "<br />"
strOut &= "Maximum longitude (East): " & sglMaxLon & "<br />"
strOut &= "Minimum longitude (West): " & sglMinLon & "<br />"
lblStatus.Text = strOut
'populate gridview
PopulateGridView(sglMinLat, sglMaxLat, sglMinLon, sglMaxLon, objReader("latitude"), objReader("longitude"))
Else
'starting point not found
lblStatus.Text = "Error retrieving initial ZIP Code coordinates: No record found for " & tbZip.Text & "."
End If
objConn.Close()
objCmd.Dispose()
objConn.Dispose()
Catch ex As Exception
'technical problem running the query
lblStatus.Text = "Error executing database query for initial coordinates: " & ex.Message
End Try
End Sub
Lastly, we need to create a stored procedure that will accept the coordinates for our search box, and return all coordinates that fall within that box.
That’s because I am going to calculate, on the fly, the distance from my starting point to the ZIP Codes returned by the query.
Given two known points, expressed as lat1, lon1, lat2 and lon2; and a known radius of the sphere on which they are located, expressed as r; the distance between those points is found via this formula:
distance = acos(sin(lat1) * sin(lat2) + cos(lat1) * cos(lat2) * cos(lon2 - lon1)) * r
Again, we have to convert our latitude and longitude coordinates from degrees to radians; but note that we do not need to convert the result of the expression from radians to degrees. I will, however, round that result down to a precision of 2.
CREATE PROCEDURE [dbo].[sp_get_zips_in_radius] @maxlat DECIMAL(9, 6), @minlat DECIMAL(9, 6), @maxlon DECIMAL(9, 6), @minlon DECIMAL(9, 6), @startlat DECIMAL(9, 6), @startlon DECIMAL(9, 6), @radius INT AS SELECT zipcode, statecode, latitude, longitude, cityname, ROUND(ACOS(SIN(RADIANS(@startlat)) * SIN(RADIANS(latitude)) + COS(RADIANS(@startlat)) * COS(RADIANS(latitude)) * COS(RADIANS(longitude) - RADIANS(@startlon))) * @radius, 2) AS distance FROM zip_codes WHERE latitude < @maxlat AND latitude > @minlat AND longitude < @maxlon AND longitude > @minlon ORDER BY distance, zipcode, statecode, cityname
Now that we have the stored procedure ready to go, we just need to update the values of our SqlDataSource’s SelectParameters, then bind the GridView:
Sub PopulateGridView(ByVal sglMinLat As Single, ByVal sglMaxLat As Single, ByVal sglMinLon As Single, ByVal sglMaxLon As Single, ByVal sglStartLat As Single, ByVal sglStartLon As Single)
sqlZip.SelectParameters("minlat").DefaultValue = sglMinLat
sqlZip.SelectParameters("maxlat").DefaultValue = sglMaxLat
sqlZip.SelectParameters("minlon").DefaultValue = sglMinLon
sqlZip.SelectParameters("maxlon").DefaultValue = sglMaxLon
sqlZip.SelectParameters("startlat").DefaultValue = sglStartLat
sqlZip.SelectParameters("startlon").DefaultValue = sglStartLon
gvZIP.DataBind()
End Sub
And that’s all there is to it. You can see a working demo here: http://dougv.net/demos/zip_code_distance
You can download the code here: https://www.dougv.com/wp-content/uploads/2012/04/zip_code_distance.zip
All links in this post on delicious: http://delicious.com/dougvdotcom/getting-all-zip-codes-in-a-given-radius-from-a-known-point-zip-code-via-asp-net
Tags: latitude / longitude, regular expression, ZIP Code databasehow to increment count in database on clicking radio button
There are a few ways to go about this. I’ll demonstrate two: a traditional, PHP / MySQL only, postback approach, and a jQuery version that uses AJAX to asynchronously record and update the counts.
Just to be clear: In order to complete this solution, we have to use both JavaScript and a server-side scripting language. We use JavaScript to intercept the user clicking the radio button, but process the fact that the button was clicked on the server.
Also, for the purpose of this tutorial, I’ll assume that the radio button involved is part of a group. That is, we have several radio buttons, all with the same name, but different values, e.g.:
<form id="myform" name="myform" method="post"> <p>Select a color:</p> <label id="l_red"><input type="radio" id="r_red" name="color_name" value="red" />Red</label> (<label id="c_red">0</label>) | <label id="l_green"><input type="radio" id="r_green" name="color_name" value="green" />Green</label> (<label id="c_green">0</label>) | <label id="l_blue"><input type="radio" id="r_blue" name="color_name" value="blue" />Blue</label> (<label id="c_blue">0</label>) | <label id="l_black"><input type="radio" id="r_black" name="color_name" value="black" />Black</label> (<label id="c_black">0</label>) </form>
The easiest way to process a click is right on the page that contains the radio button, via a simple postback.
We begin with a MySQL table that will hold our count. It has two columns: color_name, which will contain unique values and thus can act as our table’s primary key; and color_count, the number of times that color has been clicked.
CREATE TABLE IF NOT EXISTS `colorcounter` ( `colorname` varchar(5) NOT NULL, `colorcount` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`colorname`) )
Next, we query the database for the current counts, which we’ll display in our form, later. We’ll dump the results set into an associative array, which will make outputting the counts in our form a little easier.
$link = mysql_connect('server', 'user', 'password') or die('Cannot connect to database server');
mysql_select_db('database') or die('Cannot select database');
// click-count increment routine will go here; more on that shortly ...
// get current color click counts
$rs = mysql_query("SELECT * FROM colorcounter") or die ('Cannot process SQL count totals query');
if(mysql_num_rows($rs) > 0) {
while($row = mysql_fetch_array($rs)) {
$count[$row['colorname']] = $row['colorcount'];
}
}
We now need to set up our form to be ready to process the user click, and to display the click count records we previously received.
<form id="myform" name="myform" method="post" action="<?php echo htmlspecialchars($_SERVER['PHP_SELF']); ?>"> <p>Select a color:</p> <label id="l_red"><input type="radio" id="r_red" name="color_name" value="red" onclick="this.form.submit();" />Red</label> (<label id="c_red"><?php echo $count['red']; ?></label>) | <label id="l_green"><input type="radio" id="r_green" name="color_name" value="green" onclick="this.form.submit();" />Green</label> (<label id="c_green"><?php echo $count['green']; ?></label>) | <label id="l_blue"><input type="radio" id="r_blue" name="color_name" value="blue" onclick="this.form.submit();" />Blue</label> (<label id="c_blue"><?php echo $count['blue']; ?></label>) | <label id="l_black"><input type="radio" id="r_black" name="color_name" value="black" onclick="this.form.submit();" />Black</label> (<label id="c_black"><?php echo $count['black']; ?></label>) </form>
Finally, we need to add some code to the page that will see if the form has been submitted, and increment the appropriate count.
// if this is a postback ...
if(isset($_POST['color_name'])) {
// create array of acceptable values
$ok = array('red', 'green', 'blue', 'black');
// if we have an acceptable value for color_name ...
if(in_array($_POST['colorname'], $ok)) {
// update the counter for that color
$q = mysql_query("UPDATE colorcounter SET colorcount = colorcount + 1 WHERE colorname = '" . $_POST['color_name'] . "'") or die ("Error updating count for " . $_POST['color_name']);
}
}
Note that this code block should appear before the code block that gets the current click counts (where I included the comment in the first block of PHP code), in order to ensure the counts are up-to-the-minute.
You can see a working demo here: http://www.dougv.com/demo/ajax_radio_clickcount/index.php
The previous example has a number of drawbacks, not the least of them being that each time you click on a radio button, the entire form posts back to the server.
That limits its usefulness quite a bit. We could work around this problem a few ways; perhaps making sure we repopulate our form fields following the postback, and only performing final processing when the submit button is clicked (by adding an onclick event to that button, which hands off the results to a JavaScript function or a different processing page).
That’s an awful lot of work for very little benefit. It makes way more sense to leverage AJAX, and update our click counts without having to post the form itself back to the server.
JavaScript — more specifically, jQuery — to the rescue!
To me, it makes sense to encumber the jQuery library whenever you create an AJAX script, rather than reinventing the wheel with an old-school XMLHTTPRequest object that has to be written several ways to ensure it works in whatever browser you encounter.
To get our jQuery solution to work, we need to create two PHP “helper” pages: one that will update our click counts whenever a radio button is selected, and the other that will get the current click counts, both on initial page load and following a click.
Let’s start by calling the helper page that gets our click counts get_counts.php. It works almost exactly like the code block that gets the counts in our PHP postback example, except this page is going to output our results in JSON.
We’re putting the results out that way because it’s very easy for jQuery to process data in JSON format.
Or, to put it even another way, think of JSON as the JavaScript version of an XML file. It’s just a way to organize data into columns and rows.
JSON is actually a bit more complex than that, but the way we’re using it, JSON is just a recordset, exactly like we would get from a MySQL query. It’s just written differently.
Let’s look page will expect to see a querystring variable named “color,” which indicates which value needs to be incremented. The page code itself will be very similar to the processing code in our postback version.
$link = mysql_connect('server', 'user', 'password') or die('Cannot connect to database server');
mysql_select_db('database') or die('Cannot select database');
// get new count totals, pass as JSON
$rs = mysql_query("SELECT * FROM colorcounter") or die('Cannot get updated click counts');
if(mysql_num_rows($rs) > 0) {
$out = "{ ";
while($row = mysql_fetch_array($rs)) {
$out .= "\"$row[colorname]\" : $row[colorcount], ";
}
$out = substr($out, 0, strlen($out) - 2);
$out .= " }";
header("Content-type: application/json");
echo $out;
}
Now that we have a way to get the counts, we need a way to increment them. Once again, we make a PHP helper page — increment_counter.php — that works fundamentally the same as the code block that increments the click counts in our PHP postback page, except that it calls upon GET, instead of POST.
(Yes, you can use jQuery to make POST and GET AJAX requests. When I’m sending a single variable that just needs to meet a range, I prefer to use GET.)
$link = mysql_connect('server', 'user', 'password') or die('Cannot connect to database server');
mysql_select_db('database') or die('Cannot select database');
// if this is a postback ...
if(isset($_GET['color'])) {
// create array of acceptable values
$ok = array('red', 'green', 'blue', 'black');
// if we have an acceptable value for color_name ...
if(in_array($_GET['color'], $ok)) {
// update the counter for that color
$q = mysql_query("UPDATE colorcounter SET colorcount = colorcount + 1 WHERE colorname = '" . $_GET['color'] . "'") or die ("Error updating count for " . $_GET['color']);
}
}
With our helper pages out of the way, we can proceed to work on the form and the jQuery JavaScript needed to make this work.
As usual, our first step is to procure and include a copy of jQuery on our page. Lately, I prefer to simply link to the version hosted by the Google Libraries API, but you can download jQuery directly and run it off your server if you prefer. Either way, call it somewhere in your page’s head section:
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
Next, I want to strip the form back to its basic components.
<form id="myform" name="myform" method="post"> <p>Select a color:</p> <label id="l_red"><input type="radio" id="r_red" name="color_name" value="red" />Red</label> (<label id="c_red">0</label>) | <label id="l_green"><input type="radio" id="r_green" name="color_name" value="green" />Green</label> (<label id="c_green">0</label>) | <label id="l_blue"><input type="radio" id="r_blue" name="color_name" value="blue" />Blue</label> (<label id="c_blue">0</label>) | <label id="l_black"><input type="radio" id="r_black" name="color_name" value="black" />Black</label> (<label id="c_black">0</label>) </form>
Notice that I no longer have onclick events assigned to each radio button. That’s because I’ll wire up that event via $(document).ready.
More on that shortly. First, I want to declare a global variable, lastChecked. It will keep track of the value of the last radio button that was checked.
This will keep our page from recording multiple clicks on the same radio button, over and over again; clicks won’t be recorded unless, and until, a new value is clicked. In other words, if I just keep clicking Red, it will only be recorded once; if I then click Blue, that will be incremented, and clicking Red again will increment its value, once again.
var lastClicked = '';
I next want to write JavaScript functions for each of my helper pages. The first, getTotals(), will — as its name suggests — send an AJAX request to get the current click totals. It will then assign those totals to the labels after each radio button.
function getTotals() {
// function to get click counts as JSON from helper page
// expects get_count.php to be in same directory level
$.ajax({
type: "GET",
url: "get_count.php",
dataType: "json",
error: function(xhr, status, msg) {
alert("Failed to get click counts: " + msg);
}
})
.done(function(data) {
// loop through JSON variables, assign to count labels
$.each(data, function(key, value) {
var tmp = "#c_" + key;
$(tmp).text(value);
});
});
}
I can now write the JavaScript function that will increment click counts. It’s named, quite cleverly, processClick, and takes as its argument the radio button (obj, for “object”) that was clicked.
function processClick(obj) {
// function to increment click count via ajax
// expects increment_count.php to be in same directory level
if(lastClicked != obj.val()) { // don't count clicks on currently active radio button
lastClicked = obj.val(); // set currently clicked radio button to this one
var qs = "color=" + obj.val(); // set query string value
$.ajax({
type: "GET",
url: "increment_count.php",
data: qs,
error: function(xhr, status, msg) {
alert("Failed to process click count: " + msg);
}
})
.done(function() {
getTotals(); // update totals on successful processing
});
}
}
Finally, we want to use $(document).ready to initially populate the click total labels, and to assign processClick as the event handler for clicks on all the radio buttons on the page.
$(document).ready(function() {
getTotals(); // get click totals on initial page load
$(document).ready(function() {
// add click incrementing event handler to all radio buttons on page
$('input:radio').click(function() {
processClick($(this));
});
});
});
You can see a working example here: http://www.dougv.com/demo/ajax_radio_clickcount/example2.htm
A few notes about this project:
It’s not overly difficult for someone to click spam what I have here, either by simply clicking a radio button multiple times, or by calling the increment_count.php helper page multiple times.
A way around that would be to create single-use tokens for each iteration of the form, so that only one click per rendering of the form would be allowed.
You could do that by creating a session variable or cookie, if you’re using the PHP postback example; setting a Boolean in that session / cookie to be true the first time the form is submitted; and refusing to process the click if that session / cookie Boolean is true.
Or, for more security, you could add another MySQL table, which counts the number of times your form has been rendered. It would consist of an autoincremented primary key, which would act as an identifier for this instance of the form; and an tinyint column for recording a Boolean, which would indicate if the form had been submitted.
Every time your form is rendered, you would insert a new record in your form-rendering table, with a Boolean value of false indicating that the form has not yet been processed, then use mysql_insert_id to get the primary key value of the record you just created.
Using mcrypt, you would then create a single-use token by encrypting that record ID with a secret key only you know, and pass that to whatever method you are using to increment the counter.
That processing code would decrypt the token, make sure the record with that ID has a Boolean false as its “form has been submitted” value. If not, the code would not process the click; but if so, the code would process the click, then mark the form’s Boolean value as true.
An added benefit of this method is that it also inhibits forgeries and cross-site scripting attacks, since the only way to create legitimate tokens is via your secret key. (Theoretically, a hacker could crack your key, but use a sufficient passphrase and a good crypto algorithm, and the chances of anyone even taking a crack at it, nonetheless succeeding, are infinitesimal.)
Sounds more complex than it is. I’ve got demonstrating how to make a single-use token like this in Evernote blog column ideas. If significant interest is expressed, I’ll move it to the top of the list.
You can download the code in this post here: https://www.dougv.com/wp-content/uploads/2012/04/ajax_radio_clickcount.zip
All links in this post on delicious: http://delicious.com/dougvdotcom/how-to-increment-a-counter-in-mysql-based-on-a-radio-button-click
Tags: arrays, global constants, Google, radio buttons"You try to keep your kids safe. ... Like, if my kids get in a car with me, I make them buckle up. I make a big deal out of it; I'm not even starting this car until you buckle your seat belts.One of the best shows on TV, and it just keeps getting better.
"(But) if we get in a taxi, I'm like, 'Just ... it's fine. Taxis are magic, nobody dies. Just get in, just go.'
"I'm not digging in the seat for a belt. There's no way I am blindly digging into the Egyptian hepatitis and severed toes so that you can have a seat belt. You gotta put on your own mask before helping the others. I'm not going through that.
"So my kids get in the cab, and they just hurtle through space at a speed determined by the profit motive of an exhausted man from another country, where life is shit cheap. Where kids die all day, and it's boring."
While vanity Googling myself today, I ran across an old news story I wrote about a murder-suicide-arson in Mount Vernon, Maine, on Sept. 29, 1994. (Warning: Java applet at source)