I was recently inspired to refresh my PHP and MySQL abilities, so I assigned myself a little bit of a programming challenge. Part of my inspiration also stemmed from never wanting to choose a place to go out to eat with friends, so I crafted the challenge to create a web application that would allow me to randomly choose a restaurant that would please all members attending. What I came up with in the end, I like to call Friendstaurant.
Friendstaurant allows you to:
- Add Friends and state any restaurants they dislike (or don’t want to go to)
- Add Restaurants, their location, a URL, and notes
- Add Tags and apply them to restaurants such as fast food, sit-down, or the city they’re in
This article will show you a little bit of the structure behind the web app. I was hoping to distribute the source, but I *dramatic pause* haven’t quite done my homework in regards to preventing SQL injection, so I won’t be making the source generally available but you can request a copy in the comments.
The database back end of Friendstaurant consists of five tables. Three of the tables contain the prime information. One table lists Restaurants: their ID, their name, URL, address, and notes. Another table lists Friends: their ID and their name. The third table lists Tags: their ID and their name.
Tags would apply to restaurants and there would also be an association between Friends and Restaurants to indicate any restaurants a person disliked. To record these associations, I wasn’t quite sure of how to proceed. I read a couple of interesting articles on the various tag schemas, here and here. Finally, I decided based on the low number of records my database would end up holding, I could simply create two tables that would be index-less and they would simply record per row the two IDs that were associated. For example, if User1 dislikes Restaurant3 than the database would hold 1 in the UserID column and 3 in the RestaurantID column. Then, in order to look up a user’s dislikes, I just query that table where UserID = that user. The same approach also works for recording what tags apply to which restaurant. An index-less table holds two columns, TagID and RestaurantID, and can be queried to generate a list of restaurants with a particular tag or for the tags that apply to a particular restaurant.
The web interface aspect of Friendstaurant has two different goals to accomplish: allow one to generate a pool of acceptable restaurants based on various conditions and allow the administration of the data inside Friendstaurant like restaurants, tags, people, and their dislikes. Both of these tasks are accessible from the front page, which looks like:
We’ll start with the administration tasks because it’s more mundane (but necessary). From the home page, you have the option to Add a ______ or Edit a _______. This is true for Restaurants, Friends, and Tags. When you click to Add a new item, you’ll also be influencing the other categories because as a matter of convenience I added the ability, for example, whenever you add a new restaurant to also state which tags should apply to that restaurant. When you add a new Tag, you can immediately select which restaurants should have that tag applied. When you add a new friend, you can select any restaurants from the database that the user doesn’t like.
Editing an item is very similar to its original creation with one additional step. Before getting to a screen like the one above, a list of all restaurants, friends, or tags in the database is available. You click on the link of the item you wish to edit and then you’ll reach a form like above (for restaurants, obviously) with the information from the database filled in. You can make any changes you want to the Name, Address, notes, URL, or tags applied and then hit the Submit button. Through PHP composing the MySQL query, the new information is written to the database.
At this point, with a bigger system, you would probably be expecting a login system. A thought might be that others should not be able to edit my “profile” and change what restaurants I dislike. First of all, the system is small and protecting that data is not that important. People would be able to deduce any restaurants you dislike from the Select a Restaurant process and a lot of times I know restaurants that my friends or family members dislike so I can update that information as I add restaurants. More importantly, it just adds a higher level of complication. What if my parents are coming into town and we’re going out to dinner. I don’t want to have to make them sit down and create passwords and profiles when I could do most of that myself.
Selecting a Restaurant
Choosing the restaurant is where a lot of the magic of PHP finally comes into play. So far it’s mostly been playing the role of composing mundane SELECT and INSERT queries. The process of selecting a restaurant works by building a pool of acceptable restaurants and then randomly selecting from that pool. The pool of acceptable restaurants starts off with all restaurants in the database and then removes restaurants based on 3 simple questions. The first question asks who is attending. Of those people that you select, any restaurants that they dislike are removed from the pool. You can see the number of restaurants that will be removed per person to the right of their name. You can also click on that number to be taken to the Edit Friend page in case you need to update their disliked restaurants.
The next question asks for any tags you’d like the restaurant to have. If you select Fast food, Lunch, and Champaign, any restaurants that do not have all 3 of those tags will be removed from the pool. To the right of the tag name, you can see the number of restaurants with that particular tag. The third question asks for any recent restaurants you might have visited. Since most people don’t like going to the same place to eat consecutively, by selecting the checkbox in front of a restaurant it will directly be removed from the pool.
The pool is generated in an array using a combination of PHP functions like array_diff() and array_intersect(). Once the pool is calculated, the number is randomly generated using PHP’s rand() function that points to which cell in the array contains the information regarding the chosen restaurant. If you don’t like the final pick, the pool of compatible restaurants is printed at the bottom of the page or you can also “re-roll” by hitting the Re-Do button. This button is just the end of a form that resubmits all the information passed from the previous page back into this page for the restaurant to be recalculated. For this function to behave consistently, resubmitting the page was necessary due to the way POST data works. If I were to make the page Ajax-driven and pick a new random array cell, I could get away without the full page reload, but at this point and low volume, it’s hardly a hit on the server.
Your results would look like this:
The highlighted restaurant is at the top and the pool of compatible restaurants is printed at the bottom.
Now you know where to go eat thanks to the indecision-enabling web app: Friendstaurant!