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
require(”include/your_db_info_file.php”);
$connection = new mysqli($host, $user, $pass, $db);
if(isset($_POST['searchString'])) {
$searchString = $_POST['searchString'];
if(strlen($searchString) > 0) {
$searchString = $searchString . ‘%’;
$query = “SELECT some_thing FROM some_table WHERE some_thing LIKE ? LIMIT 10″;
$stmt = $connection->prepare($query);
$stmt->bind_param(’s’, $searchString);
$stmt->execute();
$stmt->bind_result($search_result);
while ($stmt->fetch()) {
echo “<li onClick=\”fill(’” . addslashes($search_result) . “‘)\”>{$search_result}</li>”;
}
$stmt->close();
} else {
}
} else {
}
$connection->close();
?>
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