Filtering database using a criteria form

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Wai
Forum Newbie
Posts: 1
Joined: Sun Nov 23, 2008 9:46 pm

Filtering database using a criteria form

Post by Wai »

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!
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Filtering database using a criteria form

Post by Eran »

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:

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)
 
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:

Code: Select all

 
food_id  | food_category_id
---------------------------
   1       |            4
   2       |            1
   2       |            4
   3       |            1
 
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 :)
Post Reply