# Setting up a basic MySQL database of keywords



## HCP (Apr 14, 2006)

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 (Oct 10, 2006)

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 (Jul 5, 2007)

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 (Apr 14, 2006)

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 (Jul 5, 2007)

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 (Apr 14, 2006)

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


----------



## HCP (Apr 14, 2006)

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 (Apr 14, 2006)

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 (Aug 19, 2006)

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 (Apr 14, 2006)

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 (Apr 14, 2006)

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


----------



## HCP (Apr 14, 2006)

*bump*


----------



## HCP (Apr 14, 2006)

*bump*


----------



## carsey (Aug 19, 2006)

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 (Apr 14, 2006)

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 (Aug 19, 2006)

Leave it with me and Ill have a go.

Do you want the results displaying in a table?


----------



## HCP (Apr 14, 2006)

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

Below are the names of the tables/values.



HCP said:


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


----------



## carsey (Aug 19, 2006)

Thanks :smile:

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 (Apr 14, 2006)

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 (Apr 14, 2006)

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. :wink:


----------



## carsey (Aug 19, 2006)

Im thinking how I would work out the form. Im not sure if it is possible to search a database while selecting what options you want in a form like you specified.


----------



## carsey (Aug 19, 2006)

Just had a word with a freind about how the form would be done. He told me the easiest way to do what you asking is to use ajax or javascript. 

Unfortunately, i dont know any of these languages :sad:

Sorry
Chris


----------



## Redcore (Aug 14, 2007)

Yeah, that's some JS work right there. I don't think it'd be terribly difficult to find with some effective Google keywords, but the tougher part is going to be coding that kind of functionality with PHP. It's a bit on the advanced side, for sure. Perhaps not so much advanced as just confusing at times. I've built a lot of pretty difficult forms (Ever seen a drop down with check marks? Neither did I - but I had to build one - and it was difficult) and they're just generally more difficult if you're new to doing it.

How much PHP do you know? In concept this may seem difficult to you - but it's not really THAT bad. It just has a lot of moving parts that you need to make fit together, basically.

Anyways, write a list of what you need to do (at this moment, you may not be sure exactly how many processes you need to be building for each component, but if you don't know that's fine - just write down your specific intentions.) and in the order you should be doing them - building the database and the pages to put that information into those tables should be first. Then once you're successfully inputting information, we can work on that tag/search thing.


----------



## HCP (Apr 14, 2006)

My knowledge of PHP is extremely basic... I can put things like includes and edit the output of PHP code, but otherwise, I don't know how to build things like functions.



HCP said:


> Something like this:
> 
> (Criteria form is above results table)
> 
> ...


I don't mind what method you use.


----------



## Redcore (Aug 14, 2007)

That just seems like the design concept for the search page the users will see.

I meant how do you want this to WORK?

IE: first thing is to build database/tables, second thing is to build a page that enters new criteria into the database, third is to build a page to modify criteria, etc etc.


----------



## brent.charlebois (May 8, 2007)

Set your form up with HTML with the action= set to your PHP file.

BC


----------

