For my hermit crab site, I want to set up a basic database storing the names of foods that hermit crabs can or cannot eat.
Here's my idea of how it works: The database will contain the names of foods (eg. "Dried shrimp, "Apple") listed under particular categories (eg. "Fruit", "Meat", "Rich in calcium"). Some foods will be rich in multiple categories, so "Dried shrimp" could be listed under both "Meat" and "Rich in calcium". Each food will also have a small description attached to it (eg. for Apple, say "Slice up before serving").
Visitors will use a form to filter the data stored in the database. The form will consist of the following:
- A drop-down list with "Show All" and "Criteria". By default, have "Show All" highlighted. Every other part of the form except the submit button is faded or unable to be selected. However, if "Criteria" is selected, then the other sections of the form are able to be selected.
If "Criteria" is selected, then the following may be filled in:
- A drop-down list of the letters of the alphabet, so visitors can filter the data for foods beginning with a particular letter only
- Checkboxes labelled with the categories (eg. "Vegetable", "Fruit", "Meat", "Calcium rich"). This way, visitors can select as many categories as they want, as some foods will be listed under multiple categories (eg. "Tomato" will be listed under both "Vegetable" and "Fruit"). However, is it possible to add an "and" or "or" rule, where "and" requests only foods that are listed in both categories, while "or" requests any foods that match one, two or all the categories selected?
- A drop-down list with the choices of "Allowed" and "Forbidden" so visitors can filter the results for foods that are allowed to be offered or not
- Of course, a submit button labelled "Submit".
Once the visitors have selected their criteria, the filtered entries will show up in a table below the form. Names of foods that are in the "Allowed" category will be listed in green, while those "Forbidden" will be listed in red.
The form is to be used as a search function alone, not a tool for the user to insert data into the database.
How do I go about setting something like the above up? Sorry if that sounds confusing, but I've never tried setting up a database myself from scratch. I do have some experience editting the data in a MySQL database though.
PHP version: 5.2.6
MySQL version: 4.1.22-standard
cPanel Version: 11.23.4-STABLE
cPanel Build: 26138
Let me know if you need more details or clarification.
Thanks!
Filtering database using a criteria form
Moderator: General Moderators
Re: Filtering database using a criteria form
Between the food and food categories you have a relationship called many-to-many. To describe it you need 3 tables in a normalized database structure (read more about normalization and relationships here - http://dev.mysql.com/tech-resources/art ... ation.html).
Your table structure can resemble something like the following:
PK stands for primary key, and in the pivot table case it is a composite key of both the food_id and food_category_id columns.
To describe the relationships, you enter a row in the pivot table for every connection between a food and a category, for example:
You can see that food that has the ID of 2 is two categories - 1 and 4. Conversely, in category 4 you have 2 foods - with ID 1 and 2 respectively. So foods can exist in multiple categories and categories can have multiple foods - defining a many-to-many relationship.
To retrieve the data based on a specific criteria (such as all the food under a category, or all the categories of a specific food) you'll have to perform a 'join' against the many-to-many table in a SELECT statement.
You should google all the terms you are not familiar with and then you could ask more specific questions if you have any. Hope this helps
Your table structure can resemble something like the following:
Code: Select all
food
-- id (PK)
-- name
-- description
food_category
-- id (PK)
-- name
food_to_category //Many to many table, also know as a pivot table
-- food_id (PK)
-- food_category_id (PK)
To describe the relationships, you enter a row in the pivot table for every connection between a food and a category, for example:
Code: Select all
food_id | food_category_id
---------------------------
1 | 4
2 | 1
2 | 4
3 | 1
To retrieve the data based on a specific criteria (such as all the food under a category, or all the categories of a specific food) you'll have to perform a 'join' against the many-to-many table in a SELECT statement.
You should google all the terms you are not familiar with and then you could ask more specific questions if you have any. Hope this helps