Setting up a basic MySQL database of keywords

[HCP]

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"). Of course, some foods will be rich in multiple properties, so "Dried shrimp" could be listed under both "Meat" and "Rich in calcium". Visitors will use a dropbox to browse through the categories available (eg. "Fruit", "Meat", "Rich in calcium"). Once a category in the drop box has been selected, an iframe (or something that functions in a similar manner) under the dropbox will return the names of foods listed under the selected category (eg. "Dried shrimp", "Apple"). Along with the name of each food, I also want to add a brief description about how to prepare each of the foods (eg. for apple, I could add "Slice into tiny pieces").

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.

Let me know if you need more details or clarification.

Thanks!

[dm01]

Try setting up an Access database or Excel spreadsheet to lay out your information. You can transfer the information from the database or spreadsheet into the MySQL database quite easily once you're ready to do so.

[jamiemac2005]

Hey, from what you've said i'd suggest having about 2 tables:

1) Foods
- FoodID(Primary key)
- FoodName(20 chars or so string)
- PreperationDetails(500 chars or so string)
2) FoodType
- EntryID(a primary key)
- FoodID(Foreign Key)
- TypeOfFood(20 chars or so string)

The above would allow you to have more than one category for each food, and you could do what you've said. Do you have a testing server installed? if you do and you have mySQL ready to use the commands to create these tables would be something along the lines of:

CREATE TABLE Foods(FoodID int not null auto_increment, primary key(FoodID), FoodName varchar(20), PreperationDetails varchar(500));

The above would create the first table with the spec above.

CREATE TABLE FoodTypes(EntryID int not null auto_increment, primary key(EntryID), FoodID int, foreign key(FoodID) references Foods(FoodID), TypeOfFood varchar(20));

That code would create the second table i talked about.

As for the server-side code behind it it depends on what language you're using so post back with that info...

Cheers,
Jamey

[HCP]

Here are my details:

PHP version: 5.2.6
MySQL version: 4.1.22-standard
cPanel Version: 11.23.4-STABLE
cPanel Build: 26138

I do have access to setting up a new database and running entries. However, I've never tried setting up a database from scratch. I have a database for my phpBB3 forum and my Coppermine gallery.

I haven't exactly tried "programming" MySQL. I've only tried making minor edits (eg. changing the output text) but not making my own tables and data without the aid of someone else or a tool.

[jamiemac2005]

Hey, these tutorials will probably be a big help to you:

http://www.w3schools.com/php/php_mysql_intro.asp

They shouldn't take too long to go through(not longer than explaining the process over this thread) and they're extreemly helpful.

Cheers,
Jamey

[HCP]

Ah, that's something like what I've been looking for. I'll let you know if I need more help.

[HCP]

Okay, after having a read through some pages about PHP and MySQL online, I'll describe what I'm aiming to do in less confusing terms.

Basically, I have some data stored in my database (eg. "Fish", "Shrimp", "Apple", "Mango"). I will have a form that acts as a search function for visitors. They can select various criteria to filter the data and display only the entries that satisfy the criteria that they have selected from the form (eg. through check boxes, drop boxes, radio buttons etc). For example, if I only had those four terms I mentioned in the database, and the visitor selects the criteria that only entries that have been labelled by me as a "Fruit" will be displayed. I am likely to have entries that will have multiple labels, so I wish to allow the visitors enter multiple criteria.

Which tutorial is best for meeting my needs? I hear a lot about databases being hacked, so I want to know about any recommendations you have.

Thanks!

[HCP]

I couldn't exactly find a tutorial that made sense, so I'll just let you know what I've done so far.

The name of my database is hcpar_food.

For the food table, I have the following:
food_id (Primary key) - ID numbers of the food listed
food_name (50 chars or so string) - Names of the food
food_note (500 chars or so string) - Any additional notes

For the cat table, I have the following:
cat_id (Primary key) - ID numbers of the categories
food_id (Foreign key) - Names of the food (Yes?)
cat_name (50 chars or so string) - Names of the categories

What do I need to do next? How do I set up the PHP form and the variables?

[carsey]

What do you want to do???

Have a form where you can submit data into a table?

Echo the data in tha table onto a webpage?

[HCP]

No, I don't want my visitors to be able to add any new data to the database. I want the form to act as a search function, but without any typing fields. They "fill out" the form by selecting criteria and then the information from the database that meets their criteria will be echoed onto a webpage.

[HCP]

I hate doing this, but *bump*. How do I set up the PHP criteria form?

[HCP]

*bump*

[HCP]

*bump*

[carsey]

So, just to recap. You want to be able to search a mysql database and echo the results in a table on the webpage for visitors to look at?

[HCP]

Yes. I want to build a PHP form for visitors that acts as a search function with filtering criteria. Only data that matches the criteria that the visitor selects will be echoed. How do I set all this up? I've read through the tutorials, but I still don't know how to get started. All I've done so far is set up the database and the tables that I mentioned before.

[carsey]

Leave it with me and Ill have a go.

Do you want the results displaying in a table?

[HCP]

Yes, a table would be good. Thank you so much! I really appreciate your assistance.

Below are the names of the tables/values.

Originally Posted by HCP

The name of my database is hcpar_food.

For the food table, I have the following:
food_id (Primary key) - ID numbers of the food listed
food_name (50 chars or so string) - Names of the food eg. Tuna, Oak leaves
food_note (500 chars or so string) - Any additional notes, such as how to cook the food

For the cat table, I have the following:
cat_id (Primary key) - ID numbers of the categories
food_id (Foreign key) - Names of the food (Yes?)
cat_name (50 chars or so string) - Names of the categories eg. Vegetable, Meat

[carsey]

Thanks

To help me understand how you want the outputted results to look, could you draw me up a picture so I can understand what you need it to look like.

Chris

[HCP]

Something like this:

(Criteria form is above results table)

+-------------+------------------------------+
| Name        | Notes                        |
+-------------+------------------------------+
| Tuna        | Fresh tuna only. Not canned. |
+-------------+------------------------------+
| Oak leaves  | Serve uncooked, but cleaned. |
+-------------+------------------------------+

etc.

As for the criteria form, I want it to include:

- 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".

I forgot to mention this in my earlier posts, but for the names of the foods that will be displayed in the results table, I want to make the text colour of the foods that are "Allowed" green and those that are "Forbidden" to be in red. Do I have to set up another SQL table for something like that?

I am willing to credit you on the page. If you are interested, let me know what you would like me to credit your name as.

[HCP]

By no means am I trying to rush you, carsey, but am I asking for too much? If there is something that is really bugging you, please let me know and I'll see if I can think of a way around it.