I have spent the last few weeks priming the Beer App. Now, I am proud to announce that the beer is ready to flow…well, beer information that is. I have finished both the User Login and Beer Mapping portions of the Beer App. If you have not yet signed up for an account, you can do so with the Create Account link in the right column. With an account, you will given all the power and responsibility to add new locations and manage their beer information.

If you click on the Beer App while signed in, you will see an option labeled ‘Map A New Location’. Once a location has been added, it is immediately available for beer mapping.

There are two ways to map beers; by beer and by location. You will be able to ‘map by beer’ from several pages within the App by clicking the ‘Map It’ button next to a beer. Selecting this option will prompt you to enter a location for the beer, choose a serving type and enter a price (if known).

You can also ‘map by location’ by clicking the ‘Map Beer’ option on the Store Location page. Selecting this option will prompt you to pick a beer, choose a serving type and enter a price (again, if known).

The Store Information page also allows you to edit prices and remove beers from the location. If a beer is no longer sold or served at the location, you can press the ‘[X]‘ in the edit column to remove the entry. If a price has changed, you can press the ‘[Edit]‘ button to update the price information.

Since I need to give the App a thorough test, accounts currently have no limit on the amount of locations and beers they can map. In the future I will be using a contribution system that disperses daily credits based on positive contributions to the Beer App. I am already logging which users contribute information. I plan on using this to display a ‘leader board’ of contributions to the App. This way, I have the option to run contests based on user contributions.

Where does the App go from here? Assuming there are no issues with the new features, I am going to spend some time cleaning up the ‘look’ of the App. My philosophy is functionality over form. If the App is functioning well enough, it’s time to ‘pretty up’ this sow.

The other feature I plan to have implemented soon is the ‘Honest Pint’ filter. I will be using information from the Honest Pint Project to flag locations as Certified Purveyors of Honest Pints. You will be able to check a box if you want to search for only Honest Pint locations. These locations will also have a unique ‘push pin’ on the map and an Honest Pint tag on their information page.

After that, I will be working on getting the interface finished for letting users create and manage Beer and Brewery entries. Along with this, I will setting up a system for users to leave comments on Beer, Brewery or Location pages. When all that is complete, I will start working on the comparative rating system that started this whole project. Funny how things get side tracked some times.

So, get to mapping! You know it makes you feel all good inside. If you do have any problems either creating an account or using the mapping functions, shoot an E-mail to kevin@beerandcoding.com.

Cheers!
Kevin

At the end of my last Beer App Update, I mentioned that I was working on adding some JQuery to the Beer App search page. JQuery is a Javascript Library that makes interactions between Javascript and HTML easier.

Why do I care about HT-what’s it and Java-stuff interactions?

Well, you probably don’t…or need to. The important thing is, they do let me do some nifty stuff with the Beer App. JQuery supports AJAX calls, which I have used to create auto-suggestion drop downs for the Beer Name and Brewery Name fields in the Beer App. It also allowed me to create dynamic drop downs for the Sub-Style and State/Province fields. This post is going to cover how I am leveraging JQuery in the Beer App to accomplish this. If you just want to see what updates have been made to the App and the information about user content, skip ahead to the last few paragraphs.

For those of you still reading, If you launch the Beer App in another tab, you can see what I am talking about. If you are playing along, start typing ‘porter’ in the Beer Name field. You should notice that after you pressed ‘r’, you got a drop down displaying several beers that have ‘por’ in the name. Clicking on one of them should have populated the Beer Name field with the name of the beer and the Brewery Name field with the name of the brewery. If not, I am horrible at this programming stuff and you should probably stop reading this.

If it did work, here is why. I added an ‘onkeyup()’ event to both the Beer Name and Brewery Name fields. So, when you type a letter into either field and release it, I send the information to a Javascript function in the page header.

<input id=”field_id” onkeyup=”some_javascript_function(this.value)” name=”field_name” type=”text” />
<div id=”suggestionsArea” class=”suggestionsBox” style=”display: none”>
<div id=”suggestionsListItem” class=”suggestionList”></div></div>

Under the input field, there are a couple DIV tags. The first DIV will hold the results of our suggestions query. The second is where the individual results will be placed. Since the second DIV is nested inside the first, and the first is set to not be displayed, neither will be visible when the page loads.

On each key up, a Javascript function is passed the data in the field. The function checks to see if the data is three or more characters long. If it is, the function uses the calls an external PHP script that will perform the MYSQL query. The function also contains a call back function so the query results can be returned from the PHP call to the Javascript. It looks a little something like this:

function lookup(searchString) {
if (searchString.length < 3) { $j(’#suggestionsArea’).hide(); } else { $j.post(”../lookupscript.php”, {searchString: searchString}, function (html) { if (html.length > 0) {
$j(’#suggestionsArea’).show();
$j(’#suggestionsListItem’).html(html);
} else {
$j(’#suggestionsArea’).hide();
}
});
}
}

If you noticed, I am using ‘$j’ instead of ‘$’ to reference JQuery. This is due to Wordpress being a strange and fickle mistress. Wordpress already has the ‘$’ name reserved. This means that you will need create a new JQuery variable.

var $j = jQuery.noConflict();

You can use whatever name you want for the variable, ‘$fuckyouwordpressforstealingmyvariable’ would also be appropriate.

Any way, back to the Javascript. The function is firing ‘lookupscript.php’ and providing a call-back function for HTML returned by the PHP script. If the Javascript is returned something (if (html.length > 0) {), the DIV ’suggestionsArea’ is made visible and the HTML is inserted in the ’suggestionsListItem’ DIV. If nothing is returned in the call-back, the ’suggestionsArea’ DIV is hidden. This prevents an empty box from appearing when the user has entered data in the field that does return any results from the database.

In the PHP script, we retrieve set up the connection to the database, retrieve the user data that we passed in with JQuery, execute the MYSQL query and pass the results back to the Javascript function.

<?php

?>

With this script, we pull in the user entered data passed from the Javascript function and place it in the ‘$searchString’ variable. I decided to add a ‘%’ to the end of ‘$searchString’. This way, I can use ‘LIKE’ in the MYSQL statement and query for things that start with the user’s data. Another ‘%’ can be added to the front of ‘$searchString’ to search the database for all entries that contain the user’s data, rather than start with it.

$searchString = ‘%’ . $searchString . ‘%’;

Like the rest of BeerandCoding, I am using the the Prepare, Bind Params, Execute, Bid Results method of issuing the MYSQL query. This prevents malicious code from being passed into ‘$searchString’ and executed by the query.

Once I have the results, I issue a Fetch to retrieve them. For each result, I echo a list item back to the Javascript function. The list item has it’s own ‘onclick()’ event that will put it’s value into the search field. I use the ‘addslashes()’ function to preserve any special characters in the result’s name like apostrophes that would normally be executed as code.

function fill(selectedItem) {
$j(’#field_name’).val(selectedItem);
$j(’#suggestionsArea’).hide();
}

The examples above are basically how the Brewery Name field works. The Beer Name field is slightly different. For that field, I am querying for both the beer’s name and it’s brewery. I am also echoing both those values in the link item and filling both Beer Name and Brewery Name when a user makes a selection from the Beer Name suggestion list. I decided to do this because there are already many beers in my database with the same name. Like the example we started with; typing ‘por’ would generate a list of several beers named ‘porter’. If I didn’t display the brewery in the suggestion list, the user wouldn’t have any idea which porter they were clicking on. Also, since the user is making a choice knowing the brewery, it makes sense to fill in the Brewery Name field.

I didn’t mention CSS, but it is being used to style the form. In the page header, I have set up CSS for all the DIVs. This is what highlights the suggestions in the drop down when they are moused over. It is also what removes the bullets from the front of the list items in the suggestion list.

Ok cheaters, here is where you can start reading again.

To wrap up, the updates to the Beer App are; auto-suggestion lists for the Beer Name and Brewery Name fields and Sub-Style and State/Province drop down that our built dynamically from the Style and Country lists. I have also had time to get a little more information in the database. The Beer App currently sits at 1600+ beers from 155 breweries.

Now, here is the part you were probably looking for when you started reading this post. I am on the verge of being able to take user content. If you haven’t noticed, there are little ‘login’ and ‘new account’ links at the top of the right column. Currently, accounts do not give you access to anything, but they do let you reserve your user name. I will be starting a test next week where some individuals who have registered accounts will be given rights to add beer locations and map beers in the database to locations. This will allow me to test user submitted content without destroying my database if there is a problem. The ability to submit beers and breweries will come shortly after I have determined the user submission process is bug free.

In the next few days I will get another tutorial up on how the dynamic drop downs work that are used for Sub-Style and State/Province. I was going to go over that here, but this is post is plenty long I need to get some stuff done before i head off to the Fresh Hop Beer Tastival that is here in Eugene today.

Cheers!
Kevin

Warning: Most of this article deals with Floats, as in Floating Point numbers, not beer floats or parade floats. If you just want to see the status of the Beer App, skip down to the last paragraph.

I have been working this weekend on the user content section of the Beer App. I just finished setting up the page that will let users add new beer locations to the database. I decided to work on this page first for purely selfish reasons. I needed to add more locations for testing purposes and was getting tired of manually geocoding and inserting them into my database.

I whipped up a quick page that had all the necessary input fields; Name, Address, Phone Number, Website, Location type (bar/restaurant, bottle shop, brewpub, etc). The code behind page was pretty simple. First, I take the address and run it through the geocoder built into the Google Maps API. Once I have a latitude and longitude, I reload the page, passing it these values. When the page reloads, if a latitude and longitude were passed in, I check the coordinates against entries in the database to make sure the location is not already on file. I use the location’s coordinates instead of the name or address for the comparison since users may spells names or format addresses differently. If the location is on file, a message returns that the location already exists. If not, the location is added to the database and a message is displayed stating so. Before this feature goes live, when a location exists, the user will be shown the location’s details and given the option to make changes if the information on file is not correct.

Now it was time to test my new routines. I tried adding Beer Nuts, a bottle shop here in Eugene, to the database. The page reloaded, telling me that the location had been added. I opened up phpMyAdmin and browsed the location table. Sure enough, Beer Nuts was sitting at the bottom of the table. I went back to the page and hit the reload button. With any luck, I should receive a message that Beer Nuts already exists.

Shit, I got the “Location has been added” message again. A quick check of the database confirmed that I did in fact have two identical entries for Beer Nuts occupying the last two rows of my table. How the hell did that happen? I put in some debugging code and fired the page again. Damn, a third entry with identical information. I went back to the browser and looked at the URL. Ah, now there’s my problem. The geocoder is returning latitude and longitude to seven decimal places and I am only storing six decimal places in my database. I went back to my database to change the latitude and longitude fields. This is where I ran into my next problem.

On the recommendation of Google’s API tutorial, I set the fields up as Floating Point Numbers. Why is this an issue? You can read the Wiki article for a full explanation, but the simple answer is that a Floating Point number is an approximate representation of a number. This approximation is usually not an issue, but it does throw a wrench in directly comparing two Floating Point numbers.

My first thought was to change the database fields from Floating Point to Decimal. This solution creates a couple problems of it’s own. First, on versions of MySQL prior to 5.0, Floating Point math is still used to compute Decimal values. Second, if the hardware running my site ever changes, I run the risk that the converted geocoder values will no longer match the Decimal values stored in the database. This is due to Floating Point math varying based on the CPU performing the operations. Finally, unless you are using a 386 or older computer, your CPU has a separate section for Floating Point arithmetic. This makes Floating Point operations much faster.

So, it looked like I was stuck with Floating Point numbers. It was time to accept it and make the best of the situation. Knowing that the geocoder output was never going to match the database values, I needed to set up a small range. I added code to round the latitude and longitude values from the geocoder to six decimal places. Next, I created high and low latitude and longitude variable that are +/- .000005 of the rounded latitude and longitude values. This created a range of .00001 for both the latitude and longitude, or 1.11 meters. This should be a precise enough to not return false positives. I changed my select statement to look for locations where the latitude and longitude are between the high and low latitudes and longitudes.

I went back to my browser and tried adding Beer Nuts again. Success! This time I received the page telling me that the location already existed. I tried adding a few more locations that were already in the database. Each time, the query correctly detected the location in the database and did not insert an addition entry.

Now that my page was working correctly, I was finally able to add some new locations for testing. Currently, the following locations in the database:

Sam Bond’s Garage – Eugene, OR
Oak Street Speakeasy – Eugene, OR
Eugene City Brewery – Eugene, OR
High Street Brewery & Cafe – Eugene, OR
East 19th Street Cafe – Eugene, OR
Hop Valley Brewing Company – Springfield, OR
Bailey’s Taproom – Portland, OR
Belmont Station – Portland, OR
Concordia Ale House – Portland, OR
Saraveza – Portland, OR
Green Dragon – Portland, OR
Buckeye Beer Engine – Lakewood, OH
The Drafting Room – Spring House, PA
The Drafting Room – Exton, PA

Yes, if you skipped the top part, this is where you pick it back up.

With the Float issue out of the way, I can now get back to work on finishing the other user content submission pages. I have also made some changes to the layout of the Beer App. After receiving a few e-mails from people having issues with the search, I removed the Basic option. I also temporarily removed the Sub-Style option. Once I finish the JQuery routine, Sub-Style will automatically be populated with valid options when a Style is chosen. The database is also up to 1500+ beers from 140+ breweries. Hopefully I will be ready within the next couple weeks to hand out some beta accounts.  Until then, I will keep posting progress updates.

Cheers!
Kevin