This is merely a stand-in page. Please Click Here to get to the readMe file.
Please Click here to populate the tables with data if they are not populated.
You may need to refresh the page.
Please Click here to get to the home page of the site (note, Stored Procedure is on this page)
Stored procedure - getDishResults
Written by: Melissa Brown, 4/15/2023
getDishResults is a stored procedure which takes in three parameters:
- destID - this is the ID for the destination, or city, the user wants to search in
- restrictionList - this is a string of numbers (e.g. 1,3,5) which correspond to dietary needs somehow has
(e.g. 1=gluten free, 3 = garlic free, 5 = dairy free). This is passed as a string because it goes in the
IN () clause of the SQL statement, e.g. IN (1,3,5)
- countResults - this is the number of restrictions a user has. Essentially, the database will search for
all dishes in a city which are amenable to a certain diet. Let's say 1,3,5 in this case (gluten free,
onion free, and dairy free.) If we search for everything with the clause IN(1,3,5), the results will show dishes
that shouldn't show up- e.g. dishes that are gluten free, but not onion or dairy free. So, in the resulting query,
the restritions are counted and grouped by dish. Only dishes having greater than or equal to countResults will
show on the list for the user. That way, only dishes that have 1, 3 AND 5 at least in one dish will
show up.
Tables with sample data for Gastrono-Me
user table
state table
destination table
| destinationID | city | stateID |
|---|
| 1 | Minneapolis | 1 |
| 2 | Rochester | 1 |
| 3 | Chicago | 2 |
restrction table
| restrictionID | restrictionName |
|---|
| 1 | glutenFree |
| 2 | diabetic |
| 3 | garlicFree |
| 4 | onionFree |
| 5 | dairyFree |
| 6 | kosher |
| 7 | vegan |
| 8 | vegetarian |
restaurant table
| restaurantID | restoName | streetAddress | restoZipCode | destinationID | restoLink |
|---|
| 1 | La la Homemade Ice Cream and Luncheonnette | 3146 Hennepin Ave | 55408 | 1 | https://www.lalaicecream.com/ |
| 2 | McDonald's | 5500 Bandel Rd NW | 55901 | 2 | https://www.mcdonalds.com/us/en-us/location/MN/ROCHESTER/5500-BANDEL-RD-NW/12815.html?cid=RF:YXT:GMB::Clicks |
| 3 | Fittingly Delicious | 3939 W Irving Park | 60618 | 3 | https://www.fittinglydelicious.com/ |
userSavedCity table
| destinationID | username |
|---|
| 3 | mimosab |
| 3 | aarancor |
| 1 | nogluten |
dish table
| dishID | restaurantID | dishName | dishLink | isCustomizable |
|---|
| 1 | 2 | Burger No Bun | https://www.mcdonalds.com/us/en-us/product/hamburger.html | 1 |
| 2 | 1 | Luncheonette Salae | https://www.lalaicecream.com/food | 1 |
| 3 | 3 | Chicken Teriyaki | https://fittinglydelicious.smartonlineorder.com/ | 0 |
| 4 | 1 | diabetic vegan onion free | fakewebsite | 1 |
| 5 | 1 | kosher garlic | fake website2 | 0 |
| 6 | 1 | vegetarian | fakeweb3 | 1 |
| 7 | 2 | diabetes dairy vegetairan | fakeweb4 | 0 |
| 8 | 2 | glutenFree diabetes onion vegan | fakenewz | 1 |
| 9 | 2 | garlic kosher | fakefakefake | 1 |
| 10 | 3 | onion | superFake.com | 1 |
| 11 | 3 | glutenFree garlic onion vegetarian | notReal.com | 0 |
| 12 | 3 | diabetes dairy | faux.com | 1 |
userSavedDish table
| dishID | username |
|---|
| 2 | mimosab |
| 3 | aarancor |
| 1 | nogluten |
dishMatchesDiet table
| dishID | restrictionID |
|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
| 2 | 1 |
| 2 | 3 |
| 2 | 4 |
| 2 | 5 |
| 3 | 2 |
| 4 | 2 |
| 4 | 7 |
| 4 | 4 |
| 5 | 6 |
| 5 | 3 |
| 6 | 8 |
| 7 | 2 |
| 7 | 5 |
| 7 | 8 |
| 8 | 1 |
| 8 | 2 |
| 8 | 4 |
| 8 | 7 |
| 9 | 3 |
| 9 | 6 |
| 10 | 4 |
| 11 | 1 |
| 11 | 3 |
| 11 | 4 |
| 11 | 8 |
| 12 | 2 |
| 12 | 5 |
userSavedRestriction table
| restrictionID | username |
|---|
| 3 | mimosab |
| 4 | mimosab |
| 5 | mimosab |
| 2 | aarancor |
| 1 | nogluten |
recCity table
trackRecs table