Updating a database display order with drag and drop in SQL

In many of our applications we support dragging and dropping datasets to change the order. We save this order in a database column that is an integer and is in sequential order.

There are many ways in javascript to handle drag and drop. I will show an example using jquery sortable. If you’d like to use something else there are three pieces of key information that need to be sent to the server:

  1. Current position
  2. Desired position
  3. User’s id

HTML list

Javascript sortable

Example data set for table todos

user_id display_order todo
2000 1 Take out garbage
2000 2 Clean house
2000 3 Do dishes
2000 4 Cut grass
2000 5 Change light bulb

Step #1 – Determine the position

Step #2 – Update the dragged item

Step #3 – Move the item down

Step #4 – Move the item up

Step #5 – Update the dragged item to the desired position

With this approach the server will do a total of three queries for every change to the display order no matter how many items are in the list. Examples typically tell you to iterate over every item to do an update query to set the new order. That approach results in total queries = total amount of items. This new approach is a significant improvement and has reduced a lot of our large data sets from 40+ queries down to three.

We’re Hiring: Full Time Front-End Web Developer

We are hiring! The Web Communications office is looking for an in house Web Developer. It’s not often we put a call out for new staff but we are filling a much needed void in our team. Our department started eleven years ago with little staff and resources, we have since grown to a staff of eleven and take on new responsibilities every day. Almost everyone in the department started as a student and worked their way up (myself included). It’s not often we get the opportunity to hire from the outside, we’re looking for a talented individual to help level up the Web.

Group Work

The job

We are looking for someone with front-end development experience producing responsive and standards compliant HTML5 + CSS2/3 websites. Have experience with Javascript without the reliance on a specific library like jQuery. Proficient in using the latest front-end build tools such as Yeoman, Sass, GIT, Zurb Foundation, Gulp, Vagrant, etc. An understanding of PHP, MySQL and other back end programming languages isn’t required but is preferred.

With three developers the projects are distributed evenly so everyone has the opportunity to work on something new. We are a collaborative environment and this position has the opportunity to affect the direction of all future Web projects. If you have read our blog in the past you will know we push out a lot of sites, about one per week. All sites are created from the ground up, completely by hand, and completely responsively. The Web is a craft and we make sure we build all the tools needed to do it as streamlined as possible. This position has the opportunity to build and maintain these tools that impact the entire institution, not to mention work with an amazing team.

Primary Web property responsibilities

Environment

  • LAMP
  • Zurb Foundation
  • Sass
  • Yeoman
  • Gulp/Grunt
  • GIT
  • Vagrant

Official duties

  • Experience collaborating throughout the entire project cycle, from research, strategy, information architecture, visual design, front-end development and maintenance.
  • A solid grasp of modern front-end Web development, such as HTML, CSS and JavaScript and their associated build components.
  • A solid grasp of back-end Web development environments, including HTTP, Web servers, load balancers, the interpretation layer, databases and associated Web frameworks.
  • Considerable skill in writing web applications that retrieve and update information in relational Web centric databases.
  • The ability to clearly communicate to project stakeholders and process feedback internally and externally.
  • The ability to troubleshoot website layout and Web application performance issues and resolve issues independently or direct issues to the responsible party.
  • Provide direct supervision to internal Web site interns and guidance to unit Web site content authors.
  • Ability to work with accuracy and attention to detail to meet deadlines.
  • Ability to understand and execute oral and written instructions, policies, and procedures.
  • Considerable project management skills, including ability to provide time estimates and prepare accurate records and reports.
  • Proficiency in the use of Web applications programming languages, tools, and/or methodologies for developing integrated Web applications typically acquired through formal education or equivalent experience in Web application development.
  • Demonstrated ability in analyzing customer requirements and developing basic information systems solutions typically acquired through one to two years of directly related experience in Web application development and support.
  • The ability to translate functional requirements into cross-browser Web applications.
  • Strong understanding of Web technologies and related user device capabilities required to access the Web.
  • Strong understanding of test driven development.

Qualifications

  • Have worked with creating templates for a content management system.
  • Have experience with device and browser testing.
  • Working knowledge in Photoshop.
  • Enable and execute A/B tests to measure different design approaches.
  • Can code HTML Email templates with understanding of limitations, and standard practices.
  • An eye for detail and great communication skills, for example, multi-tasking in a dynamic, fast-paced environment.
  • Effectively communicate with Project Managers, Designs, Clients and other Developers.
  • Have an unquenchable thirst for knowledge and professional growth.
  • Create tools and resources to communicate with our rapidly expanding developer community.
  • Stay current with trends and best practices.

How to Apply

Please do not send resumes directly to me. Apply at jobs.wayne.edu. Posting #040365

DetCoffeeCode – Show and Tell @ Barnes and Noble – December 14, 2013

logoAs a member and leader of a handful of Detroit tech groups I can say that the community is second to none. If you’re interested in anything tech related there is surely an event every day of the week to attend.

DetCoffeeCode is one of those  groups, free to attend, and offers a great place to learn and network. Their next meeting is real close to home, right on Wayne State’s campus at Barnes and Noble.

DetCoffeeCode – Show and Tell

The afternoon will start with a networking session. Later, people will be able to show and tell their projects for 2014 to the group. If you don’t have a completed project then cool, show us what you have so far and we’ll help you out. Follow the Meetup event or @detcoffeecode for the updates!

When

Saturday, December 14, 2013
1:00 PM to 

Where

Barnes and Noble
5221 Gullen Mall
Detroit, MI 48202,

We’re Hiring: Full Time Web Developer

We are hiring! The Web Communications office is looking for an in house Web Developer. It’s not often we put a call out for new staff but with Shaun’s recent leave we are in need. Our department started seven years ago with little staff and resources, we have since grown to a staff of eleven and take on new responsibilities every day. Almost everyone in the department started as a student and worked their way up (myself included). It’s not often we get the opportunity to hire from the outside.

The job

We take the Web seriously. The position at hand is responsible for a third of all the code that gets published to the public Web server. This ranges from core back-end API functionality to front-end HTML, Javascript and accessibility. With three developers the projects are distributed evenly so everyone has the opportunity to work on something new. We are a collaborative environment and this position has the opportunity to affect the direction of all future Web projects. If you have read our blog in the past you will know we push out a lot of sites, about one per week. All sites are created from the ground up, completely by hand, and now completely responsively. The Web is a craft and we make sure we build all the tools needed to do it as streamlined as possible. This position has the opportunity to build and maintain these tools that impact the entire institution, not to mention work with an amazing team.

Primary Web property responsibilities

Environment

  • LAMP
  • PHP Simpl Framework
  • jQuery
  • Load balanced dev/prod environments
  • 450+ websites using centralized tools
  • SVN

Official duties

  • Translate functional requirements into Web applications
  • Centralize and maintain existing code bases
  • Prepare test plans for Web Applications
  • Design database schemas and develop database scripts in SQL
  • Serve as primary resource for units university-wide regarding the code and appearance of unit Web pages. Administer and provide training in content management tools, control access, review sites for adherence and conformity to code and design standards and resolve related problems.
  • Serve as liaison with IT personnel campus-wide to identify and resolve Web page related problems. Work with clients, designers and vendors to ensure established standards and expectations are met.
  • Develop and implement mechanisms and processes designed to evaluate the effectiveness of university electronic communications. Provide analysis of collected data and submit reports and recommendations as requested.

Qualifications

  • HTML + CSS: Mastery of responsive cross browser CSS and HTML. Extra points for RESS.
  • JavaScript: Proven understanding of Javascript fundamentals, plus experience working with AJAX
  • PHP + MySQL: Experience building data-driven web applications using PHP and MySQL. Solid understanding of relational databases required.
  • Experience working with Web Services and associated languages: REST, XML, JSON, etc.
  • Good knowledge of web standards and trends
  • Ability to learn and thirst for knowledge
  • Test/Behavior Driven Development experience is a definite plus
  • Graduation from an accredited college or university or an equivalent combination of education and/or experience. Major concentration in computer science preferred.
  • Excellent writing, editing and communication skills.
  • Ability to establish and maintain effective working relationships with units university wide.

How to Apply

Please do not send resumes directly to me. Apply at jobs.wayne.edu. Posting #039042

Help bring a two-day responsive Web design workshop to Detroit!

Build Responsively

Responsive Web Design

“Responsive design” is a subset of a larger concept which is called “Adaptive design”. When talking about responsive we refer to the layout only (Ethan Marcotte, fluid grids, flexible images & media queries). (source)

What is @brworkshop?

Build responsively is a moving workshop that focuses on Responsive Web Design. The talks start with the basics and go on to the advanced. A wrap up of the Cincinnati is online with the presenter slides if you’re interested.

This is a great opportunity to show the impact of the Web community in Detroit. Our city is going up against Pittsburgh, Chicago, Nashville, and others. Take just one minute to show your love and support for Detroit and RWD.

All it takes is a click and a tweet.


How does software quality happen?

Have you ever wondered why most software you interact with is hard to use or isn’t updated regularly to fix bugs? It’s a fundamental problem of computer science programs, they have failed to teach us how to manage software projects. Learning and using complex algorithms is just one small part of the software development process. The larger, more complicated (and ultimate determinator) of a project’s success is managing the requirements, time, team and process.

The most complex human task

Writing software is one of the most complex human tasks according to Douglas Crockford. Writing software isn’t a linear process, it is so complex we still haven’t learned how to create quality software. Although this is an older talk, it’s the best explanation I’ve heard about why writing software is so complex and what we can do about it.

The software crisis

He explains why the early programmers were so concerned with the software crisis and why it’s still happening today. If you are part of any software development process or even if you just use software on a daily basis this talk can give you some insight about why you may have frustrations around it.

Quality is more than good programming

Source: http://www.yuiblog.com/blog/2007/05/16/video-crockford-quality/

We’re looking for two student Web developers

With the recent departure of Brett and the upcoming graduation of Shaun we are in need of two new student Web developers. It’s a great opportunity to work in a real Web environment with real clients, deadlines and the ability to shape the future of Wayne State’s website.

Here’s who we’re looking for:

  • Freshman or sophomore computer science student (but not limited to)
  • Prefer PHP, MySQL, jQuery, MVC and Smarty (or similar) experience
  • Interest in contributing or previous experience with open source software
  • Excited about working with a lot of data
  • Interested in a career in Web development long term

If you were working for us, here is an idea of what your tasks look like:

  • Talk through the implementation of a new feature in the CMS with the senior developers
  • Fix a small CSS display bug in IE7 on a departmental homepage
  • Take a paper form and turn it into a web form with validation
  • Add a module to an existing site to view and export entries from a web form
  • Meet with one of our web designers to go over a mockup to determine which parts of the design may be difficult to implement
  • Take a PSD created by the designers as turned it into an HTML template in our CMS
  • Track down and fix a bug in an existing application that locked a user out of their manager
  • Create a database query to connect a few tables and export the results as a CSV
  • Answer emails from our clients (departments on campus) in a timely and polite mannor
  • Walk someone through adding an event on the university events calendar on the phone
  • Review another developer’s code for consistency and bugs
  • Test a site for mobile phone compatibility and print friendliness

This list may look intimidating, you won’t dive into everything your first week but it’s just a little of what you will experience.

Experience is not important. We aren’t interested in your resume, we don’t care if your only other job is at McDonald’s. We are looking for students who are aspiring web developers and have a passion to learn. We assure you will leave every day with more experience than you could ever get by yourself or sitting in class.

We are a teaching department, most of the full time staff started out as part time and grew in to full time positions while learning. Your education is important to us so we are very flexible about your schedule. We are looking for someone to work 10-20 hours per week with 10 hours being the minimum.

If this job sounds like something you’d love to do every day, we definitely want to hear from you. We need to hear from you!

Please get in touch by emailing web [at] wayne dot edu with a brief description of your experience and why you think you would be a good addition to our department. Please include “Student Developer” somewhere in the subject. Thanks!

 

Events Calendar Update: Campus Calendars, Cross Promotion & more

Introducing Campus Calendars

Each school/college/department has their own calendar and it’s worked great until a few months ago. We realized a lot of activities were cross disciplinary and more than one department/group plans activities for the same event. Our solution was to introduce “campus calendars”. These are available to everyone to add events to regardless of what calendars you own. Some of these calendars will show up year-round while others will only show up at certain times of the year. It will help pull together events from around campus into larger event groupings.

View by Category

The left hand column now has a “View by Category” heading. This allows visitors to just see events in a certain category. It works great when browsing for something to do based on interest. For example you can view all the “Art Shows” around campus in one listing regardless of what department is hosting it.

Facebook and Twitter features

We realized a lot of events are being shared through our “Add This” plugin and we wanted to make it even easier to tell friends about events. So we added the native Tweet and Like buttons from Twitter and Facebook to hook directly into the visitor’s accounts and their friends’. We don’t have enough stats from these services just yet but I’m sure we’ll do a post about their use soon.

Removing the “I’m Interested” and “I’m Going” links

After optimizing the right-hand menu to hook directly into existing social services we realized the “I’m Interested” and “I’m Going” functionality were redundant and confusing. We looked at our statistics and realized they were not being used as much as we had hoped and the data wasn’t available to the event owner and frankly wasn’t helping anyone. We ended up removing them completely and rely only on existing services.

Cross Promote

Lastly over the last few months we have been battling with groups around campus wanting to promote events on their homepages and calendars from other areas around campus. The current calendar only allowed the event owner to add the event to different calendars. We decided to allow anyone to “pull” events into their calendar. These events will show up in the normal listing and display on their homepage but they won’t have access to make changes to the event. You have to be logged in to see this option and when selected you can select the calendars you want a certain event to show up in.

All of the changes above were inspired by every user of the calendar. Our goal is to make the most useful calendar possible for the campus community. If you have any suggestions to make it better just drop us a line at web@wayne.edu.

Tweaks to the global header and footer

Yesterday we made some changes to the global header. The header looks almost identical, all the changes were under the hood. I have been meaning to make these changes for some time now. Luckily I used this week’s independent study time to get it accomplished. More about that later.

From an on screen perspective the header is now 5px shorter and 960px wide (our new standard) instead of 900. The code was simplified to reduce space and be more descriptive. Last but not least it now works fully in all A-grade browsers and IE all the way back to 5.5. I have also outlined a few other benefits below.

Better Print Support

At some point the print style for the global header just simply disappeared. Not sure what happened but it was just gone. This newest update fixes that. The print header is now black and white with our wordmark and Aim Higher fully readable on top of each page. The top tab doesn’t print and the bottom border is where it is suppose to be.

The only thing that changed in the footer was the removal of the copyright policy link and no longer underlining Wayne State University.

Better Mobile Support

Something completely left out of the old header was support for mobile or “handheld” style rendering. It doesn’t do any mobile detection just yet but any device that does pick up the handheld style will now get a header like the one on the right above.

Using Opera’s small screen rendering you can see a simulation of how it looks on a mobile device. The old header (left) doesn’t accomodate for the small width and forces the user to scroll left/right. The new version (right) linearizes the page, makes the skip links visible and the tab links are now visible by scrolling down.

Fewer HTTP Requests

We reduced the number of HTTP requests that it takes to build the header from 10 to just 5. We did this by creating a sprite of all the images that make up the header. We did have to add two new “real” images though for the wordmark and aim higher to print in black and white. Having fewer HTTP requests will allow the header to load faster on devices with higher latency and makes rendering quite a bit faster. Yahoo has a great explanation about the importance of fewer HTTP requests. As you can see from the graphs above although we increased the file size size overall (just a little) the total rendering time was reduced by almost half.

More technical graphs with the comparison: old header | new header

Using a Sprite

Above is the sprite we used to create the header. A List Apart has a great article explaining sprites. This single image is used to create all the background and styled images that make up the header. It just needs to load and position it in the right spot and the browser does the rest of the work.

Skip to Navigation and Content

Recently testing our site with a screen reader we found our skip links were not functioning correctly. WebAIM had a great article about making the skip links available to people using keyboard navigation so we decided to give it a shot. Above you will see what any of our sites look like if you press the “Tab” key to navigate your way through the page. The “Skip to Navigation” link will come up first, then the “Skip to Content” will show. Basically when the links have :focus they become visible, otherwise they are hidden.

Legacy Code

We tried to support as many legacy situations as possible and fix any issues before hand. But unfortunately we came up with two situations after the fact that we could not account for. The first was users who replicated the main header code but hotlinked to our style sheet. These sites will see an odd shaped header till they update their code. The second is customized headers, some sites used our original code but with overrides in their local css to change the header style. These sites will all be broken till the individual site administrators update their code, there is nothing we can do about custom overrides.

Using the New Header/Footer

If we don’t handle your web site and you want to use the new header/footer you can right away. The code and instrcutions can be found at: http://wcs.wayne.edu/style/

Additional Issues

If you happen to see a site that is looking funky with the new header please take a screen shot and send it to wcs@wayne.edu and we’ll look into it.