[MUSIC PLAYING] 

DAVID J. MALAN: Hello. Let's take a walk through Problem Set 8 Mashup, which is going to challenge you to draw upon elements for Google Maps with elements from Google News and mash them together into a web applet that allows a user to search a map for news local to specific towns, cities, and zip codes. To do this, we're going to integrate some HTML, CSS, PHP, SQL, JavaScript, and a technique generally known as AJAX in order to create this immersive user experience. 

Let's first for Google Maps itself. This, of course, is perhaps a familiar interface. But it turns out that Google Maps also provides an API-- application programming interface-- via which you can take elements of Google Maps and integrate them into your own applications. Indeed, throughout this process, you're going to find a couple of URLs particularly helpful that are mentioned in the specification for Problem Set 8, specifically this Getting Started Guide or the Developer's Guide for Google Maps API Version 3 as well as the Google Maps JavaScript API v3 reference, which is a bit more arcane to read but actually has all of the lower level details about what functions or methods and objects and properties and events actually come with the API, very similar in spirit to [INAUDIBLE] pages. 

Now if we take a look at Google News, you'll perhaps see a familiar interface here. But it turns out you can also search Google News for specific geographies via an HTTP parameter called geo. In fact, if I zoom in up here, you'll see that I'm at news.google.com/news/section?geo=02138. And, indeed, if I zoom out, you'll see that I'm looking at a page with a whole bunch of views about Cambridge, Massachusetts. 

Meanwhile, if I actually change the URL not to be a zip code like this, but something a little messier like Cambridge, +Massachusetts, where the plus is the way you encode a space character in a URL and hit Enter, you'll see that I actually see almost the same news. Perhaps it's a little bit different because Cambridge actually has multiple zip codes. Now how would I know that and, in fact, how could I somehow tie cities and towns to zip codes in case I want to allow the user to look up either? Well, it turns out that there's a website out there called geonames.org which is an initiative to have a freely available database of all sorts of geographic information, not only for the US, but also for other countries as well. In fact, if I go to this URL here, which is also mentioned in the problem set specification, you'll see it three listing of a whole bunch of zip files any of which can be downloaded by you. In fact, for this problem set you're going to download us.zip. Now within this file, is a whole bunch of data in text format. The files is very similar to a CSV-- Comma Separated Values file-- but it actually uses tabs to demarcate fields. Now, meanwhile, if you look here at what I've highlighted, the fields in this file are going to be things like country codes, postal codes, place names, and then, in some form or other, states and counties, communities, and more. In fact, I've already downloaded this file in advance. Let me go ahead and open it here-- us.text-- and, indeed, you'll see if I scroll down to line 16,792 you'll see a few records for Cambridge, Massachusetts and its various zip codes. What you also see there is the county, some numbers that I don't really understand, but also all the way on the right, some GPS coordinates-- latitude and longitude. This is great because one of the features of Google Maps API is the ability to detect where you are geographically in terms of GPS coordinates. 

Now let's begin to figure out how to start tying these things together. We've given you a whole bunch of distribution code, as well as MySQL database. In fact, if I pull a phpMyAdmin having already imported, as you soon will, pset8.SQL, you'll see a MySQL table that looks like this, an ID field, country code, postal code, place name and more. The types of all of those columns I derived simply by reading the readme.text file here that specified whether a field is an integer, or varchar or the like. 

So we've created that table for you and given you the SQL commands to execute to create that table in your own database, but there's actually no data in it yet. Rather, you're going to have to download us.zip or any country's zip file from that URL there. And then you're going to have to write a command line script in PHP that's going to open up that text file, iterate over its lines, and then for each of those lines do an insert into that places table in your MySQL database. So at the end of this process, you'll have run that script ultimately just once in theory. In reality you'll probably run it a bunch of times while trying to fix various bugs. 

Ultimately, you're going to have a really big database with thousands and thousands of geographic rows. Then you're going to put that import script aside once it's working and your database is nice and correct, and then you're going to move on to actually implementing the mashup itself. The mashup is going to look a little something like this. At mashup.cs50.net, we have a staff solution that looks a little something like this. Indeed, if I click on this newspaper icon for Cambridge, Massachusetts, you'll see a spinning icon briefly and then an ordered list, a bulleted list of articles related to Cambridge, Massachusetts. If I click on Charlestown, Massachusetts, I'll see the same for that town. And If I click on Watertown, Massachusetts, there might not be any news of from Watertown, so you'll see something like slow news day. 

Now, meanwhile, at top left are some familiar Google Maps controls to let you zoom out, pan up, down, left, and right, but also a search box that we put there. So if I search for, frankly, the only other zip code I know, 90210, we'll actually see Beverly Hills, California. When clicked it leads me to California and a whole bunch of news about Beverly Hills. Now notice, too, what happened there. If I this time search for 02138 or even Cambridge comma Massachusetts or some variant thereof, you get a little autocomplete dropdown. Now this is using a plugin for a library called jQuery, and that plugin is called typeahead. We simply read through the documentation, downloaded the .js file integrated into the distribution code so that you ultimately can write the code that fills that dropdown menu with the auto selections or the auto suggestions. 

Now the distribution code, though, that you received doesn't do nearly as much. You get the Google Map embedded, and you get the controls the top left, and you get the search box. But if I type something like 02138, no places are found yet. So that's going to be one of our goals here. Moreover, if you take a step back and look at the map itself, there's no news whatsoever. Even if I click and drag, no markers actually appear for news because that challenge is left for you as well. 

Let's take a look then at the distribution code. Once you've downloaded pset8.zip and unzipped it into your vhost directory in the CS50 Appliance, you'll see these directories here inside. Bin-- which generally stands for binary for executable programs-- includes, as in pset7, some PHP files that other files include, then public, which is the files that need to be publicly accessible to a user with a browser. Let's take a look in the bin directory, and we'll see that there's a file there already called Import. If we open this with gedit, we'll see that, unfortunately, there's not much there. All that is there, though, is a shebang at top which specifies which interpreter-- in this case PHP-- should be used to actually execute this file. But then where it says TODO is where you're going to need to write some code that probably requires the config file that's in the includes directory as we've done before with PHP files. And then you're going to have to somehow open up us.text which you presumably have unzipped already. Then you're going to have to iterate over the lines in that file, perhaps using some of the functions suggested in the specification. Then insert each of those lines into MySQL database by using the query function, which we've again provided you with-- or at least a variant thereof in functions.php, which we'll see in just a moment. 

Now let's close import and go back to our directory and this time go into includes. And if I do ls there, you'll see three files quite like Problem Set 7. And let's take a quick look, for instance, at config.php. In there, is fewer lines than before, and it seems this file includes constants.php and functions.php. We're using a slightly different technique this time around to actually specify that these files are relative to the current directory __ DIR__ represents whatever directory this file, config.php, is itself in. So this is a more explicit way of specifying what other files you want to require. 

Now if I close this file and open up constants.php instead, you'll see a file very reminiscent to Problem Set 7's as well, albeit with a different database called pset8. Finally, in functions.php, we'll see just one function this time called query. This is almost the same except we handle errors this time around a little bit differently, but it's usage is the same as in problem set seven. 

Now let's go back into our pset8 directory, go into public, and in there if I do ls, you'll see this-- articles.php, index.html, search.php, and update.php-- all files. And then the css fonts, img, and js directory quite like pset7. 

Let's take a look at index.html, which is going to be really the entry point to the smashup. Now in index.html, you'll see a whole bunch of link elements in the head, specifically, for bootstrap for our own CSS followed by a whole bunch of script tags for things like the maps, API itself, a special marker with label utility that we mentioned in the specification is available to you, jQuery itself, bootstrap itself, and another library called underscore which we talk about in the spec. Underscore.js like jquery.js is a JavaScript library that has a whole bunch of functionality that a lot of people in the world wish existed in JavaScript itself. So all of these are actually quite popular. We've also mentioned typeahead which is the library that does that autocomplete dropdown and finally a link to our own JavaScript. 

Meanwhile, and perhaps thankfully, this mashup is driven by relatively little HTML down here at the bottom. Notice that we've specified a div in our body of class-container fluid. This, per bootstrap's documentation, just means that this div is going to fill the viewport or the browser's window fully. 

Meanwhile, below that we have a div that's opened and immediately closed with the unique ID of map canvas. This now is from Google Maps documentation for its API, whereby I simply need to have an empty div into which to inject, ultimately, an actual Google Maps. But more on that in just a bit. 

Finally, there's a form inside of here which implements the text box up top left in our interface for searching. Notice that we've used a bit of bootstrap here too-- things like form-inline and form-group. We've given the former unique ID of form. And then, ultimately, I actually have an input type, which is pretty familiar, whose ID is q. Just a convention. Q for query-- could have been called anything. And then the placeholder, meanwhile, is city, state, and postal code which you might recall seeing in our mashup demo earlier. Let's close this file. 

Now take a look at the PHP files that await and then the JavaScript files. In our PHP files, we've already implemented for you, for instance, updates. Update.php-- we won't spend a huge amount of time on here-- in a nutshell is the file that our JavaScript code is going to contact via AJAX that asynchronous technique that's built into JavaScript these days that's going to allow us to ask update.php for more information. 

Specifically, anytime the user drags the map or performs a search that jumps the user to another location, our JavaScript code, as we'll soon see, is going to call update.php and ask for 10 or so markers within the viewport based on the GPS coordinates of the top and bottom corners of that map. We can then repopulate the map now that the user has moved the screen in order to see 10 probably new markers for different towns. Meanwhile, this file is ultimately going to execute a SQL query against our database table called places which is going to return those 10 or fewer locations. 

Meanwhile, in articles.php, is another file we've written in its entirety. It is very similar in spirit to Problem Set 7's LOOKUP function, which contacted Yahoo Finance for you. This file contacts Google News for you, ultimately grabbing a machine-readable version-- in something called RSS format-- of the news for Cambridge or Beverly Hills or whatever town you've searched for based on that geoparameter. We parse that RSS, which is just a type of markup language called XML, and then we actually return it to your browser and to your JavaScript code, specifically, in a format called JSON, JavaScript Object Notation. Now you'll see in the specification-- we point you at how you can actually see some of the JSON coming back-- that this functionality ultimately lets you populate those popup menus so that when you click on a marker in the map you actually see a whole bunch of bullets, each of which links to an article. 

Now let's take a look at one last PHP file which, fortunately, doesn't have much going on-- just a pretty big TODO. Right now this file declares an array called places. And then ultimately prints that array in JSON format-- pretty-printing it just so that things are easier to debug. Unfortunately, in the middle there is this TODO, which calls for you to search the database for places matching a geo HTTP parameter. 

And, indeed, this is going to be one of your challenges-- to implement this functionality here so that when you contact this file with a URL like search. php?geo= something, your code will ultimately return a JSON array of all of the places in your database table that match that input. So if the user types in Cambridge, your file here search.php should ultimately return a JSON array for all of the matches for Cambridge, which might be in Massachusetts but could be even anywhere else. 

Lastly, let's take a look at two files that are static ultimately-- your CSS file and your JavaScript file. If I go into our CSS directory, there's a whole bunch of files there, but most of them are libraries. I'm going to take a look, specifically, at styles.css, which is our own global CSS that's going to stylize this whole mashup. I'll leave it to you to read through the comments herein, but, in a nutshell, this is the CSS that ensures that our mashup, by default out of the box, looks exactly the way we want it-- with the map filling the view port and with the search box up at the top left. We've also taken the liberty of stylizing that typeahead dropdown menu a little bit as well. 

The most important file perhaps for this problem set is this last one, scripts.js. Inside of your JS directory is even more files. All of them are library files except for this one, scripts.js. If we open this up, let's take our final tour through the functions that are built into this file for you and call attention to the TODOs that lie ahead. 

At the top of this file, are three global variables. One for a map, which is going to be a reference to our Google map. You can think of it sort of as a pointer. Meanwhile, we have another global variable called info, which appears to be storing the return value of a call to new google.maps.InfoWindow. JavaScript supports objects which are very similar in spirit to Struts. And what this line for our purposes is doing is creating a new info window in memory and then keeping around a reference thereto in a variable called Info. And in between those, meanwhile, is what appears to be an empty JavaScript array called markers. All of those newspaper icons, or you might choose another icon altogether, are going to be stored ultimately in this array so that we can very easily add to the map and remove them from the map. 

Now let's scroll down a little bit and whiz through the code that's going to be executed as soon as the DOM or document object model or the page itself is ready. Recall that this syntax here simply specifies that the following code should be executed only when the browser has finished loading everything else. 

We first declare a whole bunch of styles, which end up stylizing the map as per the spec. We then declare a whole bunch of options, which further customize the Google map that we're about to embed. We then use a bit of jQuery code, which is explained in a bit more detail in the spec, to grab that element, map-canvas that we so uniquely identified. And then this line here is what seems to magically give us a Google map inside of our own application, storing a reference thereto in that variable called map. 

Finally, down here we register what's called a listener. Think back-- way, way back-- to week zero in CS50 when we looked at Scratch and its support through a walk through for things called events and broadcasts. You might not have used it yourself, but it's a mechanism whereby a browser in this case can get our attention when it's ready to actually execute some code. In this case, it's going to listen to the map for an event called idle. This means that the browser has finished loading the Google map. At this point a function called configure should ultimately be executed. That function, configure, we'll see, is written by us. 

Now down here is a function that, unfortunately, is just a TODO add marker. Per the spec. you're going to need to write the code that actually adds a marker-- whether it looks like a newspaper, or a thumb tack, or something else-- to the Google map. Here now is that function called configure. I'll leave it to you to read through this in more detail, but realize that we add a bunch more Listeners so that we can execute code when the user clicks on and drags the map. We also have code in here that initializes that typeahead plugin so that the dropdown menu actually works. 

But let's focus on just a couple of places herein. Specifically, this to do here. I'll defer to the online documentation and the specification for how to fill in this TODO. But in a nutshell, this library typeahead allows you to pass in what's generally known as a template, which has some variable placeholders very similar in spirit to printf's %.*s. But in this case, the template per the spec allows you to specify what variables you want to inject from data that's come back from something like the PHP files that you've written that are emitting JSON output. 

Now down here realize that we are listening for typeahead selections when the user actually conducts a search and selects a value. This is how we're actually going to listen for that and execute some code as a result. Then we continue to configure the mashup just a little bit. And, ultimately, we call this function update. It updates the markers on the screen. More on that in just a moment. Meanwhile, there's a few small functions in here. One of which is hideInfo which simply closes the InfoWindow. Another function here, which ultimately won't be too long, remove markers. That's going to undo whatever your add marker function does. And then down here is search. And this one is interesting because we have written the JavaScript code that's going to talk to search.php on the server and get back some response. 

You, of course, will still need to implement search.php, but we've implemented the JavaScript code that's going to handle actually performing searches from that text box. In particular, notice that this function here, search, does call search.php by a method called get JSON, which we saw in lecture. And the syntax here is a little different from lecture in that we're using jQuery so-called promise interface. More on that in the spec. This simply means for our purposes now that there are two special functions we need to call with dot notation here immediately after calling get JSON. One is called done. One is called fail. You can think of these as the success handler and the failure handler just in case something goes wrong. 

Now let's look at the last couple of functions in this file. Down here is a function called showInfo, which shows info in one of those little info windows that pops up when the user clicks a marker. Down here further is that update function that we have implemented for you. It determines the bounds of the map. What are the GPS coordinates of its northeast and southwest corners here. We've prepared some HDP parameters here and then passed them ultimately to update.php, which we've also implemented for you. That ultimately gets back some JSON from the file called update.php and then removes any markers on the screen and then iterates over the data that's come back from update.php, which again is just a JSON array. And then it ultimately adds a marker for each of those places, handling failure or errors which might very well happen. 

Now just to give you a taste of how you might go about debugging this project, realize that I've opened in advance this tab here to this URL, pset8/articles.php?geo=02138. Now, again, articles about PHP we implemented for you so this isn't so much what you'll be using to debug, but rather the technique. Notice that I've searched for Cambridge's zip code here, and I've gotten back, indeed, a JSON array of JSON objects inside of which are two keys-- link and title. 

So this functionality works already for you. But this technique of manually going to a URL like this for something like search.php?geo=cambridge or 02138 or whatever the user has typed in should prove invaluable as you, yourself, try to figure out exactly whether or why search.php is working or not. 

Ultimately then, you have a few TODOs ahead of you. You're going to first implements that import script that reads in us.text into your database. You're then going to need to implement search.php so that it behaves exactly as specified. You're then going to want to focus on scripts.js and ultimately implements those couple of TODOs, including for configure and that template, add markers, remove markers, and then last, but not least, one personal touch. 

Once you have your mashup working quite like ours, the goal at hand is for you to add a personal touch to your mashup, whether it's aesthetic or functional. Take the mashup ever so slightly to the next level. So long as you push yourself beyond your familiarity with the spec itself and pick up one technique new, even if it's just something aesthetic like changing the layout of the map that you're using, the scope that we expect will be satisfied. That then is Problem Set 8 Mashup. Stay tuned for more in the specification and best of luck tackling this, your last CS50 problem set ever. 

[MUSIC PLAYING]