The following queries are needed for the database managed as well as for the page population.
- To create a Form to populate the English translations, a query that will retrieve only those rows where the English title, the English text and English name are empty because the translation is needed.
SELECT RECIPES.RECIPE_ID, RECIPES.RECIPE_NAME, RECIPES.INGREDIENTS,
RECIPES.RECIPE_TEXT, RECIPES.ENGLISH_NAME, RECIPES.ENGLISH_INGREDIENTS, RECIPES.ENGLISH_TEXT,
RECIPES.DATE_ENTERED, CATEGORIES.CATEGORY_NAME, READERS.LAST_NAME, READERS.FIRST_NAME
FROM READERS INNER JOIN (CATEGORIES INNER JOIN RECIPES ON CATEGORIES.CATEGORY_ID
= RECIPES.CATEGORY_ID) ON READERS.READER_ID = RECIPES.READER_ID
WHERE (((RECIPES.ENGLISH_NAME) Is Null) AND ((RECIPES.ENGLISH_INGREDIENTS) Is
Null) AND ((RECIPES.ENGLISH_TEXT) Is Null));
- To create a Form to enter new recipes, a query that will join the RECIPES, READERS, and CATEGORIES tables where they share the keys
SELECT RECIPES.RECIPE_ID, RECIPES.RECIPE_NAME, RECIPES.INGREDIENTS,
RECIPES.RECIPE_TEXT, RECIPES.ENGLISH_NAME, RECIPES.ENGLISH_INGREDIENTS, RECIPES.ENGLISH_TEXT,
RECIPES.DATE_ENTERED, CATEGORIES.CATEGORY_NAME, READERS.LAST_NAME, READERS.FIRST_NAME
FROM READERS INNER JOIN (CATEGORIES INNER JOIN RECIPES ON CATEGORIES.CATEGORY_ID
= RECIPES.CATEGORY_ID) ON READERS.READER_ID = RECIPES.READER_ID;
- To create a form for unanswered requests
SELECT REQUESTS.REQUEST_ID, REQUESTS.REQUEST_TEXT, REQUESTS.ENGLISH_TEXT,
REQUESTS.DATE_ENTERED, REQUESTS.DATE_ANSWERED, REQUESTS.ADDED_TO_PAGE, REQUESTS.LANGUAGE,
READERS.READER_ID, READERS.LAST_NAME, READERS.FIRST_NAME, READERS.EMAIL
FROM READERS INNER JOIN REQUESTS ON READERS.READER_ID = REQUESTS.READER_ID;
- To search for a specific request when an answer has come will use the same query but will add a WHERE clause depending on what we are searching. As an example:
SELECT REQUESTS.REQUEST_ID, REQUESTS.REQUEST_TEXT, REQUESTS.ENGLISH_TEXT,
REQUESTS.DATE_ENTERED, REQUESTS.DATE_ANSWERED, REQUESTS.ADDED_TO_PAGE, REQUESTS.LANGUAGE,
READERS.READER_ID, READERS.LAST_NAME, READERS.FIRST_NAME, READERS.EMAIL
FROM READERS INNER JOIN REQUESTS ON READERS.READER_ID = REQUESTS.READER_ID
WHERE (((REQUESTS.REQUEST_TEXT) Like "*menta*"));
FOR THE WEB SITE PAGES THE FOLLOWING SQL statements ARE NEEDED to get the right recordset
- SQL statement by recipe name for a search page by recipe name
SELECT CATEGORY_NAME, LAST_NAME, FIRST_NAME, EMAIL, RECIPE_ID,
RECIPE_NAME, INGREDIENTS, RECIPE_TEXT
FROM CATEGORIES, READERS, RECIPES
WHERE RECIPES.READER_ID=READERS.READER_ID AND CATEGORIES.CATEGORY_ID=RECIPES.CATEGORY_ID
AND RECIPES.RECIPE_NAME like '%VAR_RECIPE_NAME%'
- SQL statement by ingredients for a search page by ingredients
SELECT CATEGORY_NAME, LAST_NAME, FIRST_NAME, EMAIL, RECIPE_ID,
RECIPE_NAME, INGREDIENTS, RECIPE_TEXT
FROM CATEGORIES, READERS, RECIPES
WHERE RECIPES.READER_ID=READERS.READER_ID AND CATEGORIES.CATEGORY_ID=RECIPES.CATEGORY_ID
AND RECIPES.INGREDIENTS like '%VAR_INGREDIENTS%'
- SQL statement by category to populate the master page by category
RECIPE_NAME, INGREDIENTS, RECIPE_TEXT
FROM CATEGORIES, READERS, RECIPES
WHERE RECIPES.READER_ID=READERS.READER_ID AND CATEGORIES.CATEGORY_ID=RECIPES.CATEGORY_ID
AND CATEGORY_NAME='VAR_CATEGORY'