Category Archives: BU MET CS 601 Fall 2011

Category to hold my blog posts for MET CS 601 that I am taking during the Fall 2011 semester.

CS 601 – MySQL INSERT … SET syntax

by

While working on my project, I came across the following. The standard syntax for inserting items into a database is something akin to:

INSERT INTO tblMyTable (FirstName, LastName, Email, Phone)
     VALUES ('John', 'Doe', 'johndoe@gmail.com', '1234567890')

This is standard SQL. However, MySQL has an alternative syntax using SET which is similar to the UPDATE syntax:

INSERT INTO tblMyTable SET
       FirstName = 'John',
       LastName  = 'Doe',
       Email = 'johndoe@gmail.com',
       Phone = '1234567890'

I ended up using the alternative syntax throughout my project. Why? I felt it is much cleaner and easier to read than the standard syntax, especially when removing a column to insert. (When adding a column, it’s easy enough to add them to the end of the lists.) It should also greatly reduce errors from incorrect ordering between column names and values since they’re right next to each other. I realize that using MySQL specific syntax would break the site if it was moved to another database, but I don’t feel that was a concern for this project. In the real world, especially dealing with projects that are commercial and may/will grow larger, it might not be a good idea.

Here’s an example from my actual code:

$query = <<<EOT
    INSERT INTO tblReservations SET
        dtReservationDateTime = FROM_UNIXTIME($datetimeunix),
        intPartySize = {$_POST['numparty']},
        intCustomerID = "{$_POST['userid']}",
        vcReservationNameFirst = "{$_POST['firstname']}",
        vcReservationNameLast = "{$_POST['lastname']}",
        vcReservationEmail = "{$_POST['email']}",
        chReservationPhone = $phone,
        vcComments = "{$_POST['comment']}",
        dtReservationDateTimeMade = NOW()
EOT;

CS 601 – JQuery UI Datepicker – Blocking Dates

by

One of the things I wanted to do is to block certain dates from being enabled in the JQuery UI Datepicker widget. There is built-in functionality for blocking weekends, but not for particular dates, e.g., Christmas. Some web searching led me to a fairly easy solution. The widget has an option for providing a function to call before a date is shown. Here is my widget constructor:

$('#datepicker').datepicker({
                inline: true,
                showOn: "both",
                buttonImage: "/cafemarburg/images/calendar.jpg",
                minDate: +1,
                maxDate: +180,
                beforeShowDay: checkClosedDates
        });

checkClosedDates is a function that returns an array of the form [true/false, a class to add, a tooltip message], the last two being optional. It takes the date (as a Javascript object) as a parameter.
Here is the function call:

var closedFullDates = new Array(); // Used for one-time closures or irregular scheduled holidays
var closedYearlyDates = new Array('12-25','7-4'); // Used to regular holidays (e.g., Christmas)
var closedDaysOfWeek = new Array('0')
function checkClosedDates(date) {
 var blocked = false;
 var ymd = date.getFullYear() + '-' + (date.getMonth()+1) + '-' + date.getDate();
 var md = (date.getMonth()+1) + '-' + date.getDate();
 if ($.inArray(ymd,closedFullDates) > -1) {
     blocked = true;
 } else if ($.inArray(md,closedYearlyDates) != -1) {
     blocked = true;
 } else if ($.inArray(String(date.getDay()),closedDaysOfWeek) != -1) {
     blocked = true;             
 }
 if (blocked) {
    return [false,'dpClosedDate','Sorry, we are closed that day.'];
 } else {
     return [true];
 }
}

As can be seen, I use three separate arrays. One is for one-time events or events that change date year to year. Another is for dates that occur on the same date. The third is for days of the week.

CS 601 – PHP Multiline Strings

by

During the course of my project, I had many instances of needing to write very long strings, often when constructing moderately complex MySQL queries. There are several way to do this. One would be repeated each statements or assignment with .= . Another would be running the echo/assignment over multiple lines by using . at the end of each until the final one (ended with ; ). Example:

$mystr = 'Here is a really long line of text that runs to '.
         'more than one line';

I found a very clean and convenient way is to use PHP’s heredoc syntax. Here’s an example:

$query = <<<EOT
        SELECT * FROM tblOrderItems
        INNER JOIN tblFoodItems
                ON tblFoodItems.intFoodItemID = tblOrderItems.intFoodItemID
        INNER JOIN tblFoodCategories
                ON tblFoodCategories.intFoodCategoryID = tblFoodItems.intFoodCategoryID
        INNER JOIN tblFoodCategoryOrder
                ON tblFoodCategoryOrder.intFoodCategoryID = tblFoodCategories.intFoodCategoryID
        WHERE intOrderID = {$order['intOrderID']}
        ORDER BY tblFoodCategoryOrder.intFoodCategoryOrder, tblFoodItems.vcFoodItemName
EOT;

There’s a few things to note:

  • The EOT is arbitrary as long as both instances are the same. (I use EOT for “end of text”.)
  • Variables can be included in the string. A simple variable is simply included as $myvar. Here, I use the {} to separate out a “complex” variable. (I didn’t realize this could be done until later on, so for some of my earlier instances, I set it to a simpler variable immediately before the heredoc declaration.)
  • The terminator (here EOT;) MUST be on a line by itself with no leading or trailing whitespace. This cannot be emphasized enough. Leading whitespace is pretty easy to spot, but several times NetBeans added trailing whitespace which wasn’t obvious in and of itself. (NetBeans does color the heredoc string differently, so you will see the “carry over”.) I finally got in the habit of checking.

PHP ≥ 5.3.0 also has the nowdoc syntax. It’s very similar to heredoc except is uses $myvar = <<<‘EOT’ (note the single quotes). The difference is that variables are not expanded akin to standard single quoted strings.

CS601 – Delete Current Element from DOM

by

At one point a few weeks back (for one of the homeworks), I could not figure out how to delete the current element from the DOM while working in Javascript. Some searching turned up the following solution:

detaildiv.parentNode.removeChild(detaildiv);

Basically, you get the element’s parent node, then deleted the specified child node, this being the element. I’m not sure this the standard method, but it works and is clean. (This was before we were looking at JQuery, so perhaps it has a better solution.)

CS601 – Tablesorter

by

I’m a bit behind in blogging for class due to time constraints. I’m spending most of my free time working on my project. I have a bunch of ideas in the queue, but here’s one I just used tonight.

One aspect of the project requires a sortable table. Not easy to implement on my own, but I found the very nifty JQuery UI Tablesort plugin. This is pretty cool and pretty easy to implement. I’ll include some snippets from the page which shows the employees as list of all reservations.

Here is the JS code which creates the object:

<script type="text/javascript">

// add parser through the tablesorter addParser method
$.tablesorter.addParser({
// set a unique id
id: ‘restatus’,
is: function(s) {
// return false so this parser is not auto detected
return false;
},
format: function(s) {
// format your data for normalization
return s.toLowerCase().replace(/made/,0).replace(/arrived/,1).replace(/seated/,2);
},
// set type, either numeric or text
type: ‘numeric’
}); $(document).ready(function()
{
$(“#restable”)
.tablesorter();
}
);
</script>

The first block is simply a function to allow me to sort the reservation status (e.g., has the party arrived, have they been seated) using an ordering index regardless of the text for the status. (E.g., I would want “Made” sorted before “Arrived”.)

The second block actually creates the applies the tablesorter object to the HTML table with the ID of restable. This table is a plain old HTML table with the addition of the class of tablesorter.

You can also download the themes from the site, put them in your CSS directory, and then include that in your header. With all that you get the following (on load):

If you click the Last Name column:

If you click the Date/Time column:

It does the date correctly automatically. (Verified as 11/30/2011 12:00 PM sorts before 11/30/2011 01:30 PM and before 01/20/2012 07:45 PM. If it was a simple text-based sort, this would not be the case as 0 comes before 1.)

It can also do multicolumn sort and a multitude of other things which I have not explored.

CS 601 – Clearing Divs

by

One of the issues that came up was where cases where divs floated (left, but I suppose it could happen to right floated ones as well) is that they are misaligned when they don’t fill the space. Below is an example of this from my registration form. What I want is to get fields in the form:

City     State   Zip

Phone     Email

Instead I get:

I fixed this to get the desired result:

How did I do this? I used a “clearing” div as follows:

        <label for="zipcode"><span>Zip</span>
            <input type="text" name="zipcode" />
        </label>
        <div class="clearing"></div>  <=== Relevant Code
        <label for="phone"><span>Phone</span>
            <input type="text" name="phone" />
        </label>
        <label for="email"><span>Email</span>
            <input type="text" name="email" />
        </label>

(Note, I omitted the City and State as the State select input has a bunch of irrelevant PHP code.)

An here's the CSS:

.clearing {
    clear: both;
}

Based on some quick reading, this is telling the browser to not allow floating elements on both sides, hence resetting the floats. I have to admit, I didn't realize this now. Instead, I have been using it extensively for float issues in the BU WordPress installation used for the Chemistry website. (It works even better now that they're not lost when someone edits a page with the visual editor instead of the HTML editor.)

Based on some additional reading, it seems like another way to do this is to put the objects in a containing div and then setting the width and overflow: auto . (See http://www.quirksmode.org/css/clearing.html.) I actually think the "old" way is better as you don't have to readjust the width of the container if the inner elements change and you don't have to have the extra CSS to set the width for each and every container. (Instead, I have one CSS selector for div.clearing.) Also, In my mind it's cleaner to have the single, self-contained div line in the HTML instead of the div wrapping a bunch of other code.

CS 601 – Scrollable Divs

by

While doing my styling, I realized the menu is going to be much longer than the space I have allotted for it in the design. I used the technique listed at http://www.htmlite.com/faq015.php to make the menu div scrollable. Here’s the CSS:

.menu {
    height: 700px; /* Same height as content div */
    overflow: auto;
}

Here’s what it looks like:

I’m not sure I like what it looks like, and I may replace it with some nicer Javascript. For now, however, it works.

CS 601 – Different Footers Depending on State

by

One of the things I’m doing with the site is to have different footers depending on if a user is logged in. The footer will contain a second, static menu for accessibility reason. This would be pretty straightforward to do in PHP in the footer page except I have different numbers of items depending on if a customer is logged in, if an employee is logged in, or if no one is logged in. This means the div widths have to change to keep it centered in the footer menu wrapper div. I could do this in the page, but I thought it would get messy and cumbersome. Instead I use includes.

In index.php:

$loggedinCustomer = True;  // Just here for styling testing.
$loggedinEmployee = False; // Will be extracted from login cookie.

In footer.php:

if ($loggedinCustomer) {
    include('footermenuCustomerLoggedIn.html');
} else if ($loggedinEmployee) {
    include('footermenuEmployeeLoggedIn.html');
} else {
    include('footermenuNotLoggedIn.html');
}

Each of the HTML pages is the html for just the menu and not a complete page (with head and body elements, etc.) The main.css file then contains selectors for different divs, etc., classes of NotLoggedIn, CustomerLoggedIn, and EmployeeLoggedIn. In fact, considering it, I could even import different CSS files depending on the login state, but that might be overkill for this.

This looks like an interesting solution. and I may use it for more than this. It also follows the model-view-controller (MVC) desing concept presented by Murach.

PS, coming soon will be the post about MySQL developer which I promised a couple of weeks back. I’m also going to do one on the Netbeans IDE. For the latter, suffice to say that if you’re not using it (or an equivalent IDE), get it.

CS 601 – HTML Validator Firefox Plugin

by

While Googling for the W3C HTML validator page to add it as a bookmark, I found that there is a plugin for Firefox that will validate the code right in the browser. It has the option to run the same backend validator engine as the service at W3C (the SGML parser), to run a more thorough validation engine (the Tidy parser; also developed by the W3C) or to run them serially (i.e., the W3C engine and then the more thorough one if no errors are found.) The Tidy parser can also do accessibility checks and suggest fixes, among other things. The plugin can be found at https://addons.mozilla.org/en-US/firefox/addon/html-validator/. Keep in mind that you will get more errors with the Tidy parser. Playing with some “real” sites is interesting. Google yields 60 errors. Amazon came up with either 160 or 1566 depending on how the results are read. (I think it’s different reading a frame vs the entire page.) Reddit has 45. It looks like it might have some issues with HTML5, but you simply have to “view source” in the plugin menu. It also has a direct link to the online HTML validator (sending the page) as well as the CSS validator.