I have been asking really basic generic questions trying to find out what I need to start building a search function for my web site and as far as I know the first step is for me to _Conceive_ the database organization. I thought this sounded easy, because I just needed to make a table but that thought didnt last long.

I don't know any php or mysql code. I just searched around and found that phpmyadmin was a really good program and I check my cpanel to make sure I had it.

Then I looked into the database and I found out that I need a table but the page i was reading was kinda like my question very vague and didn't give me any step by step instructions.

So right now I am trying to conceive the database organization. I thought this sounded easy, because I just needed to make a table but that thought didnt last long.

Where do I make the table at on a blank page? On my index? on my search page? It didnt say

I think I need one entry per species, with at least one field being an absolutely unique ID: usually just a number or something. It would be tempting to use the Latin name, since a species is uniquely identified by its name, but this is probably not a good idea, because of subspecies, and curiosities like the "official name" changing.

but I don't know if I need to do that because I want something that will allow my users to submit all the data. Its hard to explain but there are 800+ different species and there are news found all the time. If I need to make a table with all of them in it then doing it this way is not what I neeed.

I'm sure I'm going to have to code my own but if there are many choices to code it with what option would you guys recommend for someone that only knows html/css?

I am willing to lean new code though i just heard that you could it without php or mysql.

Thanks so much for all the advice I'm just having trouble finding a starting place.

I'm really not sure what you're asking?

A database table is like an Excel worksheet. It's a grid of rows and columns. Each row corresponds to a record, each column a field. Databases can maintain unique identifiers for you, like a number that increments, or a GUID.

You make a table by issuing a CREATE TABLE command to the database engine, and then you interact with it through SQL commands, usually to put data into the table and read it back out.

Make a table, call it "tableSpiders"
add these fields:
uid [int] (unique identifier)
genus [varchar(50)]
description [varchar(500)]
any other information you have about all the different species, add a field for

Then your search function just needs to search all the string fields in your table, genus, description, etc. Something like:

select * from tableSpiders 
where genus like '%searchterm%' or description like '%searchterm%'

In what's beginning to look like a consistent pattern, I agree with virtually everything Nyef says. I'd suggest using an integer instead of a GUID, but that's a small point that you won't even notice in day to day use. The main point, tho, is it's really about as easy as he just made it look.

Thanks so much for helping me!

http://www.tarantuladatabase.com/assets/pages/test.html

This is kinda what I have been playing around with for php If this is working I can see how it works a little.

I haven't been able to get connected to the database I did find out that to make a table I go into my cpanel then into phpmyadmin. I made a table with 1 field but I don't understand what format I need to use.

Will i have 1 table for each spider or will i have 1 table called Tarantula with 1 field called Housing and so one like this.

Table called Tarantula

>Housing
>>>>>>>>Arboreal
>>>>>>>>Terrestrial
>>>>>>>>Fossorial
>Location
>>>>>>>>New World
>>>>>>>>Old World
>Speed
>>>>>>>>Slow
>>>>>>>>Medium
>>>>>>>>Fast

Is this right at all?
I am confused about the things that I dont know like genes species and color.

I'd suggest using an integer instead of a GUID, but that's a small point that you won't even notice in day to day use.

Ah, but I didn't say to use a GLOBAL unique identifier, just a unique identifier. I also identified the type in brackets as [int]. I guess the uid threw you off =) I just was pointing out that it was the unique identifier for the table, the ideal candidate for a primary index.

I don't like messing with indexes and triggers and all that until a site's traffic gets big enough that database lookups start to slow it down. So I tend to just use a simple UID field for every table and make it the primary index also. Then later on as needed you can migrate stuff more to the database layer (stored procedures etc) to speed things up. Until then, keep it simple =)

You are seriously over estimating my intelligence

I think i got the hang of how to create the table... I think

This is my table

http://www.tarantuladatabase.com/table.JPG

and these are the values

http://www.tarantuladatabase.com/value.JPG

I couldn't figure out how to leave a field value black so I just put None in as a place holder.

How does that look?

You've got it down, Snot. And to answer an earlier question you asked, the "proper" way is one table with lots of stuff in it. We could go into detail and talk for days about why that's so, but the main point is its easier this way. If you had a table for each spider, well, you'd need a different way to store the list of all of them, and that would have to tell you which table to use. That's what the database is for - just store everything that's alike in one place.

If you're feeling brave, I have some recommendations. You probably noticed how handy varchar(255) is, but sometimes there's a better way. I think a few of your fields could benefit from it. Let's talk about a few specifics.

You might want to get rid of your humidity column. It's only useful for one thing right now - you can look up a spider, and see what humidity it corresponds to. That's a useful thing, but a database can do even more. Instead, you might use 2 columns, called minHumidity and maxHumidity, both floating point (or some other number with decimal format) types. This way instead of text saying "high 70 to 80 percent" you have numbers that you can do math with. This way, you can have a search function, where I might say "I have a tank at 63 % humidity - what spider can live in it?" Same concept applies to your size column.

Speed, location, and housing, could all benefit from a different idea, called lookup tables. You don't have very many differnet values in those columns, but you have to type and store them in each time. Worse, if you make a typo, every time you run a search, you'll start missing things that were spelled wrong. Instead, you might give size its own table, with an ID column. Small can get 1, and medium 2. Then, in your spider table, you'd have a SizeID column, that's just a number - if it's 1, you know you're dealing with a tiny spider. The benefit there is a narrow table that's faster, but it's also consistant data, no worry about spelling something wrong and not being able to find it.

I don't know if i'm that brave lol

This is my reasoning behind doing it like that and it could be compleatly wrong so please correct me but

Every tarantula will fall into all of these one time everytime

housing, humidity, speed, size, location

so I was going to use bullets in my from as way to filter them.

http://www.tarantuladatabase.com/***...es/search.html

is kinda an example if you dont look at any of the drop downs. So there wont be any thing to type in or spell wrong because it will all be in the from.


edit

oh I fixed the id thing and got rid of the search all and none values.

http://www.tarantuladatabase.com/gotit.JPG

I am going to start making a new from soon

That will work. Do what's easy for you to get it working, then later you can update it with fancier features.

Originally Posted by nyef

Ah, but I didn't say to use a GLOBAL unique identifier, just a unique identifier. I also identified the type in brackets as [int]. I guess the uid threw you off =) I just was pointing out that it was the unique identifier for the table, the ideal candidate for a primary index.

That's exactly where I lost track!

I think have it now hehe thanks to you guys!