Storing information in MySQL database

Michiel

Hi there,

I've got a question but I'm not sure I picked the right forum. The question is about MySQL, but it is kind of a newbie question (I guess). Ok, here it comes:

I'm setting up a website where people have to fill in their personal information (name, street, city, day of birth, email, etc.) via a form. After that I have got another form where people tell me in what period (e.g. 23 september - 5 october) they are availlable. After they have filled in both forms I want to put it all in a database (MySQL I think). After the information is stored in the database I want to be able to search the database (so e.g. 'look for all people that are availlable in the period from 24 september -2 october').

After this very long intro comes the real question: how do I store the information people fill in in my database and how do I set up the database? What is the best way to store the dates (both day of birth and availlable period)? I'm very new to MySQL so if someone can clearly explain me how to do this (or point out some practical tutorials to me) I would be helped a lot!

Thanx in advance, Michiel

sarahk

Hi Michiel

Looks like you need two tables
table Person
personid <-- primary, autoincrement
firstname
lastname
dob
etc....

table Available
availableid
personid
startdate
enddate

then when the user has filled in the first form you can do the insert into person straight away, get back the personid and move onto availability.

Dates: I do use regular dates in my tables but where the date is searchable I tend to format a date as 20020924 as it makes it very easy for comparison. Then when I need to display it I just deconstruct the 8 chars into the format I want.

$sql = "Select * from available, person where available.personid  person.personid and startdate < '$mydate' and enddate > '$mydate'";

This column can be indexed and will be really simple to use.

However, others will have alternate ideas so see what other responses you get before making up your mind.